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.

add_column_backfill.sql 
CREATE TABLE migration_choice AS WITH params(default_status) AS (VALUES ()) 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 ()) 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;
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.