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).

Solution: Make extension usage optional with error handling:

-- Try to create extension, ignore if unavailable
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm') THEN
    BEGIN
      CREATE EXTENSION pg_trgm;
    EXCEPTION
      WHEN insufficient_privilege OR feature_not_supported THEN
        RAISE NOTICE 'pg_trgm extension not available - skipping trigram indexes';
    END;
  END IF;
END $$;

-- Only create trigram indexes if extension exists
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm') THEN
    CREATE INDEX idx_name_trgm ON packages USING gin(name gin_trgm_ops);
  END IF;
END $$;

4. Idempotent Migrations

Always use IF (NOT) EXISTS to make migrations re-runnable:

-- Tables
CREATE TABLE IF NOT EXISTS users (...);

-- Columns
ALTER TABLE users ADD COLUMN IF NOT EXISTS email VARCHAR(255);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Drop operations
DROP TABLE IF EXISTS old_table CASCADE;
DROP INDEX IF EXISTS old_index;
DROP VIEW IF EXISTS old_view CASCADE;
DROP FUNCTION IF EXISTS old_function(args);

-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

5. Handling Circular Dependencies

Issue: Table A references table B, table B references table A.

Solution: Create tables first without foreign keys, then add constraints:

-- Step 1: Create tables without foreign keys
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255)
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  author_id UUID  -- No FK constraint yet
);

-- Step 2: Add foreign key constraints
ALTER TABLE posts
ADD CONSTRAINT fk_posts_author
FOREIGN KEY (author_id) REFERENCES users(id);

6. Working with Generated Columns

Rules:

Must use IMMUTABLE functions only
Cannot reference other generated columns
Use STORED (not VIRTUAL in PostgreSQL)
Cannot be updated directly

-- ✅ Good - IMMUTABLE functions
ALTER TABLE packages
ADD COLUMN full_name TEXT
GENERATED ALWAYS AS (namespace || '/' || name) STORED;

-- ✅ Good - with COALESCE for nulls
ALTER TABLE packages
ADD COLUMN search_text TEXT
GENERATED ALWAYS AS (
  coalesce(name, '') || ' ' || coalesce(description, '')
) STORED;

-- ❌ Bad - NOW() is not immutable
ALTER TABLE logs
ADD COLUMN year INTEGER
GENERATED ALWAYS AS (EXTRACT(YEAR FROM NOW())) STORED;  -- ERROR

-- ✅ Good - use created_at column instead
ALTER TABLE logs
ADD COLUMN year INTEGER
GENERATED ALWAYS AS (EXTRACT(YEAR FROM created_at)) STORED;

7. Materialized Views

Best Practices:

-- Create with data
CREATE MATERIALIZED VIEW IF NOT EXISTS package_rankings AS
SELECT
  id,
  name,
  total_downloads,
  ROW_NUMBER() OVER (ORDER BY total_downloads DESC) as rank
FROM packages
WHERE visibility = 'public';

-- Create indexes on materialized views
CREATE INDEX IF NOT EXISTS idx_rankings_downloads
ON package_rankings(total_downloads DESC);

-- Refresh function
CREATE OR REPLACE FUNCTION refresh_rankings()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY package_rankings;
END;
$$ LANGUAGE plpgsql;

-- Schedule refresh (requires pg_cron extension)
-- SELECT cron.schedule('refresh-rankings', '0 * * * *', 'SELECT refresh_rankings()');

8. Full-Text Search Optimization

Pattern: Use generated column + GIN index for best performance:

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

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

-- 3. Create GIN index
CREATE INDEX idx_packages_search ON packages USING gin(search_vector);

-- 4. Query using the index
SELECT *
FROM packages
WHERE search_vector @@ websearch_to_tsquery('english', 'react hooks');

9. Composite Indexes for Common Queries

Principles:

Equality filters first, then ranges, then sorts
Most selective columns first
Include WHERE clause conditions

-- Query: WHERE type = 'agent' AND category = 'development' ORDER BY downloads DESC
CREATE INDEX idx_packages_type_category_downloads
ON packages(type, category, total_downloads DESC)
WHERE visibility = 'public';

-- Query: WHERE author = 'foo' AND deprecated = FALSE ORDER BY created_at DESC
CREATE INDEX idx_packages_author_active
ON packages(author_id, created_at DESC)
WHERE deprecated = FALSE AND visibility = 'public';

-- Partial index for common filter
CREATE INDEX idx_packages_verified
ON packages(verified, total_downloads DESC)
WHERE verified = TRUE AND visibility = 'public';

10. Migration File Structure

Best Practice Template:

-- Migration XXX: Description
-- Brief explanation of what this migration does

-- ============================================
-- EXTENSIONS
-- ============================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ============================================
-- TABLES
-- ============================================

CREATE TABLE IF NOT EXISTS table_name (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX IF NOT EXISTS idx_table_name ON table_name(name);

-- ============================================
-- VIEWS
-- ============================================

CREATE OR REPLACE VIEW view_name AS
SELECT * FROM table_name WHERE active = true;

-- =================

postgres-migrations

Como adicionar

Cole no README do seu repo

Skills relacionadas

xlsx

how-it-works

weekly-digests

mem-search

Receba novas skills de Dados e Análise toda segunda