Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-11]

PostgreSQL version 11

235 questions
11
votes
3 answers

How to swap primary key between records in postgres

I can't seem to find a way to achieve this transactionally (or not) What I need to achieve is non-standard, hence my difficulty finding a solution. I need to code a data migration tool to "swap" old records with new records in a table, but I have…
Pedro Borges
  • 211
  • 2
  • 7
9
votes
2 answers

Does SELECT remove dead rows like VACUUM does?

I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing rows from a table seems to reduce the work VACUUM has to do afterwards. Test Data Note: autovacuum is disabled CREATE TABLE numbers (num bigint); ALTER TABLE numbers SET…
rafbm
  • 193
  • 4
8
votes
2 answers

How to set statement timeout per user?

I have multiple users in Postgres. I would like to set up different statement timeouts for different users. Eg: Guest 5 minutes and Admin 10 minutes. Is it possible in Postgres 11.11?
Dharanidhar Reddy
  • 193
  • 1
  • 1
  • 7
8
votes
1 answer

ERROR: insufficient columns in PRIMARY KEY constraint definition

I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have some partitioned table with inherence and planning to migrate them to the declaration. Table DDL: CREATE TABLE c_account_p ( billing_account_guid character varying(40) NOT…
Rj_N
  • 436
  • 1
  • 7
  • 13
8
votes
4 answers

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of contents. When I query for a page, I wish to output all…
Tim
  • 266
  • 1
  • 2
  • 10
8
votes
1 answer

PostgreSQL How to DEFAULT Partitioned Identity Column?

PostgreSQL 11 What is the best way to generate default values for identity columns on partition tables. E.g CREATE TABLE data.log ( id BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 …
akagixxer
  • 183
  • 1
  • 5
7
votes
1 answer

Is it safe to use default value with not null when adding a new column?

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below: ALTER TABLE "blogs" ADD "published" boolean DEFAULT FALSE NOT NULL I know adding a new column with a default…
ogirginc
  • 275
  • 2
  • 3
  • 10
7
votes
2 answers

Returning multiple result sets from server-side procedures in Postgres 11

This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets. ➥ Did this feature indeed appear in the Postgres 11 release? If so, can you explain how…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
7
votes
1 answer

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A and B cannot be referenced in a single row in C,…
Guiik
  • 73
  • 1
  • 2
  • 6
6
votes
2 answers

PostgreSQL: How can I list the tables to which a sequence belongs?

I know I can list all sequences with this: SELECT * FROM information_schema.sequences; But I need to know by which table this sequence is used. The reason I want to do this is to find out which sequences of PRIMARY KEYs are not in the default…
H.Ç.T
  • 185
  • 1
  • 1
  • 7
6
votes
1 answer

Why EXPLAIN doesn't show heap fetches for index scan

I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output. Test environment -- function to fill test table CREATE OR REPLACE FUNCTION fillTable (n…
Frank59
  • 215
  • 2
  • 6
6
votes
1 answer

Fastest way to extract full table in Postgres

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This could take like an hour in the best cases. I'm also…
Imanol Y.
  • 785
  • 2
  • 10
  • 28
6
votes
1 answer

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. The name itself is not important, but I would…
Tim
  • 266
  • 1
  • 2
  • 10
5
votes
1 answer

Postgresql 11: terminating walsender process due to replication timeout

I have found some questions about the same error, but didn't find any of them answering my problem. The setup is that I have two Postgres11 clusters (A and B) and they are making use of publication and subscription features to copy data from A to…
Abhishek B
  • 131
  • 3
  • 10
5
votes
1 answer

Determine Nodes in Network with PostgreSQL

I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes in the chain, not just the nodes the node itself…
Christophe
  • 53
  • 4
1
2 3
…
15 16