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
- 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 ...
- 92.7k
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 ...
- 8,986
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 ...
- 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 ...
- 150k
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()...
- 70.6k
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
...
- 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 ...
- 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 ...
- 92.7k
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 ...
- 150k
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 ...
- 27k
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....
- 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 ...
- 1,053
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 ...
- 150k
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 ...
- 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 ...
- 150k
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=...
- 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
- 70.6k
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 ...
- 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" ...
- 70.6k
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 ...
- 150k
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 (...
- 150k
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)
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 ...
- 27k
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 ...
- 150k
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 ...
- 150k
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 ...
- 8,823
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 ...
- 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, ...
- 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 ...
- 55.7k
Only top scored, non community-wiki answers of a minimum length are eligible