Cheatsheet: PostgreSQL

Last updated 2026-08-25

Connecting

connect to a local database as a user

psql -U {{username}} {{database}}

connect to a remote server, specifying host and port

psql -h {{server}} -p {{port}} -U {{username}} -d {{database}}

connect using a connection URI

psql "postgresql://{{username}}@{{server}}:{{port}}/{{database}}"

connect and immediately run one command, then exit

psql -U {{username}} -d {{database}} -c "SELECT version();"

run a .sql file against a database

psql -U {{username}} -d {{database}} -f script.sql

quit the psql prompt

\q

psql meta-commands

list all databases

\l

connect to a different database

\c {{database}}

list tables in the current database

\dt

describe a table (columns, types, indexes)

\d {{table_name}}

list views

\dv

list indexes

\di

list users/roles

\du

list schemas

\dn

show table sizes

\dt+

toggle expanded (vertical) output — great for wide rows

\x

show command history

\s

get help on SQL commands

\h CREATE TABLE

get help on psql meta-commands

\?

Databases and roles

create a database

CREATE DATABASE {{database}};

drop a database

DROP DATABASE {{database}};

create a role/user with a password that can log in

CREATE ROLE my_user WITH LOGIN PASSWORD 'my_password';

grant all privileges on a database to a user

GRANT ALL PRIVILEGES ON DATABASE {{database}} TO my_user;

make a user a superuser

ALTER ROLE my_user WITH SUPERUSER;

change a user's password

ALTER ROLE my_user WITH PASSWORD 'new_password';

list roles and their attributes

SELECT rolname, rolsuper, rolcreatedb FROM pg_roles;

Tables

create a table

CREATE TABLE {{table_name}} (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

add a column

ALTER TABLE {{table_name}} ADD COLUMN email TEXT;

rename a column

ALTER TABLE {{table_name}} RENAME COLUMN name TO full_name;

drop a column

ALTER TABLE {{table_name}} DROP COLUMN email;

drop a table

DROP TABLE {{table_name}};

truncate a table (delete all rows, keep schema)

TRUNCATE TABLE {{table_name}};

create an index on a column

CREATE INDEX idx_{{table_name}}_name ON {{table_name}} (name);

Querying

select rows with a filter

SELECT * FROM {{table_name}} WHERE name = 'value';

upsert a row (insert or update on conflict)

INSERT INTO {{table_name}} (id, name)
VALUES (1, 'value')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

return the inserted/updated row

INSERT INTO {{table_name}} (name) VALUES ('value') RETURNING id;

aggregate with GROUP BY

SELECT name, COUNT(*) FROM {{table_name}} GROUP BY name;

window function example: running total

SELECT id, name, SUM(id) OVER (ORDER BY id) AS running_total FROM {{table_name}};

explain a query's execution plan

EXPLAIN ANALYZE SELECT * FROM {{table_name}} WHERE name = 'value';

Backup and restore

dump a single database to a SQL file

pg_dump -U {{username}} -h {{server}} {{database}} > backup.sql

dump a database in the custom (compressed) format

pg_dump -U {{username}} -Fc {{database}} > backup.dump

restore a plain SQL dump

psql -U {{username}} -d {{database}} -f backup.sql

restore a custom-format dump

pg_restore -U {{username}} -d {{database}} backup.dump

dump all databases and roles on a server

pg_dumpall -U {{username}} > all_databases.sql

Server operations

start the server (Homebrew install)

brew services start postgresql

stop the server (Homebrew install)

brew services stop postgresql

start/stop the server (systemd install)

sudo systemctl start postgresql
sudo systemctl stop postgresql

show currently running queries

SELECT pid, query, state FROM pg_stat_activity WHERE state != 'idle';

cancel a running query

SELECT pg_cancel_backend(pid);

terminate a connection

SELECT pg_terminate_backend(pid);

Cheatsheet: PostgreSQL

See also