PostgreSQL Advanced Query Examples

PostgreSQL is known for its standards compliance and performance — but what truly sets it apart is its depth. Beyond the typical SELECT, JOIN, and WHERE statements lies a powerful set of query features that can make your SQL far more expressive and efficient.

In this article, we’ll explore advanced PostgreSQL queries that go beyond the basics — including recursive CTEs, partitioned tables, JSONB filtering, window functions, and more. Each example includes simple schema setup statements, so you can try them out directly in your environment.

1. Recursive CTE: Handling Parent–Child Relationships

Recursive Common Table Expressions (CTEs) are perfect for working with hierarchical data like categories or organization trees.

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT,
    parent_id INT REFERENCES categories(id)
);

INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Laptops', 1),
('Phones', 1),
('Gaming Laptops', 2),
('Ultrabooks', 2);

WITH RECURSIVE category_hierarchy AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ch.level + 1
  FROM categories c
  INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM category_hierarchy ORDER BY level;

This query walks through the hierarchy, starting from the top-level parent and recursively joining child nodes. It’s an elegant and efficient way to visualize hierarchical relationships without complex application logic.

2. Partitioned Tables for Scalable Queries

When working with large datasets, table partitioning improves query performance and manageability. PostgreSQL’s native support for range, list, and hash partitions makes it straightforward.

CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL,
    amount DECIMAL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

INSERT INTO orders VALUES
(1, '2024-03-12', 120),
(2, '2025-02-01', 340);

SELECT tableoid::regclass AS partition, COUNT(*) FROM orders GROUP BY partition;

Here, tableoid::regclass helps identify which partition a record lives in — a handy trick for debugging and understanding partitioned data distribution.

3. Querying JSONB Data

Modern applications often store flexible schemas inside JSON columns. PostgreSQL’s JSONB type lets you query this data efficiently using built-in operators.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO products (data) VALUES
('{"name": "Laptop", "specs": {"ram": "16GB", "cpu": "i7"}}'),
('{"name": "Phone", "specs": {"ram": "8GB", "cpu": "Snapdragon"}}');

SELECT
    data->>'name' AS product_name,
    data->'specs'->>'ram' AS ram
FROM products
WHERE data->'specs'->>'cpu' = 'i7';

The -> and ->> operators allow you to traverse and extract values from JSONB fields. This makes PostgreSQL an excellent choice for semi-structured data without sacrificing SQL power.

4. Window Functions for Ranking and Analytics

Window functions add analytical capabilities directly into SQL. You can calculate ranks, running totals, or percentages across specific partitions of data.

CREATE TABLE sales (
    id SERIAL,
    product TEXT,
    amount INT,
    sale_date DATE
);

INSERT INTO sales (product, amount, sale_date) VALUES
('Laptop', 1000, '2024-01-01'),
('Laptop', 1200, '2024-01-10'),
('Phone', 500, '2024-01-02'),
('Phone', 700, '2024-01-08');

SELECT
  product,
  amount,
  RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM sales;

This query assigns ranks per product category based on sales amount, making it perfect for leaderboards or performance summaries.

5. Lateral Joins for Row-by-Row Subqueries

Lateral joins let you run a subquery for each row of another query. This is especially useful for fetching related data dynamically.

CREATE TABLE authors (
    id SERIAL,
    name TEXT
);

CREATE TABLE books (
    id SERIAL,
    author_id INT REFERENCES authors(id),
    title TEXT
);

INSERT INTO authors (name) VALUES ('Alice'), ('Bob');
INSERT INTO books (author_id, title) VALUES
(1, 'PostgreSQL Deep Dive'),
(1, 'CTE Patterns'),
(2, 'JSONB in Action');

SELECT a.name, b.title
FROM authors a
LEFT JOIN LATERAL (
    SELECT title FROM books WHERE books.author_id = a.id LIMIT 1
) b ON TRUE;

This pattern is cleaner than correlated subqueries and often performs better, especially when used with indexes.

6. Time-Based Aggregation Using generate_series

Sometimes you need to include missing time intervals in reports. The generate_series function helps fill in the gaps.

WITH dates AS (
  SELECT generate_series('2024-01-01'::date, '2024-01-07'::date, interval '1 day') AS day
),
sales AS (
  SELECT DATE '2024-01-01' AS day, 100 AS amount
  UNION ALL
  SELECT '2024-01-03', 200
)
SELECT d.day, COALESCE(s.amount, 0) AS amount
FROM dates d
LEFT JOIN sales s USING (day)
ORDER BY d.day;

This ensures even days without sales appear in your report — a must for time-series visualizations and daily statistics dashboards.

Conclusion

PostgreSQL isn’t just another relational database — it’s a data powerhouse. With features like recursive queries, native partitioning, and JSONB filtering, it bridges the gap between traditional RDBMS and document or analytical systems.

By mastering these advanced queries, you can write more efficient, expressive SQL that keeps your application logic clean and your data layer powerful.