PostgreSQL version 10
Questions tagged [postgresql-10]
349 questions
34
votes
4 answers
Unquoting JSON strings; print JSON strings without quotes
SELECT json_array_elements('["one", "two"]'::json)
gives result
| json_array_elements |
| :------------------ |
| "one" |
| "two" |
I would like to have the same but without the quotes:
one
two
Looks like I can't use…
Maxim Yefremov
- 465
- 1
- 4
- 7
28
votes
1 answer
Multiple on conflict targets
I have two unique indexes on columns a and b. I need something like this:
insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'
So generally I want to make…
user606521
- 1,415
- 6
- 23
- 28
26
votes
2 answers
What is the data type of the ‘ctid’ system column in Postgres?
The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.
That page mentions that oid values “are 32-bit quantities”. And that page says the same about transaction identifiers. So I will assume that means oid,…
Basil Bourque
- 11,188
- 20
- 63
- 96
21
votes
1 answer
How do I cleanup PostgreSQL's WAL?
What is the correct way to cleanup PostgreSQL's WAL? I have a database with more than 100 GB and it has around 600 GB in pg_wal. Also I have 2 logical replications set up.
Primary and replicas are running PostgreSQL 10.
Primary and replicas have…
Gabriel Weich
- 313
- 1
- 2
- 7
19
votes
2 answers
What is a “transition table" in Postgres?
The page describing what's new in Postgres 10 mentions “Transition Tables for Triggers”.
Transition Tables for Triggers
This feature makes AFTER STATEMENT triggers both useful and performant by exposing, as appropriate, the old and new rows to…
Basil Bourque
- 11,188
- 20
- 63
- 96
16
votes
2 answers
What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer)
I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it).
I've spent hours researching bigserial vs uuid…
ffxsam
- 271
- 1
- 2
- 6
16
votes
3 answers
What are Identity Columns?
I was reviewing the commit-fest scheduled for 7/01 for PostgreSQL and I saw that Pg is likely going to get "identity columns" sometime soon.
I found some mention in information_schema.columns but nothing much
is_identity yes_or_no …
Evan Carroll
- 65,432
- 50
- 254
- 507
14
votes
4 answers
Postgres jsonb vs composite type performance differences
What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?
For example, consider a column like that used in the Postgres documentation:
CREATE TYPE inventory_item AS (
name …
Metropolis
- 243
- 3
- 7
14
votes
1 answer
Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?
I have the following tables (taken from the Sakila database):
film: film_id is pkey
actor: actor_id is pkey
film_actor: film_id and actor_id are fkeys to film/actor
I am selecting a particular film. For this film, I also want all…
Jelly Orns
- 243
- 1
- 2
- 6
13
votes
1 answer
Why is PostgreSQL choosing the more expensive join order?
PostgreSQL using defaults, plus
default_statistics_target=1000
random_page_cost=1.5
Version
PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
I've vacuumed and analyzed. The query is very straightforward:
SELECT…
Paul Draper
- 800
- 7
- 20
12
votes
2 answers
Combining array_agg and unnest
Given a dataset (with a GIN index on values):
key | values
-------------
1 | {4,2,1}
1 | {2,5}
2 | {4,1,3}
I want to aggregate the arrays:
key | values
-------------
1 | {4,2,1,5}
2 | {4,1,3}
My first thought didn't work:
SELECT key,…
OrangeDog
- 338
- 1
- 3
- 13
12
votes
1 answer
Efficient pagination for big tables
Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results.
In an attempt to not use OFFSET, I pass the id from the last row in the previous page in a parameter called p (prevId).…
David
- 123
- 1
- 1
- 6
11
votes
1 answer
How to add a PostgreSQL 10 identity column to an existing table?
I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.
Is it also possible to add an identity column to an existing table?
For example, with the old SERIAL columns, I can do:
ALTER…
tinlyx
- 3,810
- 14
- 50
- 79
9
votes
3 answers
Why is there no max(uuid)/min(uuid) function?
Why can I use a UUID to sort rows:
SELECT uuid_nil()
ORDER BY 1;
But I cannot compute the maximum value:
SELECT max(uuid_nil());
[42883] ERROR: function max(uuid) does not exist
Hint: No function matches the given name and argument types. You…
xehpuk
- 327
- 2
- 5
- 11
9
votes
1 answer
PostgreSQL ANALYZE execution time over 24h (still running)
I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized that the indexes were not being used in one of the…
wjozsi
- 171
- 1
- 6