postgres-migrations

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
GROUP BY SPLIT_PART(p.id, '/', 1);

-- ✅ Good - use CTE to compute aggregates first
WITH author_stats AS (
  SELECT
    SPLIT_PART(p.id, '/', 1) as author,
    COUNT(*) as count
  FROM packages p
  GROUP BY SPLIT_PART(p.id, '/', 1)
)
SELECT
  author,
  count,
  EXISTS (SELECT 1 FROM users WHERE username = author_stats.author) as claimed
FROM author_stats;

2. "Functions in index expression must be marked IMMUTABLE"

Cause: PostgreSQL requires functions in indexes/generated columns to be IMMUTABLE.

Problem Functions:

• array_to_string() - marked STABLE, not IMMUTABLE
• to_char() - depends on timezone/locale settings
• now() - changes over time

Solution: Create IMMUTABLE wrapper functions:

-- Create IMMUTABLE wrapper for array_to_string
CREATE OR REPLACE FUNCTION immutable_array_to_string(text[], text)
RETURNS text AS $$
  SELECT array_to_string($1, $2)
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;

-- Use in generated column
ALTER TABLE packages
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
  setweight(to_tsvector('english', immutable_array_to_string(tags, ' ')), 'B')
) STORED;

-- Now you can index it
CREATE INDEX idx_search ON packages USING gin(search_vector);

3. "Relation does not exist" (Extensions)

Cause: Extension not installed (e.g., pg_stat_statements, pg_trgm, uuid-ossp).