PostgreSQL Query Cookbook

Advanced open-source object-relational database system emphasizing extensibility and standards compliance. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Upsert Record with ON CONFLICT

Insert a new row or update existing row columns when primary key conflicts.

INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON CONFLICT (user_id) DO UPDATE SET
  login_count = user_stats.login_count + 1,
  last_login = EXCLUDED.last_login;

#upsert#conflict#insert

2. Query Nested JSONB Properties

Filter rows based on deeply nested JSON key values using arrow operators.

SELECT id, metadata->>'title' AS title
FROM documents
WHERE metadata @> '{"status": "published", "author": "Alex"}';

#jsonb#json#query

3. Group Values into Delimited List

Aggregate text rows into a single comma-separated list per group.

SELECT department_id,
       STRING_AGG(first_name, ', ' ORDER BY first_name) AS team_members
FROM employees
GROUP BY department_id;

#string_agg#group_by#aggregate

4. Window Functions for Lead/Lag Analysis

Compute previous and next period row values without self-joins.

SELECT sale_date, amount,
       LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
       amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS diff
FROM daily_sales;

#window#lag#analytics

5. Create Range Partitioned Table

Divide large tables into date range partitions for query pruning.

CREATE TABLE sales_log (
  id BIGSERIAL,
  sale_date DATE NOT NULL,
  amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2026_q1 PARTITION OF sales_log
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

#partition#ddl#scaling

6. Full-Text Search with tsvector

Perform natural language text matching with rank scoring.

SELECT title, ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC;

#fts#search#text

7. Recursive CTE for Hierarchy Trees

Traverse employee-manager organizational hierarchies recursively.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree ORDER BY level, id;

#cte#recursive#hierarchy

8. Find Running & Blocked Queries

Inspect active database locks and long-running client sessions.

SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND now() - query_start > INTERVAL '5 seconds'
ORDER BY duration DESC;

#dba#monitoring#locks

9. Check Size of Tables & Indexes

Retrieve total disk space consumption for database tables.

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

#dba#storage#size

10. Generate Date Ranges with generate_series

Create continuous date series tables for zero-filling missing trend data.

SELECT generate_series(
  '2026-01-01'::date,
  '2026-01-07'::date,
  '1 day'::interval
)::date AS calendar_day;

#generate_series#utility#dates

Test your PostgreSQL architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study PostgreSQL Flashcards →