psql is PostgreSQL’s interactive terminal — fast, scriptable and available on every server with PostgreSQL installed. Its backslash meta-commands make exploring a database quick once you know them.

Connect

sudo -u postgres psql                          # local superuser via peer auth
psql -h 127.0.0.1 -U myapp -d myapp            # password auth over TCP
psql "postgresql://[email protected]:5432/myapp?sslmode=require"
psql -h db.example.com -p 5432 -U myapp -d myapp -c "SELECT now();"   # one command

Avoid typing passwords: put them in ~/.pgpass (host:port:db:user:password, mode 600) or use the PGPASSWORD variable for one-off scripts.

Inside psql, switch databases with \c otherdb.

Meta-commands: look around

Command Shows
\l Databases
\c dbname Connect to a database
\dn Schemas
\dt Tables in the search path
\dt *.* Tables in all schemas
\d tablename Columns, indexes, constraints of a table
\d+ tablename Plus size, storage, description
\di Indexes
\dv / \dm Views / materialized views
\df Functions
\du Roles
\dp tablename Privileges
\dx Installed extensions
\conninfo Current connection
\? All meta-commands
\h CREATE INDEX SQL syntax help

Patterns work: \dt public.order*.

Output formatting

\x            -- toggle expanded output (one column per line) — great for wide rows
\x auto       -- expanded only when rows don't fit
\pset null '∅' -- show NULLs visibly
\pset border 2
\timing       -- show query execution time

Editing and history

\e            -- open the last query in $EDITOR
\g            -- re-run the last query
\s            -- history
\i file.sql   -- run a SQL file
\o out.txt    -- send output to a file (\o to stop)
\! ls         -- run a shell command
\q            -- quit

Export and import CSV

\copy runs on the client side (files on your machine, no superuser needed):

\copy (SELECT id, email FROM users WHERE created_at > now() - interval '7 days') TO 'new_users.csv' WITH CSV HEADER
\copy users(email, name) FROM 'import.csv' WITH CSV HEADER

(COPY … TO '/path' without the backslash writes on the server and requires privileges.)

Useful queries

-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- Largest tables (including indexes and TOAST)
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- Running queries
SELECT pid, usename, state, now() - query_start AS runtime, left(query, 80)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC;

-- Cancel or terminate
SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

-- Locks waiting
SELECT pid, wait_event_type, wait_event, left(query, 60) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Scripting with psql

psql -h 127.0.0.1 -U myapp -d myapp -v ON_ERROR_STOP=1 -f migration.sql
psql -At -c "SELECT count(*) FROM users;" myapp        # unaligned, tuples only → just the number
psql -X -q -c "…"                                      # ignore .psqlrc, quiet

ON_ERROR_STOP=1 makes a script stop at the first error (and return a non-zero exit code) instead of continuing.

Transactions safety net

BEGIN;
DELETE FROM orders WHERE status = 'test';
-- check the row count reported…
ROLLBACK;   -- or COMMIT;

Set \set AUTOCOMMIT off in risky sessions so nothing is committed without an explicit COMMIT.

A handy ~/.psqlrc

\set QUIET 1
\pset null '∅'
\x auto
\timing on
\set HISTSIZE 5000
\set HISTCONTROL ignoredups
\set PROMPT1 '%n@%/%R%# '
\set QUIET 0

Related: PostgreSQL users and databases, pg_dump and pgAdmin for a graphical alternative.

  • PostgreSQL
  • psql
  • Cheat Sheet