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?
Explore Topics
#PostgreSQL#PostgreSQLCheatSheet#Database#Intermediate#PsqlBasics#SQLEssentials#IndexesEXPLAIN#CommonDataTypes#Databases#CheatSheet#SkillVeris
