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