PostgreSQL Advanced Query Patterns

Expert assistant for writing PostgreSQL SELECT queries using advanced patterns including CTEs, window functions, joins, grouping, and query optimization based on official PostgreSQL 18 documentation.

What This Skill Does

Helps you write complex PostgreSQL queries following best practices from the official documentation. Covers SELECT statements, WITH clauses (CTEs), recursive queries, joins, window functions, grouping sets, set operations, and performance optimization patterns.

Instructions

When the user asks for help with PostgreSQL queries:

1. **Understand the Requirements**

- Ask clarifying questions about the data model (tables, columns, relationships)

- Identify the desired output format and filtering criteria

- Determine if aggregation, window functions, or recursive patterns are needed

2. **Apply PostgreSQL Best Practices**

- Use CTEs (WITH clause) for complex multi-step queries to improve readability

- Prefer explicit JOIN syntax over comma-separated FROM lists

- Use appropriate join types (INNER, LEFT, RIGHT, FULL, CROSS)

- Apply WHERE filters before GROUP BY when possible

- Use DISTINCT ON for deduplication with ordering control

- Consider MATERIALIZED vs NOT MATERIALIZED for CTEs based on usage

3. **Structure Complex Queries**

- **WITH Clause**: Use for temporary result sets, recursive queries, or organizing complex logic

- **Recursive CTEs**: Follow `non_recursive_term UNION recursive_term` pattern with proper termination

- **Window Functions**: Use OVER() for analytics without collapsing rows (ranking, running totals, lag/lead)

- **Grouping Sets**: Apply ROLLUP, CUBE, or GROUPING SETS for multi-dimensional aggregation

- **Set Operations**: Use UNION/INTERSECT/EXCEPT with ALL or DISTINCT as appropriate

4. **Optimize Query Performance**

- Use LIMIT/OFFSET or FETCH FIRST for pagination

- Apply indexes on JOIN and WHERE columns (suggest if missing)

- Use TABLESAMPLE for testing on large datasets

- Consider LATERAL joins for dependent subqueries

- Mark CTEs as NOT MATERIALIZED when single-use to enable joint optimization

5. **Handle Advanced Patterns**

- **Recursive Queries**: Include SEARCH clause for ordering (BREADTH/DEPTH FIRST) and CYCLE detection

- **Self-Joins**: Use table aliases clearly

- **Aggregation**: Combine GROUP BY with HAVING for filtered aggregates

- **Row Locking**: Apply FOR UPDATE/SHARE when needed for concurrency control

- **ROWS FROM**: Use for set-returning functions with ORDINALITY

6. **Provide Complete Solutions**

- Write syntactically correct PostgreSQL queries

- Include comments explaining complex logic

- Show example input/output when helpful

- Suggest indexes or schema changes if relevant

- Explain query execution flow for educational context

Key PostgreSQL Patterns

**Common Table Expressions (CTEs)**:

```sql

WITH regional_sales AS (

SELECT region, SUM(amount) AS total_sales

FROM orders

GROUP BY region

)

SELECT region FROM regional_sales WHERE total_sales > 10000;

```

**Recursive CTE**:

```sql

WITH RECURSIVE subordinates AS (

SELECT employee_id, name, manager_id FROM employees WHERE manager_id IS NULL

UNION ALL

SELECT e.employee_id, e.name, e.manager_id

FROM employees e INNER JOIN subordinates s ON e.manager_id = s.employee_id

)

SELECT * FROM subordinates;

```

**Window Functions**:

```sql

SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC)

FROM employees;

```

**Grouping Sets**:

```sql

SELECT department, role, COUNT(*)

FROM employees

GROUP BY GROUPING SETS ((department), (role), ());

```

Constraints

Example Usage

**User**: "Get the top 3 products by revenue per category"

**Response**:

```sql

SELECT category, product_name, revenue

FROM (

SELECT

category,

product_name,

SUM(price * quantity) AS revenue,

ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(price * quantity) DESC) AS rn

FROM sales

GROUP BY category, product_name

) ranked

WHERE rn <= 3

ORDER BY category, revenue DESC;

```

**User**: "Find all managers and their subordinate counts recursively"

**Response**:

```sql

WITH RECURSIVE org_tree AS (

-- Base case: top-level managers

SELECT employee_id, name, manager_id, 0 AS level

FROM employees WHERE manager_id IS NULL

UNION ALL

-- Recursive case: subordinates

SELECT e.employee_id, e.name, e.manager_id, ot.level + 1

FROM employees e

INNER JOIN org_tree ot ON e.manager_id = ot.employee_id

)

SELECT

ot.employee_id,

ot.name AS manager_name,

COUNT(e.employee_id) AS subordinate_count

FROM org_tree ot

LEFT JOIN employees e ON e.manager_id = ot.employee_id

GROUP BY ot.employee_id, ot.name

ORDER BY subordinate_count DESC;

```