33 votes

Unquoting JSON strings; print JSON strings without quotes

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json); Result: col --- one two
user avatar
  • 431
26 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 ...
user avatar
18 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 ...
user avatar
17 votes
Accepted

How do I cleanup Postgres 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 ...
user avatar
  • 31.5k
16 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 ...
user avatar
15 votes
Accepted

Combining array_agg and unnest

You need to do the unnest in a sub-query: select d."key", array_agg(distinct x.v) from data d cross join lateral unnest(d."values") as x(v) group by d."key"; Set returning functions (like unnest()...
user avatar
14 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 ...
user avatar
  • 55.7k
13 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 ...
user avatar
  • 51.8k
13 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 ...
user avatar
13 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 ...
user avatar
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 ...
user avatar
12 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....
user avatar
  • 55.7k
12 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 ...
user avatar
12 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 ...
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 ...
user avatar
  • 31.5k
10 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 ...
user avatar
10 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=...
user avatar
  • 2,380
10 votes
Accepted

Replicate only a few tables in PostgreSQL 10

To replicate some specific tables, you can use logical replication Another option is to not replicate at all and access the remote tables through a foreign table
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 ...
user avatar
  • 218
9 votes
Accepted

Postgres: What could cause the error "cannot call populate_composite on a scalar"?

Unrelated, but: you can simplify your query to: SELECT x.* from the_table, jsonb_array_elements(jsonbrecords) AS t(doc), jsonb_to_record(t.doc) as x ("grade" text, "userId" text, "endYear" ...
user avatar
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 ...
user avatar
9 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, btree (...
user avatar
9 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)
user avatar
8 votes
Accepted

Does PostgreSQL support ICU collation's options and settings?

Case insensitive or accent-insensitive collations cannot be used prior to PostgreSQL 12, because internally PostgreSQL considers that strings with a different binary representation are not equal. When ...
user avatar
8 votes

Declaring variable for a whole script

There are no global variables per se in Postgres. But we have "customized options" that can be streched for the puropose. SET myvars.test TO '0.1'; Persists for the duration of the session (not just ...
user avatar
8 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 ...
user avatar
8 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 ...
user avatar
8 votes
Accepted

pg_ctl hangs over ssh

It looks like pg_ctl wants a real terminal for output, which is not allocated by ssh when you simply ask it to run a command. According to the Postgres manual On Unix-like systems, by default, the ...
user avatar
  • 21.2k
7 votes
Accepted

How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

It appears you use the same functions as before: https://www.postgresql.org/docs/10/static/functions-info.html pg_get_serial_sequence returns the name of the sequence associated with a column, ...
user avatar
  • 2,191
7 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 ...
user avatar
  • 55.7k

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