POSTGRESQL
CHEAT SHEET.

Installation & Setup

# macOS
brew install postgresql
brew services start postgresql

# Ubuntu / Debian
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

# Windows (Chocolatey)
choco install postgresql

# Connect
psql -U postgres
psql -d mydb -U user -h localhost -p 5432

# Common psql commands
\l             # list databases
\c dbname      # connect to database
\dt            # list tables
\d table       # describe table
\di            # list indexes
\dv            # list views
\df            # list functions
\du            # list users
\?             # help
\q             # quit

Databases

# Create database
CREATE DATABASE mydb;

# Drop database
DROP DATABASE mydb;

# Rename database
ALTER DATABASE mydb RENAME TO mydb_new;

# Set owner
ALTER DATABASE mydb OWNER TO new_owner;

Data Types

Table Operations

CREATE TABLE

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    preferences JSONB DEFAULT '{}'::jsonb
);

ALTER TABLE

# Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

# Drop column
ALTER TABLE users DROP COLUMN phone;

# Rename column
ALTER TABLE users RENAME COLUMN username TO user_name;

# Change data type
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

# Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

# Drop constraint
ALTER TABLE users DROP CONSTRAINT unique_email;

# Set default
ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;

# Drop default
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;

DROP TABLE

DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP TABLE users CASCADE;  // also drops dependent objects

CRUD Operations

INSERT

# Single row
INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 25);

# Multiple rows
INSERT INTO users (username, email, age) VALUES
    ('bob', 'bob@example.com', 30),
    ('charlie', 'charlie@example.com', 35);

# With RETURNING
INSERT INTO users (username, email) VALUES ('dave', 'dave@ex.com')
RETURNING id, created_at;

# From SELECT
INSERT INTO users_archive SELECT * FROM users WHERE age > 60;

SELECT

# Basic
SELECT * FROM users;
SELECT username, email FROM users;

# DISTINCT
SELECT DISTINCT age FROM users;

# WHERE
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE username LIKE 'A%';
SELECT * FROM users WHERE email IS NOT NULL;

# ORDER BY
SELECT * FROM users ORDER BY created_at DESC;

# LIMIT / OFFSET
SELECT * FROM users LIMIT 10 OFFSET 20;

# Aggregate
SELECT COUNT(*), AVG(age), MAX(age), MIN(age) FROM users;

# GROUP BY
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

UPDATE

UPDATE users SET age = 26 WHERE username = 'alice';
UPDATE users SET age = age + 1, updated_at = NOW() WHERE age < 18;

DELETE

DELETE FROM users WHERE username = 'alice';
DELETE FROM users WHERE age < 13;
DELETE FROM users;  // all rows

Constraints

Constraint	Description	Example
PRIMARY KEY	Unique identifier	id SERIAL PRIMARY KEY
FOREIGN KEY	References another table	user_id INTEGER REFERENCES users(id)
UNIQUE	Unique values	email VARCHAR(255) UNIQUE
NOT NULL	Cannot be null	username VARCHAR(50) NOT NULL
CHECK	Condition	age INTEGER CHECK (age >= 0)
DEFAULT	Default value	is_active BOOLEAN DEFAULT TRUE

Joins

# INNER JOIN
SELECT u.username, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

# LEFT JOIN
SELECT u.username, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

# RIGHT JOIN
SELECT u.username, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

# FULL OUTER JOIN
SELECT u.username, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

# CROSS JOIN
SELECT u.username, p.product_name
FROM users u CROSS JOIN products p;

# Self Join
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Indexes

# B‑Tree (default)
CREATE INDEX idx_users_email ON users(email);

# Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

# Composite index
CREATE INDEX idx_users_age_name ON users(age, username);

# Partial index
CREATE INDEX idx_users_active ON users(id) WHERE is_active = true;

# Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

# Hash index
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

# GIN index (for JSONB, arrays)
CREATE INDEX idx_users_preferences ON users USING GIN(preferences);

# Drop index
DROP INDEX idx_users_email;

# List indexes
SELECT * FROM pg_indexes WHERE tablename = 'users';

Views

# Create view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE is_active = true;

# Materialized view
CREATE MATERIALIZED VIEW user_orders AS
SELECT u.id, u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

# Refresh materialized view
REFRESH MATERIALIZED VIEW user_orders;

# Drop view
DROP VIEW active_users;
DROP MATERIALIZED VIEW user_orders;

