# DDL Migrations: Column Operations

Step-by-step migration patterns for column-level changes using the Table Recreation Pattern.

**MUST read [overview.md](overview.md) first** for destructive operation warnings and the common verify & swap pattern.

---

## DROP COLUMN Migration

**Goal:** Remove a column from an existing table.

### Pre-Migration Validation

```sql
SELECT COUNT(*) as total_rows FROM target_table;
SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'target_table' ORDER BY ordinal_position;
```

### Migration Steps

#### Step 1: Create new table excluding the column

```sql
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  tenant_id VARCHAR(255) NOT NULL,
  kept_column1 VARCHAR(255),
  kept_column2 INTEGER
  -- dropped_column is NOT included
);
```

#### Step 2: Migrate data

```sql
INSERT INTO target_table_new (id, tenant_id, kept_column1, kept_column2)
SELECT id, tenant_id, kept_column1, kept_column2
FROM target_table;
```

For tables > 3,000 rows, use [Batched Migration Pattern](batched-migration.md).

**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))

---

## ALTER COLUMN TYPE Migration

**Goal:** Change a column's data type.

### Pre-Migration Validation

**MUST validate data compatibility BEFORE migration** to prevent data loss.

```sql
-- Example: VARCHAR to INTEGER - check for non-numeric values
SELECT COUNT(*) as invalid_count FROM target_table
WHERE column_to_change !~ '^-?[0-9]+$';
-- MUST abort if invalid_count > 0

-- Show problematic rows
SELECT id, column_to_change FROM target_table
WHERE column_to_change !~ '^-?[0-9]+$' LIMIT 100;
```

### Data Type Compatibility Matrix

| From Type | To Type    | Validation                                              |
| --------- | ---------- | ------------------------------------------------------- |
| VARCHAR   | INTEGER    | MUST validate all values are numeric                    |
| VARCHAR   | BOOLEAN    | MUST validate values are 'true'/'false'/'t'/'f'/'1'/'0' |
| INTEGER   | VARCHAR    | Safe conversion                                         |
| TEXT      | VARCHAR(n) | MUST validate max length ≤ n                            |
| TIMESTAMP | DATE       | Safe (truncates time)                                   |
| INTEGER   | DECIMAL    | Safe conversion                                         |

### Migration Steps

#### Step 1: Create new table with changed type

```sql
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  converted_column INTEGER,  -- Changed from VARCHAR
  other_column TEXT
);
```

#### Step 2: Copy data with type casting

```sql
INSERT INTO target_table_new (id, converted_column, other_column)
SELECT id, CAST(converted_column AS INTEGER), other_column
FROM target_table;
```

**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))

---

## ALTER COLUMN SET/DROP NOT NULL Migration

**Goal:** Change a column's nullability constraint.

### Pre-Migration Validation (for SET NOT NULL)

```sql
SELECT COUNT(*) as null_count FROM target_table
WHERE target_column IS NULL;
-- MUST ABORT if null_count > 0, or plan to provide default values
```

### Migration Steps

#### Step 1: Create new table with changed constraint

```sql
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  target_column VARCHAR(255) NOT NULL,  -- Changed from nullable
  other_column TEXT
);
```

#### Step 2: Copy data (with default for NULLs if needed)

```sql
INSERT INTO target_table_new (id, target_column, other_column)
SELECT id, COALESCE(target_column, 'default_value'), other_column
FROM target_table;
```

**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))

---

## ALTER COLUMN SET/DROP DEFAULT Migration

**Goal:** Add or remove a default value for a column.

### Pre-Migration Validation

```sql
SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'target_table' ORDER BY ordinal_position;
-- Identify current column definition and any existing defaults
```

### Migration Steps (SET DEFAULT)

#### Step 1: Create new table with default value

```sql
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  status VARCHAR(50) DEFAULT 'pending',  -- Added default
  other_column TEXT
);
```

#### Step 2: Copy data

```sql
INSERT INTO target_table_new (id, status, other_column)
SELECT id, status, other_column
FROM target_table;
```

**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))

### Migration Steps (DROP DEFAULT)

#### Step 1: Create new table without default

```sql
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  status VARCHAR(50),  -- Removed DEFAULT
  other_column TEXT
);
```

#### Step 2: Copy data

```sql
INSERT INTO target_table_new (id, status, other_column)
SELECT id, status, other_column
FROM target_table;
```

**Step 3: Verify and swap** (see [Common Pattern](overview.md#common-verify--swap-pattern))