psql - PostgreSQL Interactive Terminal

psql is what you use when the fancy GUI tools decide to take a coffee break. It's PostgreSQL's command-line interface - ugly as hell but gets the job done when everything else fails.

The tool connects to PostgreSQL databases and lets you run SQL queries plus a bunch of meta-commands that start with backslashes. These meta-commands are lifesavers once you memorize them, but expect to Google "\dt vs \d+ vs \dn" about 47 times in your first week.

Why You'll End Up Using It

GUI tools like pgAdmin are pretty until they crash during a production incident and you're stuck with psql anyway. When your DataGrip license expires or TablePlus decides to freeze on a 10GB query result, psql is there looking smug as fuck.

Connection strings look like someone's password manager exploded:

psql \"postgresql://username:password@hostname:5432/database?sslmode=require\"

Save them in .pgpass or you'll never remember the syntax. Trust me on this one - I've wasted 2 hours debugging connection issues that turned out to be a missing ? in the connection string.

The Meta-Commands That Actually Matter

Skip the 80+ meta-commands bullshit. Here are the 10 you'll actually use:

• `\l` - List databases (prepare for a wall of text)
• `\c database_name` - Switch databases (finally)
• `\dt` - Show tables (add + if you hate yourself and want to wait forever)
• `\d table_name` - Describe table (the only way to see column types without writing SQL)
• `\x` - Toggle expanded mode (lifesaver for wide tables, annoying for small ones)
• `\q` - Quit (most important command)

Pro tip: \dt without a pattern on a large database will list 400+ tables and you'll regret it. Use `\dt pattern*` unless you enjoy scrolling.

PostgreSQL 18 and What Actually Changed

With PostgreSQL 18 released on September 26, 2025, you get async I/O improvements that can speed things up 3x in certain scenarios. The UUIDv7 functions are actually useful unlike the marketing promises.

The big win is JSON_TABLE support - finally, you can query JSON without writing nested bullshit that breaks your brain. Plus logical replication improvements that might actually work without mysteriously failing at 2am.

But here's the thing - if you're still on PostgreSQL 12 because your ops team is afraid of upgrades, none of this matters. psql works the same frustrating way it always has, which is both a blessing and a curse.

Now, before you ask "why not just use mysql or another database CLI tool?" - let's see how psql stacks up against the competition.

Commands That Don't Suck

The meta-command system turns SELECT * FROM information_schema.tables into \dt. Once you memorize them, you'll wonder how you survived without them. Until then, you'll Google the same commands daily.

The Commands You'll Use Every Day:

• \l - List databases (brace yourself for the spam)
• \c database_name - Switch databases without reconnecting
• \dn - Show schemas (useful when someone creates 47 schemas)
• \dt - Show tables (will murder your terminal on large DBs)
• \d table_name - Describe table structure (lifesaver for forgotten column names)

When Things Go Wrong:

• `\df` - List functions (when you can't remember that custom function name)
• `\dp` - Show permissions (when "permission denied" ruins your day)
• `\di` - Show indexes (when your query runs for 30 seconds instead of 30ms)

Output Formatting That Doesn't Hurt Your Eyes

The default output looks like ass. Here's how to fix it:

• `\x` - Expanded mode for wide tables (toggle this constantly)
• `\x auto` - Only expand wide tables (actually useful)
• `\pset null 'NULL'` - Show NULL values instead of empty spaces
• ` iming on` - See how slow your queries really are

Real talk: Use `\x auto` instead of \x. It only expands tables that actually need it instead of making everything look like shit.

File Operations for When You're Not Completely Fucked

The `\copy` command works without server file access, unlike `COPY` which will fail with permission errors that make you question your life choices:

-- Export that works from any client
\copy (SELECT * FROM users WHERE created_at > '2025-01-01') TO 'recent_users.csv' CSV HEADER

-- Import without dealing with server filesystem bullshit
\copy temp_table FROM 'data.csv' CSV HEADER

Gotcha alert: `\copy` and `COPY` are different. COPY runs on the server and needs file permissions. \copy runs on the client and just works. Learn this now or waste 2 hours later.

Connection Strings From Hell

SSL connections look like someone's regex nightmare:

## SSL with certificates (because security theater)
psql \"sslmode=require sslcert=client.crt sslkey=client.key host=db.example.com dbname=production user=readonly\"

## The connection string that actually works in production
export PGPASSWORD='your_password_here'
psql -h db.example.com -U readonly -d production

Save connection info in ~/.pgpass with format: hostname:port:database:username:password. Read about environment variables for easier connection management.

Production Debugging Commands

When shit hits the fan at 3am and your pager is going nuts:

-- See what's actually running (and killing your DB)
SELECT pid, usename, application_name, state, query_start,
       NOW() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < NOW() - INTERVAL '30 seconds'
ORDER BY query_start;

-- Find the table that's eating all your disk space (saved my ass during a runaway log table incident)
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;

-- See blocked queries (when everything is hanging and users are screaming)
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON (blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.granted = true)
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Nuclear option: Kill that runaway query (use with extreme prejudice)
SELECT pg_terminate_backend(12345); -- Replace 12345 with the actual PID

Real talk: I once spent 4 hours tracking down a "connection leak" that turned out to be a single query with a missing WHERE clause scanning 50M rows. Always run iming on so you know when your query takes 45 seconds instead of the expected 2ms.

Don't use pg_cancel_backend() - it's polite and slow. Use pg_terminate_backend() when you need that query dead immediately. PostgreSQL's system catalogs are your friend when everything is on fire.