Schema Evolution

Add Columns

Backfill Existing Rows

A migration can add a nullable column first, then fill existing rows with a deterministic backfill query.

Program

Play the script to choose the backfilled status for rows that existed before the new column.

default_status
add_column_backfill.sql
Replay: real traced execution (multi-file project)
CREATE TABLE migration_choice AS WITH params(default_status) AS (VALUES ('active')) SELECT default_status FROM params;
CREATE TABLE accounts (id INTEGER, name TEXT);
INSERT INTO accounts VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mira');
ALTER TABLE accounts ADD COLUMN status TEXT;
UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;
SELECT id, name, status FROM accounts ORDER BY id;

CREATE TABLE migration_choice AS WITH params(default_status) AS (VALUES ('pending')) SELECT default_status FROM params;
CREATE TABLE accounts (id INTEGER, name TEXT);
INSERT INTO accounts VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mira');
ALTER TABLE accounts ADD COLUMN status TEXT;
UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;
SELECT id, name, status FROM accounts ORDER BY id;

  1. choice ← 1 row

    1CREATE TABLE migration_choice AS WITH params(default_status) AS (VALUES ('active')) SELECT default_status FROM params;2CREATE TABLE accounts (id INTEGER, name TEXT);
    values this step1 rowchoice

  2. accounts ← 3 rows

    2CREATE TABLE accounts (id INTEGER, name TEXT);3INSERT INTO accounts VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mira');4ALTER TABLE accounts ADD COLUMN status TEXT;
    values this step3 rowsaccounts

  3. columns ← 3 rows

    3INSERT INTO accounts VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mira');4ALTER TABLE accounts ADD COLUMN status TEXT;5UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;
    values this step3 rowscolumns

  4. accounts ← 3 rows

    4ALTER TABLE accounts ADD COLUMN status TEXT;5UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;6SELECT id, name, status FROM accounts ORDER BY id;
    values this step3 rowsaccounts

  5. result ← 3 rows

    5UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;6SELECT id, name, status FROM accounts ORDER BY id;
    values this step3 rowsresult

  1. choice ← 1 row

    1CREATE TABLE migration_choice AS WITH params(default_status) AS (VALUES ('pending')) SELECT default_status FROM params;2CREATE TABLE accounts (id INTEGER, name TEXT);
    values this step1 rowchoice

  2. accounts ← 3 rows

    2CREATE TABLE accounts (id INTEGER, name TEXT);3INSERT INTO accounts VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mira');4ALTER TABLE accounts ADD COLUMN status TEXT;
    values this step3 rowsaccounts

  3. columns ← 3 rows

    3INSERT INTO accounts VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mira');4ALTER TABLE accounts ADD COLUMN status TEXT;5UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;
    values this step3 rowscolumns

  4. accounts ← 3 rows

    4ALTER TABLE accounts ADD COLUMN status TEXT;5UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;6SELECT id, name, status FROM accounts ORDER BY id;
    values this step3 rowsaccounts

  5. result ← 3 rows

    5UPDATE accounts SET status = (SELECT default_status FROM migration_choice) WHERE status IS NULL;6SELECT id, name, status FROM accounts ORDER BY id;
    values this step3 rowsresult
add column `ALTER TABLE ... ADD COLUMN` changes the table shape without rewriting the example data.
backfill The `UPDATE` fills existing rows after the column exists.
migration value The selector changes the value used for the backfill.