PostgreSQL tips

Update fields in JSONB format

Use the jsonb_set(COLUMN_NAME, PATH_TO_CHANGE, VALUE) function. The value must be a valid value in JSON (ie use '500' for numbers, 'true' for boolean and '"str"' for strings). Examples:

-- Set the postcode field to the 92600 integer.
UPDATE sales SET address = jsonb_set(address, '{postcode}', '92600') WHERE id = 47880;
-- Set a nested field to the "FRA" value. Note: the nested object must exists.
UPDATE sales SET address = jsonb_set(address, '{country,code}', '"FRA"');
-- Set a value at a given position in an array (an object must exist at this position).
UPDATE sales SET phones = jsonb_set(phones, '{0,number}', '"06"');

CSV export/import

To export content of a table to a CSV file (eg to re-import it in a different database on a different server). Update the query and connection options to fit your needs:

psql -U user -c 'COPY (SELECT * FROM sales WHERE id = 110683) To STDOUT WITH CSV HEADER;' database > /tmp/sales.csv

To import the data from a CSV file, you need to specify the columns you want to import. To find them, run head -n 1 /tmp/sales.csv to get the first lines of the CSV which contains the headers. You can then run the command below.

psql -U user database -c "COPY sales(id,organization_id,date,name,address) FROM STDIN WITH CSV HEADER;" < /tmp/sales.csv

Note 1: it should be possible to import the data with psql -U user database -c "COPY sales(id,organization_id,date,name,address) FROM '/tmp/sales.csv' WITH CSV HEADER;". However, when I tried that, I got: ERROR:  could not open file "/tmp/sale.csv" for reading: No such file or directory even though the file existed.

Note 2: you can also achieve this with pgAdmin with you want a nice GUI.

Update rows in bulks

This will update rows by batch of 100 without being blocked by locks. It is useful to prepare for the addition of an NOT NULL constraint without downtime.

UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
    SELECT server_ip
    FROM   server_info
    WHERE  status = 'standby'
    LIMIT  100
    FOR    UPDATE SKIP LOCKED
)

Understand lock issues

This will display all queries blocked by locks and which query is blocking them (pg 9.6+):

SELECT pid,
    usename,
    pg_blocking_pids(pid) AS blocked_by,
    state,
    age(now(), query_start) AS age,
    query AS blocked_query
FROM pg_stat_activity
ORDER BY age DESC NULLS LAST;

Source: https://stackoverflow.com/a/43363536

Use variables in query

Use a CTE:

WITH myconstants (var1, var2) as (
    values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = myconstants.var1
OR something_else = myconstants.var2;

Source: https://stackoverflow.com/a/54220318/3900519

Database sizes

To view the size of tables in a database:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;

To view the sizes of databases:

SELECT
  pg_database.datname,
  pg_size_pretty(pg_database_size(pg_database.datname)) AS size
  FROM pg_database
  ORDER BY size DESC;

Source: http://www.postgresqltutorial.com/postgresql-database-indexes-table-size/