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.
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
)
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 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
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/