PostgreSQL Cheat Sheet
Complete reference guide for PostgreSQL with interactive examples and live playground links
Basic Operations
Database Operations
Basic database operations
PostgreSQL
-- Create database
CREATE DATABASE mydb;
-- Connect to database
c mydb
-- List databases
l
-- Drop database
DROP DATABASE mydb;
-- List tables
dt
-- Describe table
d users
-- List schemas
dn
-- List functions
df
Table Operations
Table creation and modification
PostgreSQL
-- Create table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Alter table
ALTER TABLE users
ADD COLUMN phone VARCHAR(20),
ALTER COLUMN name TYPE VARCHAR(150),
DROP COLUMN age;
-- Drop table
DROP TABLE users;
-- Truncate table
TRUNCATE TABLE users;
-- Rename table
ALTER TABLE old_name RENAME TO new_name;
Data Types
Common PostgreSQL data types
PostgreSQL
-- Numeric Types
INTEGER, BIGINT, SMALLINT
NUMERIC(10,2), DECIMAL(10,2)
REAL, DOUBLE PRECISION
SERIAL, BIGSERIAL
-- String Types
CHAR(10), VARCHAR(255), TEXT
CITEXT (case-insensitive)
-- Date and Time
DATE, TIME, TIMESTAMP
TIMESTAMPTZ (with timezone)
INTERVAL
-- Binary Types
BYTEA
-- Other Types
BOOLEAN, ENUM
JSON, JSONB
ARRAY
UUID
GEOMETRY, GEOGRAPHY
Queries
SELECT Queries
SELECT query patterns
PostgreSQL
-- Basic SELECT
SELECT * FROM users;
-- Specific columns
SELECT name, email FROM users;
-- With conditions
SELECT * FROM users WHERE age > 18;
-- Sorting
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC, name ASC;
-- Limiting results
SELECT * FROM users LIMIT 10;
SELECT * FROM users OFFSET 20 LIMIT 10;
-- Distinct values
SELECT DISTINCT ON (country) country, name
FROM users
ORDER BY country, created_at DESC;
-- Aliases
SELECT u.name AS user_name, p.title AS post_title
FROM users u
JOIN posts p ON u.id = p.user_id;
JOIN Operations
JOIN operations and patterns
PostgreSQL
-- Inner Join
SELECT users.name, orders.order_number
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- Left Join
SELECT users.name, orders.order_number
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Right Join
SELECT users.name, orders.order_number
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
-- Full Outer Join
SELECT users.name, orders.order_number
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
-- Cross Join
SELECT users.name, products.name
FROM users
CROSS JOIN products;
-- Lateral Join
SELECT u.name, p.title
FROM users u
CROSS JOIN LATERAL (
SELECT title FROM posts
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 1
) p;
Aggregate Functions
Aggregate functions and window functions
PostgreSQL
-- Basic aggregates
SELECT
COUNT(*) as total_users,
AVG(age) as average_age,
MAX(age) as max_age,
MIN(age) as min_age,
SUM(points) as total_points
FROM users;
-- Group By
SELECT
country,
COUNT(*) as user_count,
AVG(age) as avg_age
FROM users
GROUP BY country
HAVING COUNT(*) > 10;
-- Window functions
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank,
LAG(salary) OVER (ORDER BY salary) as prev_salary,
LEAD(salary) OVER (ORDER BY salary) as next_salary
FROM employees;
Data Modifications
INSERT Operations
INSERT operations and patterns
PostgreSQL
-- Single row insert
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);
-- Multiple rows
INSERT INTO users (name, email, age)
VALUES
('Jane Doe', 'jane@example.com', 25),
('Bob Smith', 'bob@example.com', 35);
-- Insert from select
INSERT INTO new_users (name, email)
SELECT name, email FROM old_users
WHERE status = 'active';
-- Insert with returning
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com')
RETURNING id, name;
-- Upsert (insert or update)
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;
UPDATE Operations
UPDATE operations and patterns
PostgreSQL
-- Basic update
UPDATE users
SET name = 'John Smith',
email = 'john.smith@example.com'
WHERE id = 1;
-- Update with join
UPDATE users u
SET total_orders = u.total_orders + 1
FROM orders o
WHERE u.id = o.user_id
AND o.status = 'completed';
-- Update with returning
UPDATE users
SET status = 'inactive'
WHERE last_login < NOW() - INTERVAL '1 year'
RETURNING id, name;
-- Update with case
UPDATE products
SET price = CASE
WHEN category = 'premium' THEN price * 1.1
WHEN category = 'standard' THEN price * 1.05
ELSE price
END;
DELETE Operations
DELETE operations and patterns
PostgreSQL
-- Basic delete
DELETE FROM users WHERE id = 1;
-- Delete with join
DELETE FROM users u
USING inactive_users i
WHERE u.id = i.user_id
AND i.last_login < NOW() - INTERVAL '1 year';
-- Delete with returning
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '1 month'
RETURNING id;
-- Delete with order and limit
DELETE FROM users
WHERE status = 'inactive'
ORDER BY last_login ASC
LIMIT 100;
Advanced Features
Indexes
Index creation and management
PostgreSQL
-- Create index
CREATE INDEX idx_email ON users(email);
-- Create unique index
CREATE UNIQUE INDEX idx_username ON users(username);
-- Create composite index
CREATE INDEX idx_name_age ON users(name, age);
-- Create partial index
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- Create expression index
CREATE INDEX idx_name_lower ON users(LOWER(name));
-- Create GIN index
CREATE INDEX idx_tags ON posts USING GIN (tags);
-- Drop index
DROP INDEX idx_email;
-- Show indexes
di
Transactions
Transaction management
PostgreSQL
-- Basic transaction
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- Transaction with rollback
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
UPDATE users SET balance = balance - 100 WHERE id = 1;
IF (SELECT balance FROM users WHERE id = 1) < 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
-- Savepoints
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT order_created;
UPDATE users SET balance = balance - 100 WHERE id = 1;
IF (SELECT balance FROM users WHERE id = 1) < 0 THEN
ROLLBACK TO SAVEPOINT order_created;
END IF;
COMMIT;
Functions and Procedures
Functions and stored procedures
PostgreSQL
-- Create function
CREATE OR REPLACE FUNCTION get_user_orders(user_id INTEGER)
RETURNS TABLE (
order_id INTEGER,
order_number VARCHAR,
total DECIMAL
) AS $$
BEGIN
RETURN QUERY
SELECT o.id, o.order_number, o.total
FROM orders o
WHERE o.user_id = get_user_orders.user_id;
END;
$$ LANGUAGE plpgsql;
-- Create procedure
CREATE OR REPLACE PROCEDURE update_user_balance(
p_user_id INTEGER,
p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE users
SET balance = balance + p_amount
WHERE id = p_user_id;
INSERT INTO transactions (user_id, amount)
VALUES (p_user_id, p_amount);
COMMIT;
END;
$$;
Optimization
Query Optimization
Query optimization techniques
PostgreSQL
-- Explain query
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';
-- Force index
SELECT * FROM users
WHERE email = 'john@example.com'
USING INDEX idx_email;
-- Vacuum analyze
VACUUM ANALYZE users;
-- Show query plan
EXPLAIN (FORMAT JSON) SELECT * FROM users;
-- Show running queries
SELECT * FROM pg_stat_activity;
-- Cancel query
SELECT pg_cancel_backend(pid);
Performance Tuning
Performance tuning and monitoring
PostgreSQL
-- Show configuration
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
-- Set configuration
ALTER SYSTEM SET shared_buffers = '1GB';
ALTER SYSTEM SET work_mem = '64MB';
-- Show statistics
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;
-- Show table size
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Show index usage
SELECT * FROM pg_stat_user_indexes;
Backup and Recovery
Backup and recovery procedures
PostgreSQL
-- Create backup
pg_dump -U username database_name > backup.sql
-- Restore from backup
psql -U username database_name < backup.sql
-- Backup specific tables
pg_dump -U username -t table1 -t table2 database_name > backup.sql
-- Backup with compression
pg_dump -U username database_name | gzip > backup.sql.gz
-- Restore compressed backup
gunzip -c backup.sql.gz | psql -U username database_name
-- Backup with custom format
pg_dump -U username -Fc database_name > backup.dump
-- Restore custom format
pg_restore -U username -d database_name backup.dump
PostgreSQL - Interactive Developer Reference
Hover over code blocks to copy or run in live playground