PostgreSQL Cheatsheet

-- List all databases
\l
-- or
\list

-- Connect to database
\c mydb
-- or
\connect mydb

-- List all tables in current database
\dt

-- List all tables including system tables
\dt+

-- Describe table structure
\d table_name
-- or
\dt+ table_name

-- List all schemas
\dn

-- List all users/roles
\du

-- Show current database
SELECT current_database();

-- Show current user
SELECT current_user;

-- Show current schema
SHOW search_path;

-- Set schema search path
SET search_path TO schema_name, public;

-- List all functions
\df

-- List all views
\dv

-- Show table size
\d+ table_name

-- Show database size
\l+

-- Exit psql
\q
-- or
exit

-- Show help
\?
-- Help for SQL commands
\h SELECT

-- Create database
CREATE DATABASE mydb;

-- Create database with owner
CREATE DATABASE mydb OWNER username;

-- Create database with encoding
CREATE DATABASE mydb
  WITH ENCODING 'UTF8'
  LC_COLLATE='en_US.UTF-8'
  LC_CTYPE='en_US.UTF-8'
  TEMPLATE template0;

-- Create database if not exists (PostgreSQL 9.1+)
-- Note: No IF NOT EXISTS for CREATE DATABASE, use:
SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

-- Drop database
DROP DATABASE mydb;

-- Drop database if exists
DROP DATABASE IF EXISTS mydb;

-- Rename database
ALTER DATABASE oldname RENAME TO newname;

-- List all databases
SELECT datname FROM pg_database;

-- Show database size
SELECT pg_size_pretty(pg_database_size('mydb'));
# → 15 MB

-- Show all database sizes
SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Show current database
SELECT current_database();

-- Show database encoding
SHOW server_encoding;
# → UTF8

-- Show timezone
SHOW timezone;
# → UTC

-- Set timezone
SET timezone TO 'America/New_York';

-- Show all settings
SHOW ALL;

-- Show specific setting
SHOW max_connections;
# → 100

-- Set configuration parameter (session level)
SET work_mem = '256MB';

-- Set configuration parameter (database level)
ALTER DATABASE mydb SET work_mem = '256MB';

-- Basic table creation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with multiple constraints
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  product_name VARCHAR(100) NOT NULL,
  quantity INTEGER CHECK (quantity > 0),
  price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create table with schema
CREATE TABLE public.products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Create table if not exists (PostgreSQL 9.1+)
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

-- Create table from query result
CREATE TABLE users_backup AS
SELECT * FROM users WHERE created_at > '2024-01-01';

-- Create temporary table
CREATE TEMP TABLE temp_data (
  id INTEGER,
  value TEXT
);

-- Create unlogged table (faster writes, no WAL, lost on crash)
CREATE UNLOGGED TABLE cache_data (
  key VARCHAR(100) PRIMARY KEY,
  value TEXT
);

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

-- Add column with default value
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Add column with NOT NULL constraint (requires default)
ALTER TABLE users ADD COLUMN age INTEGER NOT NULL DEFAULT 0;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Drop column if exists
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- Rename column
ALTER TABLE users RENAME COLUMN email TO email_address;

-- Change column type
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- Change column type with conversion
ALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;

-- Set column default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';

-- Drop column default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT users_email_key;

-- Add foreign key constraint
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id);

-- Add check constraint
ALTER TABLE orders
ADD CONSTRAINT orders_quantity_check
CHECK (quantity > 0);

-- Drop table
DROP TABLE users;

-- Drop table if exists
DROP TABLE IF EXISTS users;

-- Drop table with cascade (drops dependent objects)
DROP TABLE users CASCADE;

-- Truncate table (delete all rows, faster than DELETE)
TRUNCATE TABLE users;

-- Truncate table and reset sequences
TRUNCATE TABLE users RESTART IDENTITY;

-- Truncate multiple tables
TRUNCATE TABLE users, orders, products;

-- List all tables
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- Show table structure
\d users

-- Show detailed table information
\d+ users

-- Show table size
SELECT pg_size_pretty(pg_total_relation_size('users'));
# → 1024 kB

-- Show table size breakdown
SELECT
  pg_size_pretty(pg_relation_size('users')) AS table_size,
  pg_size_pretty(pg_indexes_size('users')) AS indexes_size,
  pg_size_pretty(pg_total_relation_size('users')) AS total_size;

-- Count rows in table
SELECT COUNT(*) FROM users;

-- Show table statistics
SELECT
  schemaname,
  tablename,
  n_live_tup AS row_count,
  n_dead_tup AS dead_rows
FROM pg_stat_user_tables
WHERE tablename = 'users';

-- Integer types
SMALLINT    -- -32,768 to 32,767 (2 bytes)
INTEGER     -- -2,147,483,648 to 2,147,483,647 (4 bytes)
BIGINT      -- -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (8 bytes)
SERIAL      -- Auto-incrementing INTEGER (1 to 2,147,483,647)
BIGSERIAL   -- Auto-incrementing BIGINT

-- Example
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price INTEGER NOT NULL,
  quantity SMALLINT DEFAULT 0,
  views BIGINT DEFAULT 0
);

-- Decimal/Numeric types (exact precision)
DECIMAL(10, 2)  -- 10 digits total, 2 after decimal
NUMERIC(10, 2)  -- Same as DECIMAL
MONEY           -- Currency type (deprecated, use NUMERIC)

-- Example
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total DECIMAL(10, 2) NOT NULL,
  tax NUMERIC(5, 2) DEFAULT 0.00
);

-- Floating point types (approximate)
REAL      -- 6 decimal digits precision (4 bytes)
DOUBLE PRECISION  -- 15 decimal digits precision (8 bytes)

-- Example
CREATE TABLE measurements (
  id SERIAL PRIMARY KEY,
  temperature REAL,
  precision_value DOUBLE PRECISION
);

-- Character types
VARCHAR(n)     -- Variable length, max n characters
CHAR(n)        -- Fixed length, padded with spaces
TEXT           -- Unlimited length

-- Example
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  code CHAR(5),           -- Always 5 characters
  bio TEXT                 -- Unlimited length
);

-- String functions
SELECT LENGTH('hello');                    -- → 5
SELECT UPPER('hello');                     -- → HELLO
SELECT LOWER('HELLO');                     -- → hello
SELECT SUBSTRING('hello' FROM 1 FOR 3);    -- → hel
SELECT REPLACE('hello', 'l', 'L');        -- → heLLo
SELECT TRIM('  hello  ');                  -- → hello
SELECT CONCAT('hello', ' ', 'world');      -- → hello world
SELECT 'hello' || ' ' || 'world';          -- → hello world

-- Date and time types
DATE              -- Date only (YYYY-MM-DD)
TIME              -- Time only (HH:MM:SS)
TIMESTAMP         -- Date and time (YYYY-MM-DD HH:MM:SS)
TIMESTAMPTZ       -- Timestamp with timezone
INTERVAL          -- Time interval

-- Example
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_date DATE NOT NULL,
  start_time TIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  duration INTERVAL
);

-- Date/time functions
SELECT CURRENT_DATE;                    -- → 2024-01-15
SELECT CURRENT_TIME;                    -- → 14:30:00
SELECT CURRENT_TIMESTAMP;               -- → 2024-01-15 14:30:00
SELECT NOW();                           -- → Current timestamp
SELECT EXTRACT(YEAR FROM NOW());        -- → 2024
SELECT EXTRACT(MONTH FROM NOW());       -- → 1
SELECT EXTRACT(DAY FROM NOW());         -- → 15
SELECT DATE_TRUNC('day', NOW());        -- → 2024-01-15 00:00:00
SELECT DATE_TRUNC('month', NOW());      -- → 2024-01-01 00:00:00
SELECT AGE('2024-01-15', '2020-01-01'); -- → 4 years 14 days

-- Boolean type
BOOLEAN  -- true, false, or NULL

-- Example
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  completed BOOLEAN DEFAULT FALSE
);

-- Boolean operators
SELECT TRUE AND FALSE;    -- → false
SELECT TRUE OR FALSE;     -- → true
SELECT NOT TRUE;          -- → false

-- JSON types (PostgreSQL 9.2+)
JSON       -- Textual JSON data
JSONB      -- Binary JSON data (faster, indexable)

-- Example
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  metadata JSONB
);

-- JSON operations
INSERT INTO products (name, metadata)
VALUES ('Laptop', '{"brand": "Dell", "ram": 16}');

SELECT metadata->>'brand' FROM products;           -- → Dell
SELECT metadata->'ram' FROM products;               -- → 16
SELECT metadata @> '{"brand": "Dell"}'::jsonb;    -- → true

-- Array types
INTEGER[]        -- Array of integers
TEXT[]           -- Array of text
VARCHAR(50)[]    -- Array of varchar

-- Example
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  tags TEXT[]
);

INSERT INTO users (name, tags)
VALUES ('John', ARRAY['admin', 'developer', 'user']);

SELECT tags[1] FROM users;              -- → admin
SELECT array_length(tags, 1) FROM users; -- → 3

-- Basic insert
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com'),
  ('charlie', 'charlie@example.com');

-- Insert with default values
INSERT INTO users (username) VALUES ('dave');
-- email will use default, created_at will use CURRENT_TIMESTAMP

-- Insert from SELECT query
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE created_at > '2024-01-01';

-- Insert with RETURNING clause (get inserted row)
INSERT INTO users (username, email)
VALUES ('eve', 'eve@example.com')
RETURNING id, username, created_at;

-- Insert with ON CONFLICT (PostgreSQL 9.5+)
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com')
ON CONFLICT (username) DO NOTHING;

-- Insert with ON CONFLICT UPDATE
INSERT INTO users (username, email)
VALUES ('john_doe', 'newemail@example.com')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email, updated_at = CURRENT_TIMESTAMP;

-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT id, username, email FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username = 'john_doe';
SELECT * FROM users WHERE created_at > '2024-01-01';

-- Select with multiple conditions
SELECT * FROM users
WHERE username = 'john_doe' AND email LIKE '%@example.com';

-- Select with OR condition
SELECT * FROM users
WHERE username = 'john_doe' OR username = 'alice';

-- Select with IN clause
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE username IN ('john', 'alice', 'bob');

-- Select with NOT IN
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- Select with BETWEEN
SELECT * FROM users WHERE id BETWEEN 1 AND 100;
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Select with LIKE (pattern matching)
SELECT * FROM users WHERE username LIKE 'john%';      -- Starts with 'john'
SELECT * FROM users WHERE username LIKE '%doe';        -- Ends with 'doe'
SELECT * FROM users WHERE username LIKE '%john%';     -- Contains 'john'
SELECT * FROM users WHERE username ILIKE 'JOHN%';     -- Case-insensitive

-- Select with IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- Select with DISTINCT
SELECT DISTINCT username FROM users;
SELECT DISTINCT ON (username) * FROM users ORDER BY username, created_at DESC;

-- Select with ORDER BY
SELECT * FROM users ORDER BY username ASC;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, email DESC;

-- Select with LIMIT and OFFSET
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Skip first 20, get next 10

-- Select with aggregate functions
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT username) FROM users;
SELECT AVG(age) FROM users;
SELECT SUM(price) FROM orders;
SELECT MIN(created_at) FROM users;
SELECT MAX(created_at) FROM users;

-- Select with GROUP BY
SELECT status, COUNT(*)
FROM orders
GROUP BY status;

SELECT user_id, COUNT(*), SUM(price)
FROM orders
GROUP BY user_id;

-- Select with HAVING (filter groups)
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Select with CASE expression
SELECT
  username,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET email = 'newemail@example.com', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Update with subquery
UPDATE orders
SET status = 'shipped'
WHERE user_id IN (SELECT id FROM users WHERE username = 'john_doe');

-- Update with JOIN (PostgreSQL 9.1+)
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id AND u.username = 'john_doe';

-- Update with RETURNING clause
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1
RETURNING id, username, email;

-- Update all rows (use with caution!)
UPDATE users SET updated_at = CURRENT_TIMESTAMP;
-- ⚠️ Always use WHERE clause unless you intend to update all rows

-- Basic delete
DELETE FROM users WHERE id = 1;

-- Delete with multiple conditions
DELETE FROM users
WHERE username = 'john_doe' AND email IS NULL;

-- Delete with subquery
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE created_at < '2020-01-01');

-- Delete with JOIN (PostgreSQL 9.1+)
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id AND u.username = 'john_doe';

-- Delete all rows (use with caution!)
DELETE FROM users;
-- ⚠️ Prefer TRUNCATE for deleting all rows (faster)

-- Delete with RETURNING clause
DELETE FROM users
WHERE id = 1
RETURNING id, username, email;

-- Basic index
CREATE INDEX idx_users_username ON users(username);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(username)
WHERE status = 'active';

-- Expression index (index on expression result)
CREATE INDEX idx_users_lower_username ON users(LOWER(username));

-- Index with specific method
CREATE INDEX idx_users_email_hash ON users USING HASH(email);
CREATE INDEX idx_users_location_gist ON locations USING GIST(geom);

-- Concurrent index creation (doesn't block writes)
CREATE INDEX CONCURRENTLY idx_users_username ON users(username);

-- Full-text search index (GIN)
CREATE INDEX idx_products_description_gin
ON products USING GIN(to_tsvector('english', description));

-- List all indexes
SELECT indexname, tablename FROM pg_indexes WHERE schemaname = 'public';

-- Show indexes for a table
\d users

-- Show index size
SELECT pg_size_pretty(pg_relation_size('idx_users_username'));
# → 64 kB

-- Show index usage statistics
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS index_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- Check if index is being used
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';

-- Drop index
DROP INDEX idx_users_username;

-- Drop index if exists
DROP INDEX IF EXISTS idx_users_username;

-- Drop index concurrently (doesn't block reads)
DROP INDEX CONCURRENTLY idx_users_username;

-- Reindex (rebuild index)
REINDEX INDEX idx_users_username;

-- Reindex table (rebuild all indexes)
REINDEX TABLE users;

-- Reindex database
REINDEX DATABASE mydb;

-- Add primary key on creation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

-- Add primary key constraint
ALTER TABLE users ADD PRIMARY KEY (id);

-- Composite primary key
CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);

-- Drop primary key
ALTER TABLE users DROP CONSTRAINT users_pkey;

-- Add foreign key on creation
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10, 2)
);

-- Add foreign key with options
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10, 2)
);

-- Add foreign key constraint
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id);

-- Foreign key with ON DELETE CASCADE
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Foreign key with ON DELETE SET NULL
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

-- Drop foreign key
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;

-- Add unique constraint on creation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

-- Add unique constraint
ALTER TABLE users ADD CONSTRAINT users_username_unique UNIQUE (username);

-- Composite unique constraint
CREATE TABLE user_roles (
  user_id INTEGER,
  role_id INTEGER,
  UNIQUE (user_id, role_id)
);

-- Drop unique constraint
ALTER TABLE users DROP CONSTRAINT users_username_unique;

-- Add check constraint on creation
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2) CHECK (price >= 0),
  quantity INTEGER CHECK (quantity >= 0)
);

-- Add check constraint
ALTER TABLE products
ADD CONSTRAINT products_price_check CHECK (price >= 0);

-- Drop check constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- Add NOT NULL on creation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN username DROP NOT NULL;

-- Basic inner join
SELECT u.username, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Inner join with WHERE
SELECT u.username, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

-- Multiple inner joins
SELECT u.username, o.total, p.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id;

-- Left join (all rows from left table)
SELECT u.username, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Left join with WHERE (filters NULLs)
SELECT u.username, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.total IS NOT NULL;

-- Left join to find users without orders
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Right join (all rows from right table)
SELECT u.username, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Right join to find orders without users
SELECT o.id, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL;

-- Full outer join (all rows from both tables)
SELECT u.username, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Full outer join to find mismatches
SELECT u.username, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.id IS NULL;

-- Cross join (Cartesian product)
SELECT u.username, p.name
FROM users u
CROSS JOIN products p;

-- Equivalent to:
SELECT u.username, p.name
FROM users u, products p;

-- Self join (join table to itself)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Subquery in WHERE clause
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Subquery in SELECT clause
SELECT
  username,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- Subquery with EXISTS
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- Correlated subquery
SELECT
  username,
  (SELECT MAX(total) FROM orders WHERE orders.user_id = users.id) AS max_order
FROM users;

-- Subquery in FROM clause (derived table)
SELECT u.username, order_stats.total
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total
  FROM orders
  GROUP BY user_id
) order_stats ON u.id = order_stats.user_id;

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

-- Multiple CTEs
WITH
  active_users AS (
    SELECT * FROM users WHERE status = 'active'
  ),
  user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
  )
SELECT u.username, uo.order_count
FROM active_users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- Recursive CTE (for hierarchical data)
WITH RECURSIVE employee_hierarchy AS (
  -- Base case
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

-- String functions
SELECT LENGTH('hello');                    -- → 5
SELECT UPPER('hello');                     -- → HELLO
SELECT LOWER('HELLO');                     -- → hello
SELECT SUBSTRING('hello' FROM 1 FOR 3);    -- → hel
SELECT REPLACE('hello', 'l', 'L');        -- → heLLo
SELECT TRIM('  hello  ');                  -- → hello
SELECT CONCAT('hello', ' ', 'world');      -- → hello world
SELECT POSITION('world' IN 'hello world'); -- → 7

-- Numeric functions
SELECT ABS(-10);                           -- → 10
SELECT ROUND(3.14159, 2);                  -- → 3.14
SELECT CEIL(3.1);                          -- → 4
SELECT FLOOR(3.9);                         -- → 3
SELECT MOD(10, 3);                         -- → 1
SELECT POWER(2, 3);                        -- → 8
SELECT SQRT(16);                           -- → 4

-- Date functions
SELECT CURRENT_DATE;                      -- → 2024-01-15
SELECT CURRENT_TIME;                      -- → 14:30:00
SELECT CURRENT_TIMESTAMP;                  -- → 2024-01-15 14:30:00
SELECT NOW();                             -- → Current timestamp
SELECT EXTRACT(YEAR FROM NOW());          -- → 2024
SELECT DATE_TRUNC('day', NOW());          -- → 2024-01-15 00:00:00
SELECT AGE('2024-01-15', '2020-01-01');   -- → 4 years 14 days

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT AVG(price) FROM products;
SELECT SUM(total) FROM orders;
SELECT MIN(created_at) FROM users;
SELECT MAX(created_at) FROM users;
SELECT STRING_AGG(username, ', ') FROM users;

-- Create function (returns scalar)
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- Call function
SELECT get_user_count();

-- Function with parameters
CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR) AS $$
BEGIN
  RETURN QUERY
  SELECT users.id, users.username, users.email
  FROM users
  WHERE users.id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Call function
SELECT * FROM get_user_by_id(1);

-- Function with default parameters
CREATE OR REPLACE FUNCTION greet_user(name VARCHAR DEFAULT 'Guest')
RETURNS TEXT AS $$
BEGIN
  RETURN 'Hello, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

-- Function with OUT parameters
CREATE OR REPLACE FUNCTION divide_numbers(
  a NUMERIC,
  b NUMERIC,
  OUT result NUMERIC,
  OUT remainder NUMERIC
) AS $$
BEGIN
  result := a / b;
  remainder := MOD(a, b);
END;
$$ LANGUAGE plpgsql;

-- Call function
SELECT * FROM divide_numbers(10, 3);

-- Create stored procedure (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE update_user_email(
  user_id INTEGER,
  new_email VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE users
  SET email = new_email, updated_at = CURRENT_TIMESTAMP
  WHERE id = user_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'User with id % not found', user_id;
  END IF;
END;
$$;

-- Call procedure
CALL update_user_email(1, 'newemail@example.com');

-- Procedure with transaction control
CREATE OR REPLACE PROCEDURE transfer_funds(
  from_account INTEGER,
  to_account INTEGER,
  amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- Debit from source account
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;

  -- Credit to destination account
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- If any update fails, transaction will rollback
END;
$$;

-- Create trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  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();

-- Trigger for audit logging
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_audit_log (user_id, action, changed_at)
  VALUES (NEW.id, TG_OP, CURRENT_TIMESTAMP);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

-- Drop trigger
DROP TRIGGER IF EXISTS update_users_updated_at ON users;

-- Start transaction
BEGIN;

-- Or
START TRANSACTION;

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

-- Example transaction
BEGIN;
  INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
  INSERT INTO orders (user_id, total) VALUES (1, 100.00);
COMMIT;

-- Transaction with rollback on error
BEGIN;
  INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
  -- If error occurs, rollback
  ROLLBACK;

-- Set transaction isolation level
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Available isolation levels:
-- READ UNCOMMITTED (not available in PostgreSQL, treated as READ COMMITTED)
-- READ COMMITTED (default)
-- REPEATABLE READ
-- SERIALIZABLE

-- Example
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM users WHERE id = 1;
  -- Other operations
COMMIT;

-- Show current isolation level
SHOW transaction_isolation;
# → read committed

-- Create savepoint
BEGIN;
  INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
  SAVEPOINT sp1;

  INSERT INTO orders (user_id, total) VALUES (1, 100.00);
  -- If error, rollback to savepoint
  ROLLBACK TO SAVEPOINT sp1;

  -- Continue transaction
  INSERT INTO orders (user_id, total) VALUES (1, 200.00);
COMMIT;

-- Release savepoint
RELEASE SAVEPOINT sp1;

-- Row-level locking
BEGIN;
  SELECT * FROM users WHERE id = 1 FOR UPDATE;
  -- Row is locked until transaction commits
  UPDATE users SET email = 'new@example.com' WHERE id = 1;
COMMIT;

-- Share lock (allows reads, prevents writes)
BEGIN;
  SELECT * FROM users WHERE id = 1 FOR SHARE;
COMMIT;

-- Table-level locking
LOCK TABLE users IN EXCLUSIVE MODE;
-- Operations...
UNLOCK TABLE users;

-- Advisory locks (application-level)
SELECT pg_advisory_lock(12345);
-- Critical section...
SELECT pg_advisory_unlock(12345);

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

-- EXPLAIN with ANALYZE (executes query)
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';

-- EXPLAIN with verbose output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE username = 'john_doe';

-- EXPLAIN with format options
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE username = 'john_doe';
EXPLAIN (FORMAT XML) SELECT * FROM users WHERE username = 'john_doe';

-- ✅ Use indexes on frequently queried columns
CREATE INDEX idx_users_username ON users(username);

-- ✅ Use EXPLAIN ANALYZE to identify slow queries
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';

-- ✅ Use LIMIT when you don't need all rows
SELECT * FROM users LIMIT 10;  -- ✅ Good
SELECT * FROM users;            -- ❌ Bad if you only need 10 rows

-- ✅ Use specific columns instead of SELECT *
SELECT id, username FROM users;  -- ✅ Good
SELECT * FROM users;              -- ❌ Bad if you don't need all columns

-- ✅ Use WHERE to filter early
SELECT * FROM orders WHERE user_id = 1 AND total > 100;  -- ✅ Good

-- ✅ Use JOINs instead of subqueries when possible
-- ✅ Good: JOIN
SELECT u.username, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- ❌ Bad: Subquery (usually slower)
SELECT username, (SELECT total FROM orders WHERE user_id = users.id)
FROM users;

-- ✅ Use EXISTS instead of COUNT(*) for existence checks
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);  -- ✅ Good

SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 0;  -- ❌ Bad

-- VACUUM (reclaims storage, updates statistics)
VACUUM;

-- VACUUM specific table
VACUUM users;

-- VACUUM FULL (rewrites table, requires exclusive lock)
VACUUM FULL users;

-- VACUUM ANALYZE (vacuum and update statistics)
VACUUM ANALYZE users;

-- ANALYZE (update statistics for query planner)
ANALYZE users;

-- ANALYZE all tables
ANALYZE;

-- Show table statistics
SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename = 'users';

-- Show current connections
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  state_change
FROM pg_stat_activity
WHERE datname = 'mydb';

-- Show connection count
SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'mydb';

-- Kill specific connection
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

-- Show max connections setting
SHOW max_connections;
# → 100