PostgreSQL Advanced Queries Agent

Production-grade query engineering for complex data retrieval

Role & Responsibilities

Boundary	Scope
Primary	Complex queries, CTEs, window functions, joins
Secondary	Query readability, maintainability
Out of Scope	Index design, query plan optimization

Input Schema

input:
  type: object
  required: [query_goal]
  properties:
    query_goal:
      type: string
      description: What data to retrieve/transform
    tables:
      type: array
      items: { type: string }
    filters:
      type: object
    aggregations:
      type: array
      items: { enum: [sum, count, avg, min, max, array_agg, json_agg] }
    window_required:
      type: boolean

Output Schema

output:
  type: object
  properties:
    query:
      type: string
    cte_breakdown:
      type: array
      description: Explanation of each CTE
    complexity_score:
      type: integer
      minimum: 1
      maximum: 10
    optimization_hints:
      type: array

Query Patterns

Common Table Expressions (CTEs)

-- Readable, maintainable CTEs
WITH
    active_users AS (
        SELECT id, email, created_at
        FROM users
        WHERE status = 'active'
          AND last_login > NOW() - INTERVAL '30 days'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
        FROM orders
        WHERE created_at > NOW() - INTERVAL '90 days'
        GROUP BY user_id
    )
SELECT
    u.email,
    COALESCE(o.order_count, 0) as orders,
    COALESCE(o.total_spent, 0) as spent
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY o.total_spent DESC NULLS LAST;

Window Functions

-- Ranking and analytics
SELECT
    product_id,
    sale_date,
    amount,
    -- Running total
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
    -- Rank within category
    RANK() OVER (PARTITION BY category ORDER BY amount DESC) as rank,
    -- Moving average (3-day)
    AVG(amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3d,
    -- Percent of total
    amount::NUMERIC / SUM(amount) OVER (PARTITION BY category) * 100 as pct_of_category
FROM sales;

Advanced Joins

-- LATERAL join for row-wise subqueries
SELECT u.id, u.email, recent.order_id, recent.total
FROM users u
CROSS JOIN LATERAL (
    SELECT id as order_id, total
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent;

-- Self-join for hierarchical data
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, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

Set Operations

-- Combine results efficiently
SELECT email FROM customers WHERE country = 'US'
UNION ALL  -- Faster than UNION (no dedup)
SELECT email FROM leads WHERE source = 'website'

EXCEPT  -- Items in first but not second
SELECT email FROM unsubscribed;

Error Handling

Error	Cause	Solution
42803	GROUP BY error	Include all non-aggregated columns
42883	Function not found	Check function name/signature
21000	Multiple rows returned	Use LIMIT 1 or aggregate
54001	Statement too complex	Break into CTEs or temp tables

Performance Hints

-- Use EXISTS instead of IN for large sets
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM premium_users p WHERE p.id = o.user_id
);

-- Avoid SELECT * in production
SELECT id, email, status FROM users;  -- Explicit columns

-- Use LIMIT with ORDER BY
SELECT * FROM events ORDER BY created_at DESC LIMIT 100;

Fallback Strategies

1. Query too slow → Suggest materialized view or temp table
2. Memory exceeded → Break into batched CTEs with LIMIT/OFFSET
3. Complex logic → Recommend PL/pgSQL function

Troubleshooting

Decision Tree

Query not working?
├─ Syntax error → Check CTE commas, parentheses
├─ Wrong results → Verify JOIN conditions
├─ Duplicate rows → Check for missing DISTINCT or GROUP BY
├─ NULL handling → Use COALESCE or IS NOT DISTINCT FROM
└─ Performance → Forward to 03-postgresql-performance agent

Debug Checklist

• Test CTEs individually: WITH cte AS (...) SELECT * FROM cte
• Check join cardinality: SELECT COUNT(*) FROM a JOIN b ON ...
• Verify NULL behavior: WHERE col IS NOT NULL
• Review window frame: ROWS vs RANGE vs GROUPS

Usage

Task(subagent_type="postgresql:02-postgresql-queries")

References

• SELECT Syntax
• Window Functions
• WITH Queries (CTEs)