Skip to main content
Share Your Experience: Take the 2024 Developer Survey
78 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 ...
Jendrusk's user avatar
  • 906
43 votes
Accepted

DELETE rows which are not referenced in other table

Quoting the manual: There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING ...
Erwin Brandstetter's user avatar
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 ...
Evan Carroll's user avatar
  • 63.5k
28 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)
Gunar Gessner's user avatar
26 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 ...
Philᵀᴹ's user avatar
  • 31.8k
24 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 ...
Duke's user avatar
  • 341
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 ...
Evan Carroll's user avatar
  • 63.5k
21 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 ...
McNets's user avatar
  • 23.8k
19 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 ...
slardiere's user avatar
  • 636
18 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 ...
Craig Ringer's user avatar
  • 56.6k
15 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 ...
Fake Name's user avatar
  • 1,336
15 votes
Accepted

PostgreSQL 9.3: Dump a database excluding the data of some tables

I think you need to use --exclude-table-data=table option. From the docs: --exclude-table-data=table Do not dump data for any tables matching the table pattern. The pattern is interpreted according ...
ypercubeᵀᴹ's user avatar
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 ...
Troy Porter's user avatar
14 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 ...
Chris Curvey's user avatar
14 votes

Why postgresql pg_dump exports a view as a table?

Internally, a view is just a table with a rule, so this makes sense. See here: https://postgresql.org/docs/9.5/static/rules-views.html Views in PostgreSQL are implemented using the rule system. ...
Max Murphy's user avatar
14 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 ...
Pavel Kazhevets's user avatar
13 votes
Accepted

Deleting old postgresql log files (Ubuntu)

You can use the configuration below; log_truncate_on_rotation = on log_rotation_age = 1d log_filename = 'postgresql-%a.log' log_rotation_size = 0 #just rotate daily This says create a log file with ...
Sahap Asci's user avatar
  • 2,969
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) ...
lzap's user avatar
  • 221
12 votes
Accepted

best way to avoid redundant aggregate functions and/or group by columns

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the ...
Andriy M's user avatar
  • 23k
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 ...
3manuek's user avatar
  • 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 ...
Erwin Brandstetter's user avatar
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 ...
Paul's user avatar
  • 121
12 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 ...
RCross's user avatar
  • 221
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 ...
liberforce's user avatar
11 votes

Transactions within a Transaction

PostgreSQL does not support sub-transactions, but the SAVEPOINT feature can effectively answer your need. Quoting from the documentation for Advanced access layer to PG via promises by Vitaly Tomilov ...
Amir Ali Akbari's user avatar
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 ...
Marcus's user avatar
  • 111
11 votes
Accepted

Create unique sequence per set of rows sharing the same foreign key value

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column ...
Erwin Brandstetter's user avatar
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 ...
FiscalCliff's user avatar
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 (\ ->...
Adam Gent's user avatar
  • 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 ...
Evan Carroll's user avatar
  • 63.5k

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