PostgreSQL Cheatsheet

-- Connect to PostgreSQL
psql -U username -d database

-- List all databases
\l or \list

-- Create database
CREATE DATABASE dbname;

-- Switch database
\c dbname

-- Drop database
DROP DATABASE dbname;

-- Show current database
SELECT current_database();

-- Show all tables
\dt

-- Describe table
\d tablename

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    age INTEGER CHECK (age >= 18),
    is_active BOOLEAN DEFAULT true
);

-- Modify table
ALTER TABLE users ADD COLUMN phone VARCHAR(15);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);
ALTER TABLE users RENAME TO accounts;

-- Drop table
DROP TABLE users;

-- Truncate table
TRUNCATE TABLE users;
TRUNCATE TABLE users CASCADE;  -- With related tables

-- Insert single row
INSERT INTO users (username, email, age)
VALUES ('alex', 'alex@example.com', 25);

-- Insert multiple rows
INSERT INTO users (username, email, age) VALUES 
    ('emma', 'emma@example.com', 28),
    ('michael', 'michael@example.com', 32);

-- Insert with returning
INSERT INTO users (username, email)
VALUES ('sarah', 'sarah@example.com')
RETURNING id, username;

-- Insert from select
INSERT INTO active_users
SELECT * FROM users WHERE is_active = true;

-- Basic select
SELECT * FROM users;
SELECT username, email FROM users;

-- Conditional select
SELECT * FROM users 
WHERE age >= 25 
AND is_active = true;

-- Order results
SELECT * FROM users
ORDER BY username ASC, created_at DESC;

-- Limit and offset
SELECT * FROM users
LIMIT 10 OFFSET 20;

-- Distinct values
SELECT DISTINCT country FROM users;

-- Count results
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;

-- Group by
SELECT country, COUNT(*) 
FROM users 
GROUP BY country 
HAVING COUNT(*) > 5;

-- 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.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;

-- Subquery in WHERE
SELECT username 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM orders 
    WHERE amount > 1000
);

-- Subquery with EXISTS
SELECT username 
FROM users u
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = u.id
);

-- Subquery in FROM
SELECT avg_order, count(*)
FROM (
    SELECT user_id, AVG(amount) as avg_order
    FROM orders
    GROUP BY user_id
) as order_averages
GROUP BY avg_order;

-- Correlated subquery
SELECT username,
    (SELECT COUNT(*) 
     FROM orders o 
     WHERE o.user_id = u.id) as order_count
FROM users u;

-- Numeric types
INTEGER, BIGINT, SMALLINT
DECIMAL, NUMERIC
REAL, DOUBLE PRECISION
SERIAL, BIGSERIAL  -- Auto-incrementing

-- String types
CHAR(n), VARCHAR(n), TEXT
UUID  -- Universally unique identifiers

-- Date/Time types
DATE  -- 'YYYY-MM-DD'
TIME  -- 'HH:MI:SS'
TIMESTAMP  -- 'YYYY-MM-DD HH:MI:SS'
INTERVAL  -- Time periods

-- Boolean type
BOOLEAN  -- true/false

-- Arrays
INTEGER[]
VARCHAR[]
TEXT[]

-- JSON types
JSON, JSONB

-- Custom types
CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');

-- String functions
LENGTH(string)
LOWER(string)
UPPER(string)
TRIM(string)
SUBSTRING(string, start, length)
CONCAT(string1, string2)

-- Date functions
NOW()
CURRENT_DATE
CURRENT_TIME
DATE_TRUNC('month', timestamp)
AGE(timestamp, timestamp)
EXTRACT(YEAR FROM timestamp)

-- Aggregate functions
COUNT(*)
SUM(column)
AVG(column)
MAX(column)
MIN(column)

-- Window functions
ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)
RANK() OVER (ORDER BY column)
LAG(column) OVER (ORDER BY column)

-- Create index
CREATE INDEX idx_username ON users(username);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create compound index
CREATE INDEX idx_user_status ON users(status, created_at);

-- Create partial index
CREATE INDEX idx_active_users ON users(username)
WHERE is_active = true;

-- Create expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- List indexes
\d tablename

-- Drop index
DROP INDEX idx_username;

-- Analyze table
ANALYZE users;

-- Show index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes;

-- Explain query plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Explain and analyze
EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE created_at >= '2023-01-01';

-- Show table statistics
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables;

-- Show buffer cache hit ratio
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;