46
votes
Unquoting JSON strings; print JSON strings without quotes
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
- 561
29
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 ...
- 42.5k
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 ...
- 11.3k
22
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
...
- 65.8k
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 ...
- 186k
19
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 in ...
- 186k
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 ...
- 1,151
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 ...
- 100k
15
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)
- 151
15
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 several ...
- 32.8k
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 ...
- 62.4k
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 ...
- 186k
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 ...
- 186k
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....
- 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=...
- 3,676
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, ...
- 186k
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 ...
- 186k
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 ...
- 42.5k
10
votes
Accepted
Passing value of datatype interval in parametrized query
TLDR: Skip to chapter "Superior query" below.
You didn't disclose the module you are working with, but the problem is obviously one of type casting. Looks like your parameter is passed as ...
- 186k
10
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 (...
- 186k
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 ...
- 9,520
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 ...
- 186k
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
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 ...
- 186k
9
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 ...
- 28.8k
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 ...
- 32.8k
8
votes
Accepted
Uninstall Postgres 10 on macOS – EnterpriseDB installer
postgres user
The user account named postgres (by default) created by the installer is actually a macOS user account.
Apple allows deleting a user account in the more recent versions of macOS: ...
- 11.3k
Only top scored, non community-wiki answers of a minimum length are eligible