PostgreSQL Cheatsheet

PostgreSQL is a powerful, open-source relational database known for reliability and rich features. This cheatsheet covers psql and common SQL operations.

psql Basics

Connect and navigate.

psql -U user -d mydb     # connect
\l                       # list databases
\c dbname                # connect to db
\dt                      # list tables
\d tablename             # describe table
\q                       # quit

Creating Tables

Define schema with types.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  age INT CHECK (age >= 0),
  created_at TIMESTAMPTZ DEFAULT now()
);

Altering Tables

Change existing schema.

ALTER TABLE users ADD COLUMN name TEXT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Indexes

Speed up queries.

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX ON users (email);
DROP INDEX idx_users_email;

Upsert

Insert or update on conflict.

INSERT INTO users (id, email)
VALUES (1, 'a@x.com')
ON CONFLICT (id)
DO UPDATE SET email = EXCLUDED.email;

JSON

Query JSONB columns.

SELECT data->>'name' FROM events;
SELECT * FROM events WHERE data->>'type' = 'click';
UPDATE events SET data = data || '{"seen": true}';

Transactions

Group statements atomically.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

Useful Queries

Inspect the database.

SELECT version();
SELECT pg_size_pretty(pg_database_size('mydb'));
SELECT * FROM pg_stat_activity;

---

PostgreSQL is feature-rich and battle-tested. Master the basics, then explore window functions, CTEs, full-text search, and extensions.

For full documentation, see https://www.postgresql.org/docs/