PostgreSQL SQL

-- Define array column
CREATE TABLE example (
    tags TEXT[],
    scores INTEGER[]
);

-- Insert arrays
INSERT INTO example (tags, scores)
VALUES (ARRAY['tag1', 'tag2'], ARRAY[85, 90, 78]);

-- Array functions
SELECT array_length(tags, 1) FROM example;
SELECT unnest(tags) FROM example;
SELECT array_agg(name) FROM users;

-- JSONB is preferred (indexable, faster queries)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Insert JSON
INSERT INTO events (data) VALUES ('{"type": "click", "element": "button"}');

-- Query JSON
SELECT data->>'type' AS type FROM events;  -- Returns text
SELECT data->'element' FROM events;         -- Returns JSON

-- JSON path queries
SELECT data #>> '{nested,key}' FROM events;

-- Contains operator
SELECT * FROM events WHERE data @> '{"type": "click"}';

-- Key exists
SELECT * FROM events WHERE data ? 'type';

-- JSONB functions
SELECT jsonb_set(data, '{status}', '"active"') FROM events;
SELECT jsonb_build_object('name', name, 'email', email) FROM users;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    is_active BOOLEAN DEFAULT TRUE,
    metadata JSONB DEFAULT '{}'
);

-- With foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

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

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Change type
ALTER TABLE users ALTER COLUMN email TYPE TEXT;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT email_check CHECK (email LIKE '%@%');

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT email_check;

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

-- Set default
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT TRUE;

-- Drop default
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;

-- Set NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop NOT NULL
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- Multi-column index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- GIN index (for JSONB, arrays, full-text)
CREATE INDEX idx_events_data ON events USING GIN(data);

-- GiST index (for geometry, range types)
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);

-- BRIN index (for large sorted tables)
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- Concurrent index (no locking)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Drop index
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;

-- Single row
INSERT INTO users (email, username, password_hash)
VALUES ('user@example.com', 'johndoe', 'hash123');

-- Multiple rows
INSERT INTO users (email, username, password_hash) VALUES
    ('user1@example.com', 'user1', 'hash1'),
    ('user2@example.com', 'user2', 'hash2'),
    ('user3@example.com', 'user3', 'hash3');

-- Return inserted row
INSERT INTO users (email, username, password_hash)
VALUES ('new@example.com', 'newuser', 'hash')
RETURNING id, email, created_at;

-- Insert from select
INSERT INTO user_archive (id, email, username)
SELECT id, email, username FROM users WHERE is_active = FALSE;

-- Upsert (INSERT ... ON CONFLICT)
INSERT INTO users (email, username, password_hash)
VALUES ('user@example.com', 'johndoe', 'newhash')
ON CONFLICT (email) DO UPDATE SET
    password_hash = EXCLUDED.password_hash,
    updated_at = NOW();

-- Upsert - do nothing on conflict
INSERT INTO users (email, username, password_hash)
VALUES ('user@example.com', 'johndoe', 'hash')
ON CONFLICT (email) DO NOTHING;

-- Simple update
UPDATE users SET is_active = FALSE WHERE id = 1;

-- Multiple columns
UPDATE users SET
    username = 'newname',
    updated_at = NOW()
WHERE id = 1;

-- Update with subquery
UPDATE orders SET
    total = (SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.id)
WHERE status = 'pending';

-- Update with FROM
UPDATE orders o SET
    status = 'shipped'
FROM shipments s
WHERE s.order_id = o.id AND s.shipped_at IS NOT NULL;

-- Update returning
UPDATE users SET is_active = FALSE
WHERE last_login < NOW() - INTERVAL '1 year'
RETURNING id, email;

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

-- Delete with subquery
DELETE FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE is_active = FALSE
);

-- Delete with USING (join)
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id AND u.is_active = FALSE;

-- Delete returning
DELETE FROM users WHERE is_active = FALSE
RETURNING id, email;

-- Truncate (fast delete all)
TRUNCATE TABLE logs;
TRUNCATE TABLE orders CASCADE;  -- Also truncate referencing tables

-- Select all
SELECT * FROM users;

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

-- With alias
SELECT id, email AS user_email, username AS name FROM users;

-- Distinct
SELECT DISTINCT status FROM orders;
SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC;

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

-- Order by
SELECT * FROM users ORDER BY created_at DESC, username ASC;
SELECT * FROM users ORDER BY created_at DESC NULLS LAST;

-- Comparison
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM orders WHERE total > 100;
SELECT * FROM orders WHERE created_at >= '2024-01-01';

-- IN
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE status IN ('active', 'pending');

-- BETWEEN
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE / ILIKE
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username ILIKE '%john%';  -- Case insensitive

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- AND / OR
SELECT * FROM users WHERE is_active = TRUE AND created_at > '2024-01-01';
SELECT * FROM users WHERE status = 'admin' OR status = 'superuser';

-- NOT
SELECT * FROM users WHERE NOT is_active;
SELECT * FROM users WHERE email NOT LIKE '%@test.com';

-- ANY / ALL
SELECT * FROM products WHERE price > ANY(SELECT price FROM discounted_products);
SELECT * FROM products WHERE price > ALL(SELECT price FROM cheap_products);

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

-- INNER JOIN
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (include all from left table)
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN (include all from right table)
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN (include all from both)
SELECT u.username, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN (cartesian product)
SELECT * FROM colors CROSS JOIN sizes;

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

-- Multiple joins
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- JOIN with conditions
SELECT u.username, o.total
FROM users u
JOIN orders o ON u.id = o.user_id AND o.status = 'completed';

-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT user_id) FROM orders;
SELECT COUNT(*) FILTER (WHERE is_active) FROM users;

