/cheatsheets /postgresql/psql-cheat-sheet

psql cheat sheet: connect, list, describe and run SQL files

updated 2026-10-20

tool: PostgreSQL
versions: psql 14+
os: any

The psql commands used every day. Backslash commands are interpreted by psql itself, not sent to the server, and do not end with a semicolon; SQL statements do.

Connect

bash

psql -h localhost -p 5432 -U app -d appdb
psql "postgresql://app@localhost:5432/appdb?sslmode=require"
psql -d appdb -c "select now()"                 # run one command and exit
psql -d appdb -f migrate.sql -v ON_ERROR_STOP=1 # run a file, stop at the first error

The password comes from a prompt, PGPASSWORD, or ~/.pgpass.

Meta-commands

Command	Does
\l	list databases
\c dbname	connect to another database
\dn	list schemas
\dt [schema.*]	list tables
\d table	columns, indexes and constraints of a table
\d+ table	the same, with size and storage details
\di, \dv, \df	indexes, views, functions
\du	roles
\x [auto]	expanded (vertical) output for wide rows
\timing	show how long each statement takes
\e	edit the last query in $EDITOR
\i file.sql	run a file
\copy (query) to file.csv csv header	export to a CSV on the client machine
\conninfo	current connection
\q	quit

The same information with SQL

Useful from any client or driver. Run here against PostgreSQL 18 with PGlite:

bash

npm init -y >/dev/null
npm install --silent --no-audit --no-fund @electric-sql/pglite@0.5.10 >/dev/null 2>&1
cat > q.mjs <<'EOF'
import { PGlite } from "@electric-sql/pglite";
const db = new PGlite();
await db.exec(`create table users (id int primary key, email text not null unique);
               create index users_email_lower on users (lower(email));`);
const q = async (sql) => console.table((await db.query(sql)).rows);
await q(`select current_database() as db, current_user as "user", current_setting('server_version') as version`);
await q(`select table_schema, table_name from information_schema.tables where table_schema = 'public'`);
await q(`select column_name, data_type, is_nullable from information_schema.columns where table_name = 'users' order by ordinal_position`);
await q(`select indexname, indexdef from pg_indexes where tablename = 'users' order by indexname`);
EOF
node q.mjs

Output

┌─────────┬────────────┬────────────┬─────────┐
│ (index) │ db         │ user       │ version │
├─────────┼────────────┼────────────┼─────────┤
│ 0       │ 'postgres' │ 'postgres' │ '18.6'  │
└─────────┴────────────┴────────────┴─────────┘
┌─────────┬──────────────┬────────────┐
│ (index) │ table_schema │ table_name │
├─────────┼──────────────┼────────────┤
│ 0       │ 'public'     │ 'users'    │
└─────────┴──────────────┴────────────┘
┌─────────┬─────────────┬───────────┬─────────────┐
│ (index) │ column_name │ data_type │ is_nullable │
├─────────┼─────────────┼───────────┼─────────────┤
│ 0       │ 'id'        │ 'integer' │ 'NO'        │
│ 1       │ 'email'     │ 'text'    │ 'NO'        │
└─────────┴─────────────┴───────────┴─────────────┘
┌─────────┬─────────────────────┬─────────────────────────────────────────────────────────────────────────────┐
│ (index) │ indexname           │ indexdef                                                                    │
├─────────┼─────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 0       │ 'users_email_key'   │ 'CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email)'   │
│ 1       │ 'users_email_lower' │ 'CREATE INDEX users_email_lower ON public.users USING btree (lower(email))' │
│ 2       │ 'users_pkey'        │ 'CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)'           │
└─────────┴─────────────────────┴─────────────────────────────────────────────────────────────────────────────┘

Sources

errors · postgresqlPostgreSQL: ERROR: relation "users" does not existPostgreSQL reports relation does not exist (42P01) when a table is missing, in a schema outside the search_path, or was created with a quoted mixed-case name.commands · postgresqlPostgreSQL upsert: INSERT … ON CONFLICT DO UPDATE and MERGEInsert a row or update it if the key exists with INSERT ... ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col, skip duplicates with DO NOTHING, or use MERGE.codes · postgresqlPostgreSQL SQLSTATE error codes: 23505, 23503, 42P01, 40001 and moreThe PostgreSQL SQLSTATE codes apps handle most: 23505 unique violation, 23503 foreign key, 23502 not null, 42P01 missing table, 40001 serialization failure.

Connect

Meta-commands

The same information with SQL

Sources

Related pages