Advanced Features

Window Functions

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Running totals and moving averages
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- LAG and LEAD — access previous/next row
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS change
FROM monthly_revenue;

-- NTILE — divide into buckets
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- FIRST_VALUE, LAST_VALUE
SELECT
  name, department, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees;

JSONB

-- Creating JSONB columns
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "ssd": 512}, "tags": ["laptop", "work"]}');

-- Accessing JSON fields
SELECT attributes->>'brand' AS brand FROM products;           -- text
SELECT attributes->'specs'->>'ram' AS ram FROM products;      -- nested text
SELECT attributes->'specs'->'ram' AS ram_num FROM products;   -- numeric (JSONB)

-- Filtering
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';
SELECT * FROM products WHERE (attributes->'specs'->>'ram')::int > 8;
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';  -- containment
SELECT * FROM products WHERE attributes ? 'brand';               -- key exists
SELECT * FROM products WHERE attributes->'tags' ? 'laptop';      -- array contains

-- Updating JSONB
UPDATE products
SET attributes = jsonb_set(attributes, '{specs, ram}', '32')
WHERE id = 1;

-- Remove a key
UPDATE products
SET attributes = attributes - 'brand'
WHERE id = 1;

-- JSONB aggregation
SELECT jsonb_agg(name) AS names, jsonb_object_agg(id, name) AS id_name_map
FROM products;

Full-Text Search

-- Create FTS index
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || body);
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Highlight matches
SELECT ts_headline('english', body, to_tsquery('postgresql'), 'MaxWords=35, MinWords=15')
FROM articles WHERE search_vector @@ to_tsquery('postgresql');

-- Phrase search
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');

Transactions & Locking

-- Transaction basics
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK;

-- Savepoints
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 200);
  SAVEPOINT after_insert;
  UPDATE inventory SET stock = stock - 1 WHERE product_id = 5;
  -- if something fails:
  ROLLBACK TO after_insert;
COMMIT;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory locks (application-level)
SELECT pg_advisory_lock(12345);    -- blocks
SELECT pg_try_advisory_lock(12345); -- returns bool, non-blocking
SELECT pg_advisory_unlock(12345);

-- Row-level locking
SELECT * FROM jobs WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

Useful Extras

-- UPSERT (INSERT ... ON CONFLICT)
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- RETURNING — get inserted/updated data
INSERT INTO users (email) VALUES ('b@c.com') RETURNING id, created_at;
UPDATE orders SET status = 'shipped' WHERE id = 42 RETURNING *;

-- Arrays
SELECT ARRAY[1, 2, 3] AS nums;
SELECT '{apple,banana}'::text[] AS fruits;
SELECT 5 = ANY(ARRAY[1, 3, 5]) AS found;           -- true
SELECT ARRAY_AGG(name ORDER BY name) FROM users;
SELECT UNNEST(tags) AS tag FROM articles;

-- Generate series
SELECT generate_series(1, 10) AS n;
SELECT generate_series('2024-01-01'::date, '2024-12-31', '1 month') AS month;