Skip to content

PSQL Shell

Renaming

Renaming a table

ALTER TABLE table_name
RENAME TO new_table_name;

Renaming a column

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

PSQL Cheat Sheet

Investigating

List all tables

  • \dt for the current schema
  • \dt schema_name.*
  • \dt *.table_name
    • to find all tables with that name

List all column names

\d+ <table_name>

SELECT *
FROM information_schema.columns
WHERE table_schema = 'your_schema'
AND table_name   = 'your_table';

Debugging PSQL

Long running queries

SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Blocked PIDs

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Recover your database after your computer suddenly turns off

  • Make sure there's no postgres processes
    • ps aux | ag postgres
  • rm -f /usr/local/var/postgres/postmaster.pid
  • brew services restart postgresql
  • /usr/local/opt/postgres/bin/createuser -s postgres
    • create the postgres user

Create a CSV

psql -c "COPY (<select query>) TO STDOUT WITH CSV"
sudo -i -u postgres
  • -i: launch shell with super user privileges
  • -u: user

Last update: 2022-09-23