Applications - Databases - PostgreSQL - Notes - Miscellaneous

Show all databases:

\l

Connect to a database:

\c DATABASENAME

Show tables with size:

 \dt+

Show column details (describe table):

\d+ TABLENAME

Show schemas:

\dn

Show the 20 biggest tables with total size from big to small:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Source:PostgreSQL Wiki - Disk Usage

Show what is running now:

SELECT * FROM pg_stat_activity WHERE state != 'idle';

Source: Documentation -PostgreSQL 10 - 28.2. The Statistics Collector

SELECT schemaname,tablename 
FROM pg_catalog.pg_tables 
WHERE schemaname != 'pg_catalog' 
AND schemaname != 'information_schema' 
ORDER BY schemaname,tablename;

Source: ObjectRocket - Connect to PostgreSQL and Show the Table Schema