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

Change Owner for Tables/Functions/Sequences

With these SQL statements you’ll only generate SQL to update the owner on these objects. You’ll have to execute the generated SQL afterwards. In the examples, the public schema is used, change it to your need.

Tables

select 'ALTER TABLE '|| tablename ||' OWNER TO {newOwner};' FROM pg_tables WHERE schemaname = 'public';

Functions

SELECT 'ALTER FUNCTION '
            || quote_ident(n.nspname) || '.' 
            || quote_ident(p.proname) || '(' 
            || pg_catalog.pg_get_function_identity_arguments(p.oid)
            || ') OWNER TO {newOwner};'
          , E'\n' AS _sql
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';

Sequences

SELECT 'ALTER SEQUENCE '
            || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname)
            || ' OWNER TO {newOwner};'
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'public' AND c.relkind = 'S';