Know-How
PostgreSQL Advanced Features
PostgreSQL je nejpokročilejší open-source databáze. JSONB, window functions, partitioning — features, které v MySQL nenajdete.
JSONB
-- Uložení a dotazování
CREATE TABLE events (id SERIAL, data JSONB);
INSERT INTO events (data) VALUES ('{"type": "click", "page": "/home"}');
SELECT data->>'type' as event_type FROM events;
SELECT * FROM events WHERE data @> '{"type": "click"}';
CREATE INDEX idx_events_data ON events USING GIN(data);
Window Functions
-- Ranking
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;
-- Running total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;
CTE (Common Table Expression)
WITH monthly_stats AS (
SELECT DATE_TRUNC('month', created_at) as month,
COUNT(*) as total, SUM(amount) as revenue
FROM orders
GROUP BY 1
)
SELECT month, total, revenue,
LAG(revenue) OVER (ORDER BY month) as prev_revenue
FROM monthly_stats;
Klíčový takeaway
PostgreSQL JSONB nahradí MongoDB pro většinu use cases. Window functions pro analytiku. CTE pro čitelnost.