A migration, start to finish in SQL

Changing a live table is four small statements, each of which can be re-run.

BEGIN;

ALTER TABLE customers
ADD COLUMN plan_tier INTEGER NOT NULL DEFAULT 0;

UPDATE customers
SET plan_tier = (
        CASE plan
            WHEN 'basic' THEN 1
            WHEN 'pro' THEN 2
            WHEN 'enterprise' THEN 3
            ELSE 0
        END
    );

CREATE INDEX customers_by_tier
    ON customers (plan_tier);

SELECT
    plan,
    plan_tier,
    COUNT(*) AS customers
FROM customers
GROUP BY plan, plan_tier
ORDER BY plan_tier;

COMMIT;

How it works

1. Add the column first, so old code keeps working while new code fills it.
2. Backfill in one UPDATE, then add the constraint the data now satisfies.
3. Wrap the whole thing in a transaction: half a migration is worse than none.

Keywords and builtins used here

• ADD
• ALTER
• AS
• BEGIN
• BY
• CASE
• COLUMN
• COMMIT
• COUNT
• CREATE
• DEFAULT
• ELSE
• END
• FROM
• GROUP
• INDEX
• INTEGER
• NOT
• NULL
• ON
• ORDER
• SELECT
• SET
• TABLE
• THEN
• UPDATE
• WHEN

The run, in numbers

Lines

27

Characters to type

375

Tokens

72

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 45 seconds.

Type this snippet

Step 2 of 2 in Changing a schema, step 14 of 16 in Schema & constraints.

← Previous Next →