PostgreSQL version 11
Questions tagged [postgresql-11]
222 questions
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
- 2
8
votes
2 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
- 183
- 6
6
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
- 328
- 2
- 11
6
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
- 8,776
- 14
- 46
- 78
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.
- 767
- 1
- 6
- 26
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
5
votes
1 answer
Include non-inserted rows as result of INSERT INTO ... RETURNING
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.
For example
CREATE TABLE all_sums (sum…
Ell
- 153
- 4
5
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
- 185
- 4
5
votes
1 answer
Bad execution plan when having partial index and big In-clause in Postgres
Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements.
Given the following table:
create table foo(id bigint primary key, bar…
Aleksander Blomskøld
- 153
- 5
5
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
- 153
- 5
5
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
- 53
- 1
- 5
5
votes
1 answer
Do BRIN indexes support ENUM types?
BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type.
I thought this code would work:
CREATE TYPE test_enum AS ENUM ('a', 'b');
CREATE TEMPORARY TABLE my_table (
x test_enum
);
CREATE INDEX test_index ON my_table using…
karldw
- 153
- 3
4
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
- 153
- 6
4
votes
1 answer
What is the correct way to make an exact copy of a database with pg_dump?
Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc.
Is this doable with pg_dump or does psql need to be used instead?
I've been using pg_dump -U…
cjones
- 303
- 1
- 3
- 9
4
votes
2 answers
Postgres 11: Query plan uses seq scan after upgrade
The Situation
We have a database hosted on RDS with a few hundred tables, a few of which are quite large.
We recently upgraded the database from 9.5.22 to 11.8 and performance is significantly degraded.
After upgrading, we ran VACUUM ANALYZE on the…
hommel
- 63
- 4