Skip to main content
42 votes

Unquoting JSON strings; print JSON strings without quotes

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json); Result: col --- one two
Slava V's user avatar
  • 521
31 votes

What are Identity Columns?

This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21): 4.15.11 Identity columns The columns of a base table BT can optionally include not more than ...
ypercubeᵀᴹ's user avatar
27 votes
Accepted

How do I cleanup PostgreSQL's WAL?

pg_wal cleans itself up. You should almost never touch pg_wal by hand. If it is not cleaning itself up, you need to figure out why and fix the underlying issue. One possible reason is that you have a ...
jjanes's user avatar
  • 40.3k
23 votes
Accepted

What is the data type of the ‘ctid’ system column in Postgres?

tid See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as tid. A final identifier type used by the system is ...
Basil Bourque's user avatar
21 votes
Accepted

How can I change an existing type from "bigint" to "bigserial"?

bigserial is a pseudo-type, a notational convenience that is resolved to type bigint internally - plus a sequence, a column default, a dependency and an ownership. Basic commands to convert an ...
Erwin Brandstetter's user avatar
19 votes
Accepted

Unquoting JSON strings; print JSON strings without quotes

The default json->text coercion outputs with a double quote (") because coercing from text to a json string requires you to double-quote your input. To get rid of the double-quotes, use TRIM ...
Evan Carroll's user avatar
  • 63.9k
18 votes
Accepted

Multiple on conflict targets

Your example suggests duplicate rows in the VALUES clause itself - which would result in: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time Can be solved by folding duplicates ...
Erwin Brandstetter's user avatar
17 votes
Accepted

Postgres table partitioning "no partition of relation "parsel_part" found for row" error?

Your problem is related to this point in the documentation: When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is ...
Patrick Mevzek's user avatar
16 votes
Accepted

What is a “transition table" in Postgres?

You know how there are OLD and NEW record-variables for FOR EACH ROW triggers? Transition tables are the FOR EACH STATEMENT equivalents. They're tables with the old and new tuples, so your triggers ...
Craig Ringer's user avatar
  • 56.9k
15 votes
Accepted

How to add a PostgreSQL 10 identity column to an existing table?

You need to declare the type of the column, too (INT or BIGINT or SMALLINT): ALTER TABLE sourceTable ADD COLUMN ogc_fid int -- int or bigint or smallint GENERATED BY ...
ypercubeᵀᴹ's user avatar
14 votes

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements_text('["one", "two"]'::json) of if using jsonb instead: SELECT jsonb_array_elements_text('["one", "two"]'::jsonb)
Daniel Canales's user avatar
14 votes
Accepted

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer)

It is easy enough to benchmark this, but the INSERT performance of UUIDs will be worse, because they are bigger and slower to generate. But it doesn't sound like you are building a high performance ...
Laurenz Albe's user avatar
  • 53.9k
13 votes

What is a “transition table" in Postgres?

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table....
Evan Carroll's user avatar
  • 63.9k
13 votes
Accepted

psql gives Invalid data directory error

Error: Invalid data directory This happens when pg_wrapper is unable to figure out the data directory from the configuration. pg_wrapper is the layer on top of postgres that can juggle between ...
Daniel Vérité's user avatar
13 votes
Accepted

Efficient pagination for big tables

The key to performance is a matching multicolumn index of the form: CREATE UNIQUE INDEX ON people (firstname, id); UNIQUE, since sort order can be ambiguous without it. A UNIQUE or PRIMARY KEY ...
Erwin Brandstetter's user avatar
12 votes
Accepted

Search for nested values in jsonb array with greater operator

This is generally hard to optimize: no direct operator or index support for jsonb for this kind of test. EXISTS should at least be faster than what you have, while also avoiding duplicate rows (where ...
Erwin Brandstetter's user avatar
12 votes

Deleting all rows from INNER JOINed 3 different tables

If you have more than one join you could use comma separated USING statements: DELETE FROM AAA AS a USING BBB AS b, CCC AS c WHERE a.id = b.id AND a.id = c.id AND a....
Mahesh's user avatar
  • 231
12 votes
Accepted

How to prevent asking for password when creating new database in PostgreSQL 10?

use the PGPASSWORD environment variable, @SET PGPASSWORD=something_secret psql -c "CREATE DATABASE mydb" -U postgres postgres or just use a connection string psql -c "CREATE DATABASE mydb" "user=...
Jasen's user avatar
  • 3,603
11 votes
Accepted

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

Test setup Your original setup in the fiddle leaves room for improvement. I kept asking for your setup for a reason. You have these indexes on film_actor: "film_actor_pkey" PRIMARY KEY, ...
Erwin Brandstetter's user avatar
11 votes
Accepted

Postgres partial index on IS NULL not working

Assuming the central piece of information: with ~15% of the rows having state = 'open' and closed IS NULL is supposed to mean the same 15 % of all 1031584 rows meet both these conditions (all ...
Erwin Brandstetter's user avatar
10 votes
Accepted

Postgresql 10 create subscription hangs

From the docs: Creating a subscription that connects to the same database cluster (for example, to replicate between databases in the same cluster or to replicate within the same database) will ...
jjanes's user avatar
  • 40.3k
10 votes

pg_resetxlog command not found

That's because from version 10 it has been renamed to pg_resetwal. And in Ubuntu and Debian the binary is located in /usr/lib/postgresql/<version>/bin. The pg_xlog directory was renamed to ...
Colin 't Hart's user avatar
10 votes
Accepted

AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?

My answer is not specific to AWS Aurora PostgreSQL Serverless, but for Postgres in general. Simple alternative In your related comment, you hinted that you only need rows from the last 24 hours. So ...
Erwin Brandstetter's user avatar
9 votes
Accepted

postgresql 10 for Ubuntu 17.10 artful?

I have replaced artful-pgdg with zesty-pgdg in /etc/apt/sources.list.d/pgdg.list and it worked for me https://wiki.postgresql.org/wiki/Apt/FAQ I am using a non-LTS release of Ubuntu Non-LTS ...
5w0rdf1sh's user avatar
  • 218
9 votes
Accepted

What are Identity Columns?

How they're actually implemented in PG 10 You can see how they're actually implemented now using the test suite's expected output. Some keys to take away from this. You can specify where to start ...
Evan Carroll's user avatar
  • 63.9k
9 votes
Accepted

Conditional INSERT with a nested CTE?

For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense. You cannot nest a data-modifying CTE like you tried (as you ...
Erwin Brandstetter's user avatar
9 votes

Declaring variable for a whole script

There are no global variables per se in Postgres. But we have "customized options" that can be stretched for the purpose. SET myvars.test TO '0.1'; Persists for the duration of the session (...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible