SQL Reference

-- Tables
CREATE TABLE [IF NOT EXISTS] name (col type [constraints], ...);
DROP TABLE [IF EXISTS] name;
ALTER TABLE name ADD COLUMN col type;
ALTER TABLE name DROP COLUMN col;
ALTER TABLE name RENAME COLUMN old TO new;
ALTER TABLE name RENAME TO new_name;

-- Indexes
CREATE [UNIQUE] INDEX name ON table(column);
CREATE INDEX name ON table(col) WHERE predicate;  -- partial index
DROP INDEX [IF EXISTS] name;

-- Views
CREATE VIEW name AS select_statement;
CREATE OR REPLACE VIEW name AS select_statement;
DROP VIEW [IF EXISTS] name;

-- Materialized Views
CREATE MATERIALIZED VIEW name AS select_statement;
REFRESH MATERIALIZED VIEW name;

-- Schemas
CREATE SCHEMA name;
SET search_path = name, public;

-- Partitioned tables
CREATE TABLE name (...) PARTITION BY RANGE (col);
CREATE TABLE name_p1 PARTITION OF name FOR VALUES FROM (x) TO (y);
CREATE TABLE name (...) PARTITION BY LIST (col);
CREATE TABLE name (...) PARTITION BY HASH (col);

-- INSERT
INSERT INTO table [(cols)] VALUES (vals), (vals), ...;
INSERT INTO table [(cols)] SELECT ...;
INSERT INTO table ... ON CONFLICT DO NOTHING;
INSERT INTO table ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col;

-- SELECT
SELECT [DISTINCT] exprs FROM table [alias]
    [JOIN table2 ON cond]
    [LEFT JOIN table3 ON cond]
    [RIGHT JOIN table4 ON cond]
    [FULL OUTER JOIN table5 ON cond]
    [CROSS JOIN table6]
    [WHERE cond]
    [GROUP BY cols] [HAVING cond]
    [ORDER BY cols [ASC|DESC] [NULLS FIRST|LAST]]
    [LIMIT n [OFFSET m]];

-- UPDATE
UPDATE table SET col=val, col2=val2 [WHERE cond] [RETURNING *];

-- DELETE
DELETE FROM table [WHERE cond] [RETURNING *];

-- MERGE (UPSERT)
MERGE INTO target USING source ON (condition)
    WHEN MATCHED THEN UPDATE SET ...
    WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

-- Subqueries
SELECT * FROM (SELECT col FROM t) AS subq;
SELECT * FROM t WHERE col IN (SELECT col FROM t2);
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id);

-- DISTINCT ON
SELECT DISTINCT ON (col) * FROM t ORDER BY col, ts DESC;

BEGIN [TRANSACTION];
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
COMMIT;
ROLLBACK;

-- Savepoints
SAVEPOINT my_point;
ROLLBACK TO SAVEPOINT my_point;
RELEASE SAVEPOINT my_point;

-- INNER JOIN (default)
SELECT a.*, b.* FROM a JOIN b ON a.id = b.a_id;

-- LEFT OUTER JOIN
SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id;

-- RIGHT OUTER JOIN
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.id = b.a_id;

-- FULL OUTER JOIN
SELECT a.*, b.* FROM a FULL OUTER JOIN b ON a.id = b.a_id;

-- CROSS JOIN (cartesian product)
SELECT a.*, b.* FROM a CROSS JOIN b;

-- Self-join example
SELECT e.name, m.name AS manager
FROM employees e JOIN employees m ON e.manager_id = m.id;

-- Ranking
ROW_NUMBER() OVER ([PARTITION BY expr] ORDER BY expr [ASC|DESC])
RANK()        OVER ([PARTITION BY expr] ORDER BY expr)
DENSE_RANK()  OVER ([PARTITION BY expr] ORDER BY expr)
NTILE(n)      OVER (ORDER BY expr)
PERCENT_RANK() OVER (ORDER BY expr)
CUME_DIST()   OVER (ORDER BY expr)

-- Lag / Lead
LAG(col, offset [, default])  OVER (ORDER BY expr)
LEAD(col, offset [, default]) OVER (ORDER BY expr)

