postgres-cheatsheet.md

Magic words:

psql -U postgres

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

• \q: Quit/Exit
• \c __database__: Connect to a database
• \d __table__: Show table definition including triggers
• \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
• \l: List databases
• \dn: List schemas
• \df: List functions
• \dv: List views
• \df+ __function : Show function SQL code. \x before pretty-formats it
• SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function
• SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition)
• SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use
• SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use
• show statement_timeout;: Show current user's statement timeout
• SELECT pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';: Show queries being executed at a certain DB. Can also display query time, etc.

Casting:

• CAST (column AS type) or column::type

Constraints:

• ALTER TABLE my_table ADD CONSTRAINT my_fk FOREIGN KEY (my_field) REFERENCES my_foreign_table ON DELETE CASCADE;