PostgreSQL: The World's Most Advanced Open Source Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Storing and querying arrays
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[]  -- Array of text
);
 
INSERT INTO products (name, tags) 
VALUES ('PostgreSQL Book', ARRAY['database', 'sql', 'learning']);
 
-- Query using array operators
SELECT * FROM products WHERE 'sql' = ANY(tags);  -- Contains element
SELECT * FROM products WHERE tags @> ARRAY['database', 'sql'];  -- Contains all
SELECT * FROM products WHERE tags && ARRAY['nosql', 'sql'];  -- Overlaps with
 
-- Array functions
SELECT array_length(tags, 1) FROM products;  -- Length of array
SELECT unnest(tags) FROM products;  -- Expand to rows
SELECT array_agg(DISTINCT tag) FROM products, unnest(tags) AS tag;  -- Aggregate back
 
-- GIN index for efficient array queries
CREATE INDEX idx_products_tags ON products USING GIN(tags);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- JSONB column for flexible schemas
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
INSERT INTO events (event_type, payload) VALUES 
('user.created', '{"userId": 123, "email": "user@example.com", "plan": "pro"}'),
('order.placed', '{"orderId": 456, "items": [{"sku": "ABC", "qty": 2}], "total": 99.99}');
 
-- Query JSON fields
SELECT payload->>'email' AS email FROM events WHERE event_type = 'user.created';
SELECT payload->'items'->0->>'sku' AS first_item_sku FROM events WHERE event_type = 'order.placed';
 
-- Filter on JSON values
SELECT * FROM events WHERE payload->>'plan' = 'pro';
SELECT * FROM events WHERE (payload->>'total')::numeric > 50;
 
-- JSON containment queries (@> operator)
SELECT * FROM events WHERE payload @> '{"plan": "pro"}';
 
-- Index JSONB for performance
CREATE INDEX idx_events_payload ON events USING GIN(payload);
 
-- Index specific JSON paths
CREATE INDEX idx_events_user_id ON events ((payload->>'userId'));
 
-- JSON aggregation
SELECT jsonb_agg(payload) FROM events WHERE event_type LIKE 'user.%';
 
-- Build JSON dynamically
SELECT jsonb_build_object(
    'type', event_type,
    'data', payload,
    'timestamp', created_at
) FROM events;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

-- Basic full-text search
SELECT to_tsvector('english', 'PostgreSQL is a powerful database system');
-- Result: 'databas':5 'postgresql':1 'power':4 'system':6
-- Note: 'is' and 'a' removed (stop words), words stemmed ('powerful' → 'power')
 
SELECT to_tsquery('english', 'database & powerful');
-- Result: 'databas' & 'power'
 
-- Search example
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED
);
 
-- GIN index for fast search
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);
 
-- Search with ranking
SELECT 
    title,
    ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & replication') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
 
-- Phrase search: words must appear adjacent
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('english', 'master <-> replica');
 
-- Prefix search (autocomplete)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postg:*');
 
-- Highlighting matches
SELECT ts_headline(
    'english',
    body,
    to_tsquery('english', 'PostgreSQL & performance'),
    'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
) FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Employee hierarchy traversal
WITH RECURSIVE org_chart AS (
    -- Base case: top-level managers (no manager)
    SELECT id, name, manager_id, 1 AS level, ARRAY[name] AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers already in result
    SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || e.name
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    repeat('  ', level - 1) || name AS org_structure,
    level,
    array_to_string(path, ' → ') AS reporting_chain
FROM org_chart
ORDER BY path;
 
-- Result:
-- org_structure          | level | reporting_chain
-- CEO                    | 1     | CEO
--   VP Engineering       | 2     | CEO → VP Engineering
--     Senior Engineer    | 3     | CEO → VP Engineering → Senior Engineer
--   VP Sales             | 2     | CEO → VP Sales

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- Running total and row numbers
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (ORDER BY order_date) AS order_num
FROM orders;
 
-- Ranking within partitions
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS gap_from_prev
FROM employees;
 
-- Moving averages and analysis
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_avg,
    revenue - AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS deviation,
    NTILE(4) OVER (ORDER BY revenue) AS revenue_quartile
FROM daily_metrics;
 
-- First/last values in partitions
SELECT DISTINCT
    customer_id,
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10, 2) NOT NULL,
    quantity INTEGER NOT NULL,
    -- Virtual generated column (computed on read)
    total DECIMAL(12, 2) GENERATED ALWAYS AS (price * quantity) STORED,
    -- Can be indexed since it's STORED
    search_name TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', name)) STORED,
    -- Useful for normalized lookups
    lower_email VARCHAR(255) GENERATED ALWAYS AS (lower(email)) STORED
);
 
-- Generated columns are maintained automatically
INSERT INTO products (price, quantity) VALUES (29.99, 10);
SELECT total FROM products;  -- Returns 299.90, computed by database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- B-Tree: Standard for most queries
CREATE INDEX idx_orders_created ON orders (created_at);
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);
 
-- Multicolumn index with INCLUDE (covering index)
CREATE INDEX idx_orders_covering ON orders (customer_id, created_at) 
    INCLUDE (status, total);  -- Include columns for index-only scans
 
-- Partial index: only index relevant rows
CREATE INDEX idx_orders_pending ON orders (created_at) 
    WHERE status = 'pending';  -- Much smaller than full index
 
-- Expression index: index computed values
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';  -- Uses index
 
-- GIN for JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"user_id": 123}';
 
-- GiST for geometric/range operations
CREATE INDEX idx_reservations_during ON reservations USING GIST (during);
SELECT * FROM reservations WHERE during && '[2024-01-01, 2024-01-07]'::daterange;
 
-- BRIN for time-series data (append-only tables)
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
-- BRIN index on 1 billion rows might be only 1-2 MB!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

-- Complex business logic in the database
CREATE OR REPLACE FUNCTION transfer_funds(
    from_account_id INTEGER,
    to_account_id INTEGER,
    amount DECIMAL(10,2)
) RETURNS BOOLEAN AS $$
DECLARE
    from_balance DECIMAL(10,2);
BEGIN
    -- Check source account balance
    SELECT balance INTO from_balance
    FROM accounts WHERE id = from_account_id
    FOR UPDATE;  -- Lock the row
    
    IF from_balance IS NULL THEN
        RAISE EXCEPTION 'Source account % not found', from_account_id;
    END IF;
    
    IF from_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds: % available, % requested', 
            from_balance, amount;
    END IF;
    
    -- Perform transfer
    UPDATE accounts SET balance = balance - amount WHERE id = from_account_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account_id;
    
    -- Log the transaction
    INSERT INTO transfer_log (from_id, to_id, amount, timestamp)
    VALUES (from_account_id, to_account_id, amount, NOW());
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        -- Log error and re-raise
        INSERT INTO error_log (operation, message, timestamp)
        VALUES ('transfer_funds', SQLERRM, NOW());
        RAISE;
END;
$$ LANGUAGE plpgsql;
 
-- Trigger for audit logging
CREATE OR REPLACE FUNCTION audit_changes() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data, timestamp)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW), NOW());
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, timestamp)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), NOW());
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, timestamp)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD), NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Enable PL/Python
CREATE EXTENSION IF NOT EXISTS plpython3u;
 
-- Use Python for complex text processing
CREATE OR REPLACE FUNCTION extract_email_domain(email TEXT)
RETURNS TEXT AS $$
    import re
    match = re.search(r'@([w.-]+)', email)
    return match.group(1) if match else None
$$ LANGUAGE plpython3u;
 
-- Data science inside the database
CREATE OR REPLACE FUNCTION calculate_statistics(values FLOAT8[])
RETURNS TABLE(mean FLOAT8, std_dev FLOAT8, median FLOAT8) AS $$
    import numpy as np
    arr = np.array(values)
    return [(
        float(np.mean(arr)),
        float(np.std(arr)),
        float(np.median(arr))
    )]
$$ LANGUAGE plpython3u;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

-- Range partitioning by date (common for time-series data)
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
 
-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... continue for each month
 
-- PostgreSQL automatically routes inserts to correct partition
INSERT INTO events (event_type, payload, created_at)
VALUES ('click', '{}', '2024-01-15');  -- Goes to events_2024_01
 
-- Queries automatically scan only relevant partitions
SELECT * FROM events WHERE created_at >= '2024-01-10' AND created_at < '2024-01-20';
-- Only scans events_2024_01, skips all other partitions (partition pruning)
 
-- List partitioning by category
CREATE TABLE products (
    id SERIAL,
    name VARCHAR(100),
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2)
) PARTITION BY LIST (category);
 
CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('electronics', 'computers', 'phones');
CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('clothing', 'shoes', 'accessories');
CREATE TABLE products_default PARTITION OF products DEFAULT;
 
-- Hash partitioning for even distribution
CREATE TABLE sessions (
    id UUID PRIMARY KEY,
    user_id INTEGER NOT NULL,
    data JSONB
) PARTITION BY HASH (user_id);
 
-- Create 16 partitions for parallel operations
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 16, REMAINDER 1);
-- ... through sessions_15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- LISTEN/NOTIFY for real-time updates
-- Session 1:
LISTEN order_updates;
 
-- Session 2 (different connection):
INSERT INTO orders (...) VALUES (...);
NOTIFY order_updates, '{"order_id": 123, "status": "created"}';
 
-- Session 1 receives: Asynchronous notification "order_updates" with payload "{"order_id": 123...}"
 
-- Row-Level Security for multi-tenancy
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON customer_data
    USING (tenant_id = current_setting('app.current_tenant')::int);
 
SET app.current_tenant = '42';
SELECT * FROM customer_data;  -- Only sees tenant 42's data, guaranteed by database
 
-- Materialized Views for fast dashboards
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    date_trunc('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY 1;
 
CREATE UNIQUE INDEX ON daily_sales_summary (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;  -- Non-blocking refresh