PostgreSQL migration patterns and best practices
View on GitHubplugins/aai-stack-postgres/skills/postgres-migrations/SKILL.md
February 1, 2026
Select agents to install to:
npx add-skill https://github.com/the-answerai/alphaagent-team/blob/main/plugins/aai-stack-postgres/skills/postgres-migrations/SKILL.md -a claude-code --skill postgres-migrationsInstallation paths:
.claude/skills/postgres-migrations/# PostgreSQL Migrations Skill Patterns for safe PostgreSQL schema migrations. ## Safe Migration Patterns ### Adding Columns ```sql -- Safe: Add nullable column ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- Safe: Add column with default (instant in PG 11+) ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active'; -- Backfill if needed (in batches) UPDATE users SET phone = '' WHERE phone IS NULL; -- Then add NOT NULL if required ALTER TABLE users ALTER COLUMN phone SET NOT NULL; ``` ### Removing Columns ```sql -- Step 1: Stop reading/writing column in application -- Step 2: Deploy application changes -- Step 3: Drop column ALTER TABLE users DROP COLUMN legacy_field; ``` ### Renaming Columns ```sql -- Step 1: Add new column ALTER TABLE users ADD COLUMN full_name VARCHAR(100); -- Step 2: Backfill (in batches) UPDATE users SET full_name = name WHERE full_name IS NULL; -- Step 3: Update app to read/write both -- Step 4: Deploy app changes -- Step 5: Drop old column ALTER TABLE users DROP COLUMN name; ``` ### Adding Constraints ```sql -- Add NOT NULL with default (safe) ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user' NOT NULL; -- Add NOT NULL to existing column (requires data check) -- First ensure no nulls exist UPDATE users SET status = 'active' WHERE status IS NULL; ALTER TABLE users ALTER COLUMN status SET NOT NULL; -- Add check constraint (blocks writes during validation) ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0); -- Add check NOT VALID (doesn't validate existing rows) ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0) NOT VALID; -- Validate later ALTER TABLE users VALIDATE CONSTRAINT chk_age; ``` ### Adding Foreign Keys ```sql -- Add column first ALTER TABLE orders ADD COLUMN customer_id UUID; -- Backfill data UPDATE orders SET customer_id = users.id FROM users WHERE users.legacy_id = orders.legacy_user_id; -- Add foreign key NOT VALID (no lock) ALTER TABLE orders ADD CONSTRAINT fk_orders_custom