Useful PostgreSQL queries and commands. Mostly copied and pasted from the Mastering PostgreSQL 13 book.

postgresql-commands.sh

# List all available database versions
aws rds describe-db-engine-versions --query '*[].[EngineVersion]' --output text --region eu-west-1 --engine postgres

aws rds describe-db-engine-versions --query '*[].[EngineVersion]' --output text --region eu-west-1 --engine aurora-postgresql

# Connect to Postgres via SSH's ProxyJump
ssh \
  -L 5555:your.private.postgres.hostname:5432 \
  -J your.proxy.hostname \
    private.hostname.with.access.to.postgres

# The above opens an SSH session; in another session:
psql -h localhost -p 5555 -U db_user db_name

postgresql-queries.sql

SHOW config_file;
/*
               config_file
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
*/

-- Supported languages
SELECT cfgname FROM pg_ts_config;

-- Supported extensions
SELECT comment FROM pg_available_extensions;

-- Sequential scan report, e.g. to detect tables with missing indexes
SELECT schemaname, relname, seq_scan, seq_tup_read, 
  seq_tup_read / seq_scan AS avg, idx_scan 
FROM pg_stat_user_tables 
WHERE seq_scan > 0 
ORDER BY seq_tup_read DESC LIMIT 10;

-- Find tables with superfluous indexes
SELECT schemaname, relname, indexrelname, idx_scan, 
  pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size,
  pg_size_pretty(sum(pg_relation_size(indexrelid)) 
    OVER (ORDER BY idx_scan, indexrelid)) AS total 
FROM pg_stat_user_indexes 
ORDER BY 6;

-- Inspect currently running queries
SELECT pid, application_name, query_start, state_change, state, query
FROM   pg_stat_activity

-- Set app name on the client for easier debugging and analysis
SET application_name TO 'api.mydomain.co.uk';

-- Change owner of all objects, e.g. in preparation for removing a role
REASSIGN OWNED BY CURRENT_USER TO new_user;

-- Load table from a network resouce
COPY t_location FROM PROGRAM
  'curl https://www.cybertec-postgresql.com/secret/orte.txt';

-- Create a table space on a fast hard drive
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

-- Full text search debugging
SELECT * FROM ts_debug('english', 'go to www.cybertec-postgresql.com');

-- Indexes on date ranges (e.g. booking process)
-- https://www.postgresql.org/docs/13/rangetypes.html#RANGETYPES-CONSTRAINT

-- Calculating median values
SELECT region, percentile_disc(0.5) WITHIN GROUP (ORDER BY production) 
FROM bp_oil
GROUP BY ROLLUP (region);

-- Sliding window functions demo
SELECT id, array_agg(id)
  OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM generate_series(1, 5) AS id;

SELECT id, array_agg(id)
  OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
FROM generate_series(1, 5) AS id;

-- Composite types
-- PostgreSQL handles column expansion by actually transforming the first form into the second. 
-- So, in this example, myfunc() would get invoked three times per row with either syntax.
-- If it's an expensive function you may wish to avoid that, which you can do with a query like:

SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;

-- Placing the function in a LATERAL FROM item keeps it from being invoked more than once per row.
-- m.* is still expanded into m.a, m.b, m.c, but now those variables are just references to the output of the FROM item.
-- (The LATERAL keyword is optional here, but we show it to clarify that the function is getting x from some_table.)