PostgreSQL command line cheatsheet

Get results as JSON

select array_to_json(array_agg(row_to_json(t)))
    from (
        SELECT id, interaction_id, comment_id, created_at, state FROM ugc_permissions 
		WHERE EXTRACT(MONTH FROM created_at) IN (8,9)
		AND EXTRACT(YEAR FROM created_at) = 2017
		ORDER BY created_at ASC
    ) t

JSONB

Querying nested arrays

SELECT DISTINCT t.* 
FROM 
    table_name t,
    jsonb_array_elements(rules->'rules') as rules(rule)
WHERE
    rule->>'type' like 'HashTagRule%'

Update all values for given key nested in JSON array of objects

update transaction_docs
set signed = sq.signed
from (
	select 
	   id, 
	   jsonb_agg(
	   	 jsonb_set(
			d, 
			'{entity_id}', 
			(select u.entity_id from master.users u where u.id::text = d->>'id')::text::jsonb, 
			true)
		 ) signed 
	from  transaction_docs t, jsonb_array_elements(t.signed) d
	group by id
) as sq
where transaction_docs.id = sq.id

Update all values for given key nested in JSON array of objects Using CTE

with ct as
(
	select id, jsonb_array_elements(t.signed) d
	from transaction_docs t
	
), ct2 as 
(
	select id,    
		jsonb_set( 
	    	d,
	        '{entity_id}',
	        (select u.entity_id from master.users u where u.id::text = d->>'id')::text::jsonb,
	        true
	    ) dt2	
 	from ct
)
update transaction_docs t
set signed = (select jsonb_agg(dt2) from ct2 where ct2.id = t.id)

link

Rename key on a jsonb array

update transaction_docs t1
set signed = (
	select json_agg(el::jsonb - 'id' || jsonb_build_object('user_id', el->'id')) 
    from transaction_docs t2, jsonb_array_elements(t2.signed) as el 
    where t1.id = t2.id    
)

Dump database

# schema only
pg_dump --schema-only -h <host> -U <username> -f file.sql <db_name>

# data only
pg_dump --jobs=4 --format=d --data-only -h <host> -U <username> -f directory_name.dump <db_name>
[--exclude-table 'table_name1' --exclude-table 'table_name2' ...] # exlude multiple tables
[--table 'table_name'] # dump single table

Restore database

psql -d database_name -f dump-file.sql

# restore if dump format=d
## schema
psql -U <username> -d <database_name> -f <schema_file.sql>

## data
pg_restore -v -h <host> -p 5432 -U <username> -d <database_name> <directory_name.dump>

Misc

How to reset postgres' primary key sequence when it falls out of sync ActiveRecord::RecordNotUnique ?

SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

Create database with owner

createdb -O <owner> -E UTF8 database_name

Drop/create database

$ dropdb database_name
$ createdb database_name

Create role/user

$ createuser role_name

Access postgres shell

psql -U postgres

Check running queries

SELECT count(*) as cnt, usename, query, state FROM pg_stat_activity GROUP BY usename, query, state ORDER BY cnt DESC;

Casting:

CAST (column AS type) or column::type
'__table_name__'::regclass::oid: Get oid having a table name

Performance

-- show slow running queries (running more than 2 minutes, 10 seconds etc...)
SELECT now() - query_start as "runtime", usename, datname, state, query, pid
FROM  pg_stat_activity
WHERE now() - query_start > '10 seconds'::interval
ORDER BY runtime DESC;

-- show running queries
SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

General rule for spotting problems with index use, whether caused by OR usage or just missing indexes.

Look at the output of EXPLAIN ANALYZE for significant numbers given for "Rows removed by filter" or "Rows removed by join filter".

Generate a list of your tables in your database with the largest ones first and the percentage of time which they use an index

SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 
ORDER BY 
  n_live_tup DESC;

Using pg_stat_statements ref

The total time a query has occupied against your system in minutes
The average time it takes to run in milliseconds
The query itself

SELECT 
  (total_time / 1000 / 60) as total_minutes, 
  (total_time/calls) as average_time, 
  query 
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;

How do you find the row count for all your tables in Postgres

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

How to find the size in disk of the tables and it's indexes

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

egatjens/postgres-cheatsheet.md