PostgreSQL Advanced
Window Functions
-- Ranking functions
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- Aggregate as window
SELECT name, salary, dept,
SUM(salary) OVER (PARTITION BY dept) AS dept_total,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY dept) AS vs_avg
FROM employees;
-- Running total / moving average
SELECT date, amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM sales;
-- LAG / LEAD (access adjacent rows)
SELECT date, revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
LEAD(revenue, 1) OVER (ORDER BY date) AS next_day,
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_revenue;
JSONB Operations
-- Basic JSONB access
SELECT data->>'name' AS name, -- text
data->'address' AS address, -- jsonb
data#>>'{address,city}' AS city -- nested text
FROM users;
-- Query inside JSONB
SELECT * FROM products
WHERE data @> '{"category": "electronics"}'; -- containment
SELECT * FROM products
WHERE data ? 'discount'; -- key exists
SELECT * FROM events
WHERE data @? '$.tags[*] ? (@ == "promo")'; -- jsonpath
-- Modify JSONB
UPDATE users
SET data = jsonb_set(data, '{address,zip}', '"10001"')
WHERE id = 42;
UPDATE users
SET data = data || '{"verified": true}'; -- merge
UPDATE users
SET data = data - 'temp_token'; -- remove key
-- Index JSONB
CREATE INDEX idx_users_data ON users USING GIN(data);
CREATE INDEX idx_users_email ON users ((data->>'email')); -- expression index
-- Aggregate to JSONB
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name))
FROM users WHERE active = true;
Full-Text Search
-- Basic FTS
SELECT title, ts_rank(search_vec, query) AS rank
FROM articles,
to_tsquery('english', 'postgresql & performance') AS query
WHERE search_vec @@ query
ORDER BY rank DESC;
-- Create tsvector column (auto-updated)
ALTER TABLE articles
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
setweight(to_tsvector('english', coalesce(body,'')), 'B')
) STORED;
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vec);
-- Phrase search & prefix
SELECT title FROM articles
WHERE search_vec @@ phraseto_tsquery('english', 'query optimization');
SELECT title FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgre:*'); -- prefix
-- Highlight matches
SELECT ts_headline('english', body, to_tsquery('performance'),
'StartSel=, StopSel=, MaxWords=30') AS snippet
FROM articles;
CTEs and Lateral Joins
-- CTE (Common Table Expression)
WITH monthly_totals AS (
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS total
FROM orders GROUP BY 1
),
ranked AS (
SELECT *, RANK() OVER (ORDER BY total DESC) AS rk
FROM monthly_totals
)
SELECT * FROM ranked WHERE rk <= 3;
-- Recursive CTE (tree traversal)
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c JOIN category_tree t ON c.parent_id = t.id
)
SELECT * FROM category_tree ORDER BY depth, name;
-- LATERAL join (correlated subquery in FROM)
SELECT u.name, recent.title, recent.created_at
FROM users u
CROSS JOIN LATERAL (
SELECT title, created_at FROM posts
WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 3
) AS recent;