The things you must know

This section is for every command/instruction you should know when using PostgreSQL’s psql!

On almost every command you can add an S to also show system objects and a + to show more information!

Database

  • \l List all databases
  • \c databasename Connect to databasename database

Tables/Relations/Views

  • \d List all tables, views and sequences
  • \d tablename Show the definition for tablename table
  • \dt List tables only
  • \dv List views only
  • \ds List sequences only
  • \dp List permissions for tables, views and sequences

Users

  • \du List all users

Day to day tasks

Some queries for day-to-day tasks using a PostgreSQL server.

Create a new user with a password

create user myuser with password 'very-secure-password';

Create a database for user (which is database owner)

create database newdatabase with owner myuser;

Show stats like table sizes, row count and index size

SELECT t.tablename,
    foo.indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename::text)::regclass)) AS table_size,
    pg_size_pretty(pg_total_relation_size(s.relid::regclass)) AS table_total_size,
    pg_size_pretty(pg_total_relation_size(s.relid::regclass) - pg_relation_size(s.relid::regclass)) AS table_external_size,
    pg_size_pretty(pg_relation_size(quote_ident(foo.indexrelname::text)::regclass)) AS index_size,
        CASE
            WHEN foo.indisunique THEN 'Y'::text
            ELSE 'N'::text
        END AS "index_unique",
    foo.idx_scan AS number_of_scans,
    foo.idx_tup_read AS tuples_read,
    foo.idx_tup_fetch AS tuples_fetched
   FROM pg_tables t
     LEFT JOIN pg_class c ON t.tablename = c.relname
     LEFT JOIN pg_statio_user_tables s ON s.relname = t.tablename
     LEFT JOIN ( SELECT c_1.relname AS ctablename,
            ipg.relname AS indexname,
            x.indnatts AS number_of_columns,
            psai.idx_scan,
            psai.idx_tup_read,
            psai.idx_tup_fetch,
            psai.indexrelname,
            x.indisunique
           FROM pg_index x
             JOIN pg_class c_1 ON c_1.oid = x.indrelid
             JOIN pg_class ipg ON ipg.oid = x.indexrelid
             JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) foo ON t.tablename = foo.ctablename
  WHERE t.schemaname = 'public'::name
  ORDER BY t.tablename, foo.indexname