-- First / Last / Nth value
FIRST_VALUE(col)    OVER (ORDER BY expr)
LAST_VALUE(col)     OVER (ORDER BY expr ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NTH_VALUE(col, n)   OVER (ORDER BY expr)

-- Example: running total
SELECT name, amount,
    SUM(amount) OVER (PARTITION BY dept ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales;

-- Simple CTE
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

-- Multiple CTEs
WITH
  totals AS (SELECT dept_id, SUM(salary) AS total FROM employees GROUP BY dept_id),
  depts  AS (SELECT id, name FROM departments)
SELECT d.name, t.total FROM depts d JOIN totals t ON d.id = t.dept_id;

-- Recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM employees WHERE parent_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.parent_id, o.depth + 1
    FROM employees e JOIN org_tree o ON e.parent_id = o.id
)
SELECT * FROM org_tree ORDER BY depth, name;

SELECT col FROM t1 UNION     SELECT col FROM t2;  -- Unique rows from both
SELECT col FROM t1 UNION ALL SELECT col FROM t2;  -- All rows (including duplicates)
SELECT col FROM t1 INTERSECT SELECT col FROM t2;  -- Rows in both
SELECT col FROM t1 EXCEPT    SELECT col FROM t2;  -- Rows in t1 not in t2

UPPER(str)                -- Convert to uppercase
LOWER(str)                -- Convert to lowercase
LENGTH(str)               -- String length in characters
TRIM(str)                 -- Remove leading/trailing spaces
LTRIM(str)                -- Remove leading spaces
RTRIM(str)                -- Remove trailing spaces
SUBSTRING(str, pos, len)  -- Extract substring
REPLACE(str, from, to)    -- Replace occurrences
LPAD(str, len, pad)       -- Left-pad to length
RPAD(str, len, pad)       -- Right-pad to length
REVERSE(str)              -- Reverse string
REPEAT(str, n)            -- Repeat string n times
str1 || str2              -- Concatenation operator
LIKE 'pattern%'           -- Pattern matching (% = any chars, _ = one char)
ILIKE 'pattern%'          -- Case-insensitive LIKE

ABS(n)        -- Absolute value
ROUND(n, d)   -- Round to d decimal places
CEIL(n)       -- Ceiling (round up)
FLOOR(n)      -- Floor (round down)
POWER(x, y)   -- x to the power of y
SQRT(n)       -- Square root
LOG(n)        -- Natural logarithm
LOG(b, n)     -- Logarithm base b
MOD(x, y)     -- Modulo (remainder)
PI()          -- 3.14159265358979...
RANDOM()      -- Random float [0, 1)

-- CASE expression
CASE WHEN condition THEN result [WHEN ... THEN ...] [ELSE result] END

-- CASE value
CASE value WHEN val1 THEN res1 [WHEN val2 THEN res2] [ELSE default] END

-- Null-handling
COALESCE(a, b, c)    -- First non-NULL argument
NULLIF(a, b)         -- NULL if a = b, otherwise a
IIF(cond, t, f)      -- Inline if: returns t if cond else f

-- Type conversion
CAST(expr AS type)
typeof(expr)         -- Returns type name as text

-- Construction
JSON_OBJECT(key1, val1, key2, val2, ...)
JSON_ARRAY(val1, val2, ...)

-- Extraction
JSON_EXTRACT(json_col, '$.path.to.field')
json_col ->> 'key'          -- Text extraction (PostgreSQL-compatible)
json_col -> 'key'           -- JSON extraction

-- JSONB operators
json_col @> '{"status":"active"}'::jsonb   -- Contains
json_col ? 'key'                           -- Key exists
json_col #>> '{a,b}'                       -- Path extraction

-- JSON_TABLE (SQL:2023)
SELECT jt.*
FROM my_table,
     JSON_TABLE(data, '$[*]' COLUMNS (
         id   INTEGER PATH '$.id',
         name TEXT    PATH '$.name'
     )) AS jt;

-- Validation
JSON_SCHEMA_VALID(schema, document)  -- Returns boolean

NOW()                          -- Current timestamp with timezone
CURRENT_TIMESTAMP              -- Same as NOW()
CURRENT_DATE                   -- Current date only
DATE_TRUNC('month', ts)        -- Truncate to month boundary
DATE_PART('year', ts)          -- Extract year component
EXTRACT(YEAR FROM ts)          -- SQL standard extraction
ts + INTERVAL '7 days'         -- Add interval to timestamp
ts - INTERVAL '1 hour'         -- Subtract interval
TIME_BUCKET('1 hour', ts)      -- Time-series binning function

-- Temporal snapshots (MVCC)
SELECT * FROM orders AS OF SCN 12345;    -- Read at historical SCN

CREATE TABLE products (
    id       INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sku      TEXT    NOT NULL UNIQUE,
    price    REAL    NOT NULL CHECK (price >= 0),
    category TEXT    DEFAULT 'general',
    parent_id INTEGER REFERENCES products(id) ON DELETE CASCADE
);

-- Named constraint
CONSTRAINT chk_price CHECK (price > 0 AND price < 1000000)

-- Multi-column unique
CONSTRAINT uq_sku_rev UNIQUE (sku, revision)

-- Conflict handling
INSERT INTO products (sku, price) VALUES ('ABC', 9.99)
    ON CONFLICT DO NOTHING;

INSERT INTO products (sku, price) VALUES ('ABC', 9.99)
    ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;

-- Users
CREATE USER name PASSWORD 'pass';
DROP USER name;
ALTER USER name PASSWORD 'newpass';

-- Roles
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO alice;
REVOKE readonly FROM alice;

-- Table permissions
GRANT SELECT, INSERT ON table TO user;
GRANT ALL ON table TO user;
REVOKE DELETE ON table FROM user;

-- Row-Level Security
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_iso ON customers
    USING (tenant_id = current_setting('app.tenant_id')::integer);

-- Column encryption (Enterprise)
ALTER TABLE customers
    ENCRYPT COLUMN email USING 'aes256-gcm' KEY 'email_dek';

-- Dynamic data masking
CREATE MASKING POLICY mask_email ON customers(email)
    USING (CASE WHEN current_role = 'admin' THEN email
                ELSE regexp_replace(email, '(.{2}).*@', '\1***@') END);

-- CSV Import
COPY table FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- CSV Export
COPY table TO '/path/to/output.csv' WITH (FORMAT csv, HEADER true);
COPY (SELECT * FROM orders WHERE total > 100) TO '/path/output.csv';

-- TSV variant
COPY table FROM '/path/to/file.tsv' WITH (FORMAT tsv);

-- Stdout (for piping)
COPY table TO STDOUT WITH (FORMAT csv);

-- Binary format (3× faster — PostgreSQL-compatible)
COPY table FROM STDIN WITH (FORMAT binary);
COPY table TO STDOUT WITH (FORMAT binary);

-- Subscribe to a channel
LISTEN orders_channel;

-- Unsubscribe
UNLISTEN orders_channel;
UNLISTEN *;  -- All channels

-- Send a notification (with optional payload, max 8,000 bytes)
NOTIFY orders_channel;
NOTIFY orders_channel, '{"order_id":42,"status":"shipped"}';

-- Compatible with Rails ActionCable, Django Channels, node-postgres

-- Session-scoped exclusive lock
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- Non-blocking try-lock (returns boolean)
SELECT pg_advisory_try_lock(12345);

-- Used automatically by Flyway, Liquibase, Alembic, golang-migrate
-- 65,536 concurrent locks supported

-- ROLLUP: subtotals and grand total
SELECT region, product, SUM(sales)
FROM orders
GROUP BY ROLLUP(region, product);

-- CUBE: all combinations
SELECT year, quarter, region, SUM(revenue)
FROM sales
GROUP BY CUBE(year, quarter, region);

-- GROUPING SETS: explicit list
SELECT a, b, SUM(c) FROM t
GROUP BY GROUPING SETS ((a, b), (a), (b), ());

-- GROUPING_ID() to identify which set a row belongs to
SELECT GROUPING_ID(a, b), a, b, SUM(c) FROM t
GROUP BY ROLLUP(a, b);

-- Create a continuous streaming view
CREATE STREAMING VIEW high_value_orders AS
  SELECT customer_id, SUM(total) AS running_total
  FROM orders
  WHERE total > 1000;

-- List active streaming views
SHOW STREAMING VIEWS;

-- Drop a streaming view
DROP STREAMING VIEW high_value_orders;

-- Events delivered via NOTIFY, WebSocket, or result table

-- Convert natural language to SQL in-database (offline, no API)
SELECT NL2SQL('show me the top 10 customers by revenue', 'customers(id,name,revenue)');

-- Returns the generated SQL string:
-- SELECT name, revenue FROM customers ORDER BY revenue DESC LIMIT 10

-- Schema context can include multiple tables
SELECT NL2SQL(
  'count orders placed last 30 days',
  'orders(id,customer_id,total,created_at INT)'
);

-- Match pattern (Neo4j-compatible syntax)
MATCH (u:User)-[:FOLLOWS]->(v:User)
WHERE u.name = 'Alice'
RETURN v.name, v.email;

-- Create nodes and relationships
CREATE (a:Person {name: 'Bob', age: 30})
CREATE (b:Company {name: 'ACME'})
CREATE (a)-[:WORKS_AT {since: 2020}]->(b);

-- Merge (upsert)
MERGE (u:User {email: 'bob@example.com'})
ON CREATE SET u.created = timestamp()
ON MATCH SET u.last_seen = timestamp()
RETURN u;

-- Delete nodes
MATCH (u:User {name: 'Bob'}) DELETE u;

-- Analyze — collect table statistics for the query optimizer
ANALYZE table;
ANALYZE;  -- Analyze all tables

-- Vacuum — reclaim storage from deleted rows
VACUUM table;
VACUUM FULL table;  -- Full table rewrite (locks table briefly)

-- Explain — show query execution plan
EXPLAIN SELECT * FROM users WHERE age > 30;
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;  -- Also execute and time it

-- Prepared statements (parameter binding)
PREPARE stmt AS SELECT * FROM users WHERE id = $1;
EXECUTE stmt(42);
DEALLOCATE stmt;

-- Index management
REINDEX TABLE users;
REINDEX INDEX idx_users_email;

-- Create stored function
CREATE FUNCTION fn_name(param1 INTEGER, param2 TEXT) RETURNS INTEGER
AS 'SELECT param1 + LENGTH(param2)';

-- SQL/PSM function with body
CREATE FUNCTION calc_discount(price REAL, tier TEXT) RETURNS REAL AS $$
BEGIN
    IF tier = 'gold' THEN RETURN price * 0.80;
    ELSIF tier = 'silver' THEN RETURN price * 0.90;
    ELSE RETURN price;
    END IF;
END;
$$;

-- Call
SELECT fn_name(10, 'hello');
SELECT calc_discount(99.99, 'gold');

-- Drop
DROP FUNCTION fn_name;
DROP FUNCTION IF EXISTS calc_discount;

-- Triggers
CREATE TRIGGER trg_audit AFTER INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION log_change();

CREATE TRIGGER trg_validate BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION check_price();

DROP TRIGGER trg_audit ON orders;

-- Create an edge between two nodes
RELATE user:1 -> follows -> user:2;
RELATE user:1 -> likes -> post:42 PROPERTIES (weight = 1.0);

-- Traverse outgoing edges
TRAVERSE user:1 -> follows;

-- Traverse incoming edges
TRAVERSE user:2 <- follows;

-- BFS shortest path
GRAPH SHORTEST_PATH FROM user:1 TO user:100 EDGE follows;

-- DFS with depth limit
GRAPH DFS FROM user:1 DEPTH 3 EDGE follows;

-- AI context subgraph
CONTEXT_GRAPH user:1 DEPTH 3;

-- Subscribe to all changes on a table
LIVE SELECT * FROM orders;

-- Subscribe with filter
LIVE SELECT * FROM orders WHERE status = 'pending';

-- Subscribe to database-level changes
SUBSCRIBE TO TABLE orders WHERE status = 'pending';
SUBSCRIBE TO DATABASE mydb STARTING AT LSN 12345;

-- Unsubscribe using subscription ID
KILL subscription_id;