PostgreSQL Cheat Sheet

PostgreSQL is the most advanced open-source relational database. This cheat sheet covers essential commands and features.

psql Commands

\l              # List databases
\c dbname       # Connect to database
\dt             # List tables
\d tablename    # Describe table
\di             # List indexes
\du             # List users/roles
\q              # Quit
\x              # Toggle expanded display

Database Management

CREATE DATABASE mydb;
DROP DATABASE mydb;
ALTER DATABASE mydb RENAME TO newdb;

Table Management

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER CHECK (age >= 0),
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
DROP TABLE users;

JSON Support

-- JSONB column
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- Query JSON
SELECT data->>'name' AS name FROM events;
SELECT * FROM events WHERE data->>'type' = 'click';
SELECT * FROM events WHERE data @> '{"status": "active"}';

Indexes

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(name DESC);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_events_data ON events USING GIN(data);
DROP INDEX idx_users_email;

Common Queries

-- Pagination
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;

-- Upsert
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- CTE
WITH active AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT count(*) FROM active;

-- Window function
SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Data Types

SERIAL          # Auto-increment integer
BIGSERIAL       # Auto-increment bigint
VARCHAR(n)      # Variable-length string
TEXT            # Unlimited string
INTEGER         # 4-byte integer
BIGINT          # 8-byte integer
DECIMAL(p,s)    # Exact numeric
BOOLEAN         # true/false
TIMESTAMP       # Date and time
JSONB           # Binary JSON (indexed)
UUID            # UUID type
ARRAY           # Array type (e.g., TEXT[])

Useful Functions

NOW()                 # Current timestamp
CURRENT_DATE          # Current date
GENERATE_SERIES(1,10) # Number series
STRING_AGG(col, ',')  # Concatenate strings
ARRAY_AGG(col)        # Aggregate to array
COALESCE(a, b)        # First non-null
NULLIF(a, b)          # NULL if equal

Practice

Format your PostgreSQL queries with the SQL Formatter and generate test data with CSV to SQL.