79
votes
Accepted
How to install the additional module pg_trgm
pg_trgm is an extension, so:
CREATE EXTENSION pg_trgm;
If you get the following error
ERROR: could not open extension control file ".../extension/pg_trgm.control":
No such file or ...
- 916
39
votes
Export Postgres table as json
If you're using psql then there is no reason to use \COPY at all.
\t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;
This is the same method we use to get png/jpgs/tifs out of the database ...
- 64.2k
29
votes
Export Postgres table as json
Please find below the only answer that outputs actually valid JSON (i.e. array of objects).
\t
\a
\o data.json
select json_agg(t) FROM (SELECT * from table) t;
(source)
- 409
28
votes
postgres - pg_dump and pg_restore without roles
Using PostgreSQL 12.4:
$ pg_dump --help
...
-O, --no-owner skip restoration of object ownership in
plain-text format
...
-x, --no-privileges do not ...
- 381
27
votes
Accepted
PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
You can do this with a function that runs in the security context of its owner.
Function that refreshes the view (create it with the user that owns the MV/table):
CREATE OR REPLACE FUNCTION ...
- 31.9k
22
votes
Accepted
Postgresql To Return 0 when Null
Use max(coalesce(logincount, 0)) to avoid NULLs
According to Postgres docs (9.6):
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments ...
- 23.8k
22
votes
Accepted
Disk space unreleased after cleaning up rows from PG table
From the docs,
In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency ...
- 64.2k
20
votes
Accepted
postgres - pg_dump and pg_restore without roles
The error occurs when pg_restore set the ACLs : you can use --no-acl to prevent GRANT commands.
With the -Ft option in pg_dump, you can skip roles and ACLs only in pg_restore. You can also edit the ...
- 646
19
votes
Accepted
Dealing with disk space full in postgresql
Because PostgreSQL must write WAL before making any changes to tables, it needs free disk space in order to delete things and release more disk space.
If you let the disk fill up, you can't recover ...
- 57k
16
votes
Accepted
Fast hamming distance queries in postgres
MOAR ANSWERS!
Ok, I've finally taken the time to write a custom PostgreSQL indexing extension. I used the SP-GiST interface.
This was fairly challenging, mostly because Posgres is big.
Anyways, as ...
- 1,366
15
votes
Accepted
pg_restore certain tables only
if you're using the "custom" format , you can use the -l (ell) and -L options to do this.
create your dump, using the "custom" format
run pg_restore -l /path/to/dump/file > table_of_contents.txt
...
- 386
15
votes
How to find what tablespace a table/index is in on PostgreSQL?
For example, you have a table example_table (optionally in more than one schema):
SELECT tablespace
FROM pg_tables
WHERE tablename = 'example_table' [AND schemaname = 'your_schema'];
The same ...
- 381
15
votes
How to install the additional module pg_trgm
1) Log into postgres
psql -U <DB_USERNAME>
2) After you are connected, switch to the DB you want to install the extension for:
\c <DB_NAME>
3) Then install the extension as answered ...
- 151
13
votes
Pgbouncer getting auth failing when trying to connect using psql
To create an md5 password for PGBouncer (or PostgreSQL for that matter), the formula is:
"md5" + md5(password + username)
Here are 3 ways you can create one, where the username is "admin" and the ...
- 231
12
votes
why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist
In short, you want either (clean existing): (note the database name is postgres)
pg_restore -c -d postgres db.dump
or (create new)
pg_restore -C -d postgres db.dump
or (create new explicitly)
...
- 221
12
votes
Accepted
Does changing `wal_keep_segments` require a restart?
For checking the variable's context, you can execute the following query:
SELECT name, context FROM pg_settings WHERE name = '<variable name';
In the case of wal_keep_segments, the context is ...
- 1,449
12
votes
Accepted
ERROR: invalid input syntax for type money
The manual about the money type has advice for your case exactly:
Since the output of this data type is locale-sensitive, it might not
work to load money data into a database that has a different ...
- 180k
12
votes
Setting location of temp files for Postgresql backend (9.3.10)
There is a big problem with a_horse_with_no_name's solution. Not all instances of PostgreSQL even have a $PGDATA/base/pgsql_tmp because that entire path is actually created as needed in those ...
- 121
11
votes
why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist
Let me reword @Izap's answer, because I had to read all answers over and over to figure things out...
The database management tools need in some cases a database to connect to, in order to perform ...
- 211
11
votes
Add NOT NULL constraint to large table without table scan
One potential alternative is to create a check constraint using NOT VALID, then validating the check constraint later. This method requires holding an ACCESS EXCLUSIVE lock only for the duration to ...
- 111
10
votes
How to turn JSON array into Postgres array?
I've tested a few options. Here is my favorite query. Suppose we have a table containing id and json field. The json field contains array, which we want to turn into pg array.
SELECT *
FROM test
...
- 121
10
votes
Export Postgres table as json
I will add a special caveat to Verace's answer. You need to do post processing on the outputted JSON file if you have text columns with backslash characters: \.
Otherwise you will get duplicate (\ ->...
- 209
10
votes
Accepted
Going out of disk space in the course of querying a postgres database
Of course, there are actually many ways for a SELECT to take disk space. Just look at the docs for work_mem
work_mem (integer)
Specifies the amount of memory to be used by internal sort ...
- 64.2k
9
votes
Accepted
Why does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column?
The key is that you don't compare the same types. When comparing a bigint to a numeric, the easier way is to 'expand' the bigint with the decimal places being 0 (like 1 -> 1.0), while the other way ...
- 31.5k
9
votes
Accepted
Deleting records from tables containing FK pointing to each other
The problem can be solved with many ways:
First we notice that one of the FK columns is nullable. This allows to delete from both tables, using three statements in a single transaction and without ...
- 98.5k
9
votes
PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
@Philᵀᴹ's second function attempt is vulnerable to SQL injection by using:
SELECT refresh_mv_xxx('example_mview_name with data; TRUNCATE TABLE example_table_name CASCADE; REFRESH MATERIALIZED VIEW ...
- 191
9
votes
Is it possible to `DELETE` in a subquery?
You can DELETE in a CTE - which is a different kind of subquery.
But you probably don't need it for this simple case, you just need to use RETURNING and fix the syntax errors in your code:
DELETE
...
- 98.5k
9
votes
Accepted
Performance of large transactions and concurrency?
Are rows being updated during the transaction able to be read after the transaction starts and before it finishes?
Yes, in Postgres reads do not block writes and writes do not block reads. The ...
- 269
8
votes
pl/pgsql: Dynamicly get a column name from a record
Using row_to_json function:
do $$
declare
r json;
i int;
begin
for r in
select row_to_json(t.*)
from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)
loop
...
- 1,579
8
votes
Accepted
Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken?
You are quoting from the wrong section of the manual. That passage is from the Table-level Locks section. The lock type you are interested in is specified to be tuple. That means you need to refer to ...
- 23.1k
Only top scored, non community-wiki answers of a minimum length are eligible