Functions

# PL/pgSQL function
CREATE OR REPLACE FUNCTION get_user_age(user_id INTEGER)
RETURNS INTEGER AS $$
DECLARE
    user_age INTEGER;
BEGIN
    SELECT age INTO user_age FROM users WHERE id = user_id;
    RETURN user_age;
END;
$$ LANGUAGE plpgsql;

# Use function
SELECT get_user_age(1);

# Function with RETURNING
CREATE OR REPLACE FUNCTION create_user(username TEXT, email TEXT)
RETURNS INTEGER AS $$
DECLARE
    new_id INTEGER;
BEGIN
    INSERT INTO users (username, email) VALUES (username, email)
    RETURNING id INTO new_id;
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

Triggers

# Trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# Create trigger
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

# Drop trigger
DROP TRIGGER update_users_updated_at ON users;

Window Functions

# ROW_NUMBER
SELECT username, age, ROW_NUMBER() OVER (ORDER BY age) AS rank
FROM users;

# RANK
SELECT username, age, RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

# PARTITION BY
SELECT username, age, city,
       RANK() OVER (PARTITION BY city ORDER BY age DESC) AS city_rank
FROM users;

# LAG / LEAD
SELECT username, order_date,
       LAG(order_date) OVER (ORDER BY order_date) AS previous_order
FROM orders;

JSON / JSONB

# Create JSON data
INSERT INTO users (username, preferences)
VALUES ('alice', '{"theme": "dark", "language": "en"}'::jsonb);

# Query JSON
SELECT username, preferences->>'theme' AS theme FROM users;
SELECT username, preferences->'language' AS language FROM users;

# JSON path
SELECT username, preferences @> '{"theme": "dark"}'::jsonb AS has_dark_theme
FROM users;

Full‑Text Search

# Create tsvector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');

# Search
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('quick & brown');

# Create GIN index for full‑text
CREATE INDEX idx_articles_content ON articles USING GIN(to_tsvector('english', content));

User Management

# Create user
CREATE USER alice WITH PASSWORD 'secure_password';

# Grant privileges
GRANT CONNECT ON DATABASE mydb TO alice;
GRANT SELECT, INSERT, UPDATE ON users TO alice;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO alice;

# Create role
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

# Drop user
DROP USER alice;

# Change password
ALTER USER alice WITH PASSWORD 'new_password';

Backup & Restore

# Backup (pg_dump)
pg_dump mydb > mydb.sql
pg_dump -h localhost -U postgres mydb > mydb.sql
pg_dump -Fc mydb > mydb.dump  // custom format

# Backup specific tables
pg_dump -t users -t orders mydb > tables.sql

# Restore
psql mydb < mydb.sql
pg_restore -d mydb mydb.dump

# Backup all databases
pg_dumpall > all.sql

Performance Tuning

EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN (BUFFERS, FORMAT JSON) SELECT * FROM users;

Common Settings (postgresql.conf)

• shared_buffers = 25% of RAM
• work_mem = 4-16 MB
• maintenance_work_mem = 64-256 MB
• effective_cache_size = 50-75% of RAM
• max_connections = 100-200
• checkpoint_timeout = 5-15 minutes

VACUUM

VACUUM;                    # reclaim space
VACUUM ANALYZE;             # reclaim and update stats
VACUUM FULL;                # compact (locks table)
VACUUM VERBOSE users;       # detailed output

Common Aggregate Functions

COUNT(*)           // number of rows
SUM(column)        // sum
AVG(column)        // average
MAX(column)        // maximum
MIN(column)        // minimum
STDDEV(column)     // standard deviation
VARIANCE(column)   // variance
STRING_AGG(column, ',') // concatenate
ARRAY_AGG(column)  // array

Best Practices

• Use appropriate data types – smallest sufficient.
• Add indexes – on columns used in WHERE, JOIN, ORDER BY.
• Use connection pooling – PgBouncer for production.
• Regular backups – test restore periodically.
• Use transactions – for data consistency.
• Monitor performance – pg_stat_statements, pg_stat_activity.
• Use prepared statements – for security and performance.
• Vacuum regularly – prevent bloat.
• Use EXPLAIN ANALYZE – to identify slow queries.
• Use JSONB – over JSON for indexing.
• Partition large tables – for performance.
• Use role‑based access – least privilege.
• Use schema – to organise objects.
• Keep indexes minimal – too many slow down writes.