PSQL config

.psqrc.config

-----------------------------------------
-- psqlrc file to set psql preferences --
-- Author : Prashanth Goriparthi       --
-----------------------------------------

-- source : https://opensourcedbms.com/dbms/psqlrc-psql-startup-file-for-postgres/
-- in Linux - ~/.psqlrc
-- in Windows - %AppData%\postgresql\psqlrc.conf
	-- C:\Users\[Login id]\AppData\Roaming\postgresql\psqlrc.conf

-- ------------------------------------------

\set QUIET ON

-- colored prompt
-- \set PROMPT1 '%[%033[1;32;40m%]%M:%>; %n@%/%[%033[0m%]% # '

-- plain prompt; jdbc url type
select current_schema()  \gset
\echo :current_schema
\set PROMPT1 '%n@%m:%>/%/%x% [schema = %:current_schema:] # '
-- \set PROMPT1 '%n@%m:%>/%/%x% # '
\set PROMPT2 ' %l %R > '

\set PAGER OFF
-- \set HISTFILE ~/.psql_history- :HOST - :DBNAME -- Remove space between : and DBNAME
\set HISTFILE C:/Users/darshak.shah/AppData/Roaming/postgresql/psql_history- :HOST - :DBNAME -- Remove space between : and DBNAME
\set HISTSIZE 2000

-- enable if to print internal PG query
-- \set ECHO_HIDDEN ON

\set COMP_KEYWORD_CASE upper

\timing
\x auto
-- \encoding unicode

-- \pset null 'NULL'
\pset border 2

\set QUIET OFF

-- \echo '\nCurrent Host Server Date Time : '`date` '\n'

\echo 'Administrative queries:\n'
\echo '\t\t\t:settings\t-- Server Settings'
\echo '\t\t\t:conninfo\t-- Server connections'
\echo '\t\t\t:activity\t-- Server activity'
\echo '\t\t\t:login\t-- Current Connection'
\echo '\t\t\t:locks\t\t-- Lock info'
\echo '\t\t\t:waits\t\t-- Waiting queires'
\echo '\t\t\t:dbsize\t\t-- Database Size'
\echo '\t\t\t:tablesize\t-- Tables Size'
\echo '\t\t\t:uselesscol\t-- Useless columns'
\echo '\t\t\t:uptime\t\t-- Server uptime'
\echo '\t\t\t:ver OR :version -- Server version'
\echo '\t\t\t:param\t\t-- find parameter value - :param \'%mem%\''
\echo '\t\t\t:menu\t\t-- Help Menu'
\echo '\t\t\t\\h\t\t-- Help with SQL commands'
\echo '\t\t\t\\?\t\t-- Help with psql commands\n'

\echo 'Development queries:\n'
\echo '\t\t\t:sp\t\t-- Current Search Path'
\echo '\t\t\t:cd SCHEMA_NAMEs\t-- set Search Path'
\echo '\t\t\t:clear\t\t-- Clear screen'
\echo '\t\t\t:ll\t\t-- List\n'

-- Administration queries

-- \set menu '\\i ~/.psqlrc'
\set menu '\\i C:/Users/darshak.shah/AppData/Roaming/postgresql/psqlrc.conf'

\set settings 'select name, setting,unit,context from pg_settings;'

\set locks  'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement 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 conninfo 'select usename, state, count(*) from pg_stat_activity group by usename, state order by 3 desc, 2;'

\set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, query, state from pg_stat_activity;'

\set login 'SELECT CURRENT_USER usr, :\'HOST\' host, inet_server_port() port, current_schema();'

\set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'

\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

\set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_relation_size(C.oid) DESC LIMIT 40;'

\set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'
\set date 'select now();'

\set version 'SELECT version();'
\set ver 'SELECT version();'

\set param 'select name, setting, unit, context, short_desc from pg_settings where name like '

-- Development queries:

\set sp 'SHOW search_path;'
\set cd 'SET search_path='
\set clear '\\! cls;'
\set ll '\\! ls -lrt;'

-- ------------------------------------------
-- additional details on login

\echo 'Additional details:\n'

SELECT version();
SHOW search_path;
:login
-- \dn