-- SUM / AVG / MIN / MAX
SELECT SUM(total) FROM orders;
SELECT AVG(total) FROM orders;
SELECT MIN(created_at), MAX(created_at) FROM orders;

-- GROUP BY
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY user_id;

-- GROUP BY with HAVING
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

-- GROUPING SETS
SELECT status, user_id, COUNT(*)
FROM orders
GROUP BY GROUPING SETS ((status), (user_id), ());

-- ROLLUP (hierarchical)
SELECT status, user_id, COUNT(*)
FROM orders
GROUP BY ROLLUP (status, user_id);

-- CUBE (all combinations)
SELECT status, user_id, COUNT(*)
FROM orders
GROUP BY CUBE (status, user_id);

-- Basic CTE
WITH active_users AS (
    SELECT * FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

-- Multiple CTEs
WITH
    active_users AS (
        SELECT * FROM users WHERE is_active = TRUE
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) AS order_count
        FROM orders
        GROUP BY user_id
    )
SELECT u.username, COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

-- Recursive CTE
WITH RECURSIVE subordinates AS (
    -- Base case
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1  -- Start from CEO

    UNION ALL

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

-- CTE for running totals
WITH RECURSIVE date_series AS (
    SELECT DATE '2024-01-01' AS date
    UNION ALL
    SELECT date + INTERVAL '1 day'
    FROM date_series
    WHERE date < '2024-12-31'
)
SELECT * FROM date_series;

-- ROW_NUMBER
SELECT
    id, username, created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- RANK / DENSE_RANK
SELECT
    id, total,
    RANK() OVER (ORDER BY total DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rank
FROM orders;

-- PARTITION BY
SELECT
    user_id, id, total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num,
    SUM(total) OVER (PARTITION BY user_id) AS user_total
FROM orders;

-- LAG / LEAD
SELECT
    id, total, created_at,
    LAG(total) OVER (ORDER BY created_at) AS prev_total,
    LEAD(total) OVER (ORDER BY created_at) AS next_total
FROM orders;

-- Running total
SELECT
    id, total, created_at,
    SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- Moving average
SELECT
    id, total, created_at,
    AVG(total) OVER (
        ORDER BY created_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

-- FIRST_VALUE / LAST_VALUE
SELECT
    id, total,
    FIRST_VALUE(total) OVER (ORDER BY created_at) AS first_order,
    LAST_VALUE(total) OVER (
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

-- NTILE (divide into buckets)
SELECT
    id, total,
    NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;

-- Percent rank
SELECT
    id, total,
    PERCENT_RANK() OVER (ORDER BY total) AS pct_rank,
    CUME_DIST() OVER (ORDER BY total) AS cumulative_dist
FROM orders;

-- Scalar subquery
SELECT
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- IN subquery
SELECT * FROM users WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE total > 100
);

-- FROM subquery (derived table)
SELECT avg_total, COUNT(*) AS user_count
FROM (
    SELECT user_id, AVG(total) AS avg_total
    FROM orders
    GROUP BY user_id
) AS user_averages
GROUP BY avg_total;

-- Correlated subquery
SELECT * FROM orders o1
WHERE total > (
    SELECT AVG(total) FROM orders o2 WHERE o2.user_id = o1.user_id
);

-- LATERAL join
SELECT u.username, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT * FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent_orders;

-- UNION (removes duplicates)
SELECT email FROM users
UNION
SELECT email FROM newsletter_subscribers;

-- UNION ALL (keeps duplicates)
SELECT email FROM users
UNION ALL
SELECT email FROM newsletter_subscribers;

-- INTERSECT
SELECT email FROM users
INTERSECT
SELECT email FROM premium_users;

-- EXCEPT
SELECT email FROM users
EXCEPT
SELECT email FROM blocked_users;

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT_WS(', ', city, state, country) AS location,
    UPPER(email) AS upper_email,
    LOWER(username) AS lower_username,
    LENGTH(description) AS desc_length,
    SUBSTRING(phone FROM 1 FOR 3) AS area_code,
    REPLACE(text, 'old', 'new') AS replaced,
    TRIM(BOTH ' ' FROM text) AS trimmed,
    LEFT(text, 10) AS first_10,
    RIGHT(text, 10) AS last_10,
    SPLIT_PART(email, '@', 2) AS domain,
    REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only
FROM users;

SELECT
    NOW() AS current_timestamp,
    CURRENT_DATE AS today,
    CURRENT_TIME AS current_time,
    DATE_TRUNC('month', created_at) AS month_start,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(DOW FROM created_at) AS day_of_week,
    AGE(NOW(), created_at) AS age,
    created_at + INTERVAL '1 day' AS tomorrow,
    created_at - INTERVAL '1 hour' AS hour_ago,
    TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS formatted,
    TO_TIMESTAMP('2024-01-15', 'YYYY-MM-DD') AS parsed
FROM users;

SELECT
    CASE
        WHEN total > 1000 THEN 'high'
        WHEN total > 100 THEN 'medium'
        ELSE 'low'
    END AS order_tier,
    COALESCE(phone, email, 'no contact') AS contact,
    NULLIF(status, 'unknown') AS status_clean,
    GREATEST(a, b, c) AS max_value,
    LEAST(a, b, c) AS min_value
FROM orders;

SELECT
    COUNT(*) AS total,
    COUNT(DISTINCT user_id) AS unique_users,
    SUM(total) AS sum,
    AVG(total) AS average,
    MIN(total) AS minimum,
    MAX(total) AS maximum,
    STRING_AGG(username, ', ') AS user_list,
    ARRAY_AGG(username) AS user_array,
    JSONB_AGG(data) AS json_array,
    BOOL_AND(is_active) AS all_active,
    BOOL_OR(is_admin) AS any_admin
FROM users;