psqlrc

\x auto
\set ON_ERROR_ROLLBACK interactive
\set COMP_KEYWORD_CASE upper
\set HISTFILE ~/.psql_history/:DBNAME

-- http://www.postgresql.org/docs/9.3/static/app-psql.html#APP-PSQL-PROMPTING
\set PROMPT1 '%[%033[1m%]%/%R%[%033[0m%]%# '
-- PROMPT2 is printed when the prompt expects more input, like when you type
-- SELECT * FROM<enter>. %R shows what type of input it expects.
\set PROMPT2 '[more] %R > '

\pset pager off
\pset null '(null)'

-- From https://github.com/heroku/heroku-pg-extras/blob/main/commands
-- via https://github.com/luax
\set blocking 'SELECT bl.pid AS blocked_pid, ka.query AS blocking_statement, now() - ka.query_start AS blocking_duration, kl.pid AS blocking_pid, a.query AS blocked_statement, now() - a.query_start AS blocked_duration FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;'
\set connections 'SELECT usename AS username, COUNT(*) FROM pg_stat_activity GROUP BY username;'
\set connections_by_application 'SELECT application_name, COUNT(*) FROM pg_stat_activity GROUP BY application_name ORDER by application_name;'
\set locks 'SELECT pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, pg_locks.mode, pg_stat_activity.query AS query_snippet, age(now(),pg_stat_activity.query_start) AS "age" FROM pg_stat_activity,pg_locks left OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE pg_stat_activity.query <> ''<insufficient privilege>'' AND pg_locks.pid = pg_stat_activity.pid AND pg_stat_activity.pid <> pg_backend_pid() AND relname is not null order by query_start;'
\set locks_exclusive 'SELECT pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, pg_locks.mode, pg_stat_activity.query AS query_snippet, age(now(),pg_stat_activity.query_start) AS "age" FROM pg_stat_activity,pg_locks left OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE pg_stat_activity.query <> ''<insufficient privilege>'' AND pg_locks.pid = pg_stat_activity.pid AND pg_stat_activity.pid <> pg_backend_pid() AND pg_locks.mode LIKE ''%Exclusive%'' AND relname is not null order by query_start;'
\set long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS query FROM pg_stat_activity WHERE pg_stat_activity.query <> ''::text'' AND state <> ''idle'' AND now() - pg_stat_activity.query_start > interval ''5 minutes'' ORDER BY now() - pg_stat_activity.query_start DESC;'