PostgreSQL Cheat Sheet

Core PostgreSQL commands covering psql navigation, SQL queries, indexing, and common data types for building and tuning relational databases.

2 PagesIntermediateMar 8, 2026

psql Basics

Connecting and navigating with the psql CLI.

bash

psql -U user -d mydb -h localhost   # Connect\l                    # List databases\c mydb               # Connect to a database\dt                   # List tables\d users              # Describe a table\du                   # List roles\timing               # Toggle query timing\q                    # Quit

SQL Essentials

Common DDL and DML statements.

sql

CREATE TABLE users (  id SERIAL PRIMARY KEY,  email TEXT UNIQUE NOT NULL,  created_at TIMESTAMPTZ DEFAULT now());INSERT INTO users (email) VALUES ('a@b.com') RETURNING id;SELECT * FROM users WHERE email ILIKE '%example%';UPDATE users SET email = 'x@y.com' WHERE id = 1;WITH recent AS (  SELECT * FROM orders WHERE created_at > now() - interval '7 days')SELECT * FROM recent;

Indexes & EXPLAIN

Creating indexes and reading query plans.

sql

CREATE INDEX idx_users_email ON users(email);CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);EXPLAIN ANALYZESELECT * FROM users WHERE email = 'a@b.com';

Common Data Types

Frequently used PostgreSQL column types.

SERIAL- auto-incrementing 4-byte integer, backed by a sequence
JSONB- binary JSON storage that supports indexing and containment queries
UUID- 128-bit universally unique identifier
TEXT- variable-length string with no length limit
TIMESTAMPTZ- timestamp stored with timezone awareness
NUMERIC(p,s)- exact fixed-precision decimal number
ARRAY- e.g. INTEGER[], a native array column type
BOOLEAN- true/false/null value

Admin & Maintenance

Backup, privileges, and housekeeping.

VACUUM ANALYZE- reclaims dead tuple space and refreshes planner statistics
pg_dump- exports a database or table to a backup file
pg_restore- restores a database from a pg_dump archive
GRANT / REVOKE- adds or removes privileges on objects for a role
REINDEX- rebuilds a corrupted or bloated index

Pro Tip

Use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN — it shows actual row counts and buffer hits/misses, not just planner estimates, which is what you need to diagnose a slow query.

Was this cheat sheet helpful?

Figma to Code Workflow Cheat Sheet

MySQL Cheat Sheet