Postgresql cheat sheet

postgresql.md

Installation

brew install postgres

# Start the service and register to autostart on boot
brew services start postgresql

# Create user
createdb $(whoami)

# Login
psql

Commands

• \l: List databases
• \dt: List tables
• \du: List user roles
• \z table_name: List access privileges for a given table
• \copy (SELECT * FROM table_name) to 'output.csv' csv header: Export query to local machine in csv format
• \copy (SELECT row_to_json(r) FROM table_name as r) to 'output.json': Export query to local machine in json newline format

Queries

Get size info for each table (source):

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS index
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS table
  FROM (
  SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS table_name
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a order by total_bytes desc
) a;

Issues

'/usr/lib/libpq.5.dylib' (no such file)

If you get the following error:

E   ImportError: dlopen(/Users/justinlam/Library/Caches/pypoetry/virtualenvs/airflow-stallone-Knw1qgia-py3.8/lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so, 0x0002): Library not loaded: '/usr/local/opt/postgresql/lib/libpq.5.dylib'
E     Referenced from: '/Users/justinlam/Library/Caches/pypoetry/virtualenvs/airflow-stallone-Knw1qgia-py3.8/lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so'
E     Reason: tried: '/usr/local/opt/postgresql/lib/libpq.5.dylib' (no such file), '/usr/local/lib/libpq.5.dylib' (no such file), '/usr/lib/libpq.5.dylib' (no such file), '/usr/local/Cellar/postgresql@14/14.5_4/lib/libpq.5.dylib' (no such file), '/usr/local/lib/libpq.5.dylib' (no such file), '/usr/lib/libpq.5.dylib' (no such file)

Then to fix it, find the missing file:

$ find / -name libpq.5.dylib
/usr/local/Cellar/postgresql@14/14.5_4/lib/postgresql@14/libpq.5.dylib
/usr/local/Cellar/postgresql@14/14.5_4/lib/libpq.5.dylib
/usr/local/Cellar/libpq/14.5/lib/libpq.5.dylib
...

Then create a symlink to the missing file:

ln -s /usr/local/Cellar/postgresql@14/14.5_4/lib/postgresql@14/libpq.5.dylib /usr/local/opt/postgresql/lib/libpq.5.dylib