psql Basics

7 examples for daily psql usage - 5 basic and 2 intermediate.

Prerequisites

psql --version   # match major to server when possible

• Install postgresql-client-18 (or your target major) on admin workstations.
• Store connection defaults in ~/.pgpass or a secrets manager - not shell history.

Basic Examples

1. Connect to a Database

psql "postgresql://app_ro:****@db.internal:5432/myapp?sslmode=require"
# or
psql -h db.internal -p 5432 -U app_ro -d myapp

• sslmode=require enforces TLS on the wire.
• Wrong database name still connects - you may silently query the wrong schema.
• Use read-only roles for exploratory queries on production.

2. Switch Database (\c)

\c myapp
\c myapp app_ro
\conninfo

• \c dbname [username] opens a new connection in the same psql session.
• \conninfo prints host, port, user - verify before destructive work.
• Reconnecting resets session GUCs like search_path unless set per-role.

Related: psql Scripting - non-interactive runs

3. List Objects (\dt, \d)

\dt public.*
\d+ orders
\dn
\df public.*

• \d+ adds storage size and description comments.
• \dn lists schemas - critical in multi-tenant layouts.
• Meta-commands query catalogs - they respect your current search_path.

4. Timing Queries (\timing)

\timing on
SELECT count(*) FROM orders WHERE created_at > now() - interval '7 days';

• Shows server execution time, not network round-trip.
• Turn on for every ad-hoc performance investigation.
• Pair with EXPLAIN (ANALYZE, BUFFERS) for plan-level detail.

Related: Index & Query Rules - EXPLAIN before merge

5. Safe LIMIT Habits

-- Always bound exploratory SELECTs on large tables
SELECT id, status, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 50;
 
-- Avoid accidental full scans
SELECT * FROM events LIMIT 100;  -- still reads if no index-friendly plan

• LIMIT without ORDER BY returns an arbitrary slice.
• SELECT * on wide JSONB rows transfers megabytes per row.
• Use read replicas for analyst exploration when available.

Intermediate Examples

6. Output Formats

\x on
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();
 
\a
\t
SELECT setting FROM pg_settings WHERE name = 'max_connections';

• Expanded display (\x) helps wide catalog queries.
• \a aligned off + \t tuples only = script-friendly TSV.
• \pset footer off removes row counts for machine parsing.

7. Read-Only Session Guardrails

BEGIN READ ONLY;
SELECT sum(amount) FROM payments WHERE paid_at >= current_date;
ROLLBACK;

# Connect explicitly as read-only role
psql -U app_ro -d myapp -c "SELECT 1"

• BEGIN READ ONLY blocks accidental UPDATE in the same transaction.
• Prefer a role without INSERT/UPDATE/DELETE on production.
• Some teams alias psql_prod='psql -U app_ro ...' in approved jump boxes only.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

49454e4f4d5f434e4f5904434556494d43451b1a191956181a181c1a1d