Schema Evolution

Rebuild Tables

Copy into a New Shape

Some schema changes are easiest to model as creating a new table, copying accepted rows, and renaming it into place.

Program

Play the script to choose the minimum total copied into the replacement table.

min_total
rebuild_table_copy.sql
Replay: real traced execution (multi-file project)
CREATE TABLE migration_choice AS WITH params(min_total) AS (VALUES (0)) SELECT min_total FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);
INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
SELECT id, customer, total, archived FROM orders ORDER BY id;

CREATE TABLE migration_choice AS WITH params(min_total) AS (VALUES (50)) SELECT min_total FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);
INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
SELECT id, customer, total, archived FROM orders ORDER BY id;

  1. choice ← 1 row

    1CREATE TABLE migration_choice AS WITH params(min_total) AS (VALUES (0)) SELECT min_total FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);
    values this step1 rowchoice

  2. orders ← 3 rows

    2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);3INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);4CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);
    values this step3 rowsorders

  3. new_columns ← 4 rows

    3INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);4CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);5INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);
    values this step4 rowsnew_columns

  4. orders_new ← 3 rows

    4CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);5INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);6DROP TABLE orders;
    values this step3 rowsorders_new

  5. columns ← 4 rows

    6DROP TABLE orders;7ALTER TABLE orders_new RENAME TO orders;8SELECT id, customer, total, archived FROM orders ORDER BY id;
    values this step4 rowscolumns

  6. result ← 3 rows

    7ALTER TABLE orders_new RENAME TO orders;8SELECT id, customer, total, archived FROM orders ORDER BY id;
    values this step3 rowsresult

  1. choice ← 1 row

    1CREATE TABLE migration_choice AS WITH params(min_total) AS (VALUES (50)) SELECT min_total FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);
    values this step1 rowchoice

  2. orders ← 3 rows

    2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);3INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);4CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);
    values this step3 rowsorders

  3. new_columns ← 4 rows

    3INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);4CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);5INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);
    values this step4 rowsnew_columns

  4. orders_new ← 1 row

    4CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);5INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);6DROP TABLE orders;
    values this step1 roworders_new

  5. columns ← 4 rows

    6DROP TABLE orders;7ALTER TABLE orders_new RENAME TO orders;8SELECT id, customer, total, archived FROM orders ORDER BY id;
    values this step4 rowscolumns

  6. result ← 1 row

    7ALTER TABLE orders_new RENAME TO orders;8SELECT id, customer, total, archived FROM orders ORDER BY id;
    values this step1 rowresult
replacement table The new table carries the desired primary key, check constraint, and added column.
copy step `INSERT INTO ... SELECT` controls which old rows move into the replacement shape.
rename into place Renaming the replacement table gives callers the original table name again.