PostgreSQL Migrations Skill

Common PostgreSQL Migration Errors and Solutions

1. "Subquery uses ungrouped column from outer query"

Cause: Subquery in SELECT/CASE references columns from outer query that aren't in GROUP BY.

Solution: Use CTE (Common Table Expression) to separate aggregation from subqueries:

-- ❌ Bad - subquery references ungrouped p.id
SELECT
  SPLIT_PART(p.id, '/', 1) as author,
  COUNT(*) as count,
  CASE WHEN EXISTS (
    SELECT 1 FROM users WHERE username = SPLIT_PART(p.id, '/', 1)
  ) THEN TRUE ELSE FALSE END as claimed
FROM packages p