Schema Evolution

ALTER TABLE, CAST, and transactional migrations

Tutorial 5 of 5

This tutorial starts with a simple v1 schema and evolves it through several migrations: adding columns, renaming fields, changing types with CAST/::, and wrapping everything in transactions. This is the “my schema needs to change” workflow.

1. The v1 schema

A minimal user table to start with.

CREATE TABLE users (
    id     SERIAL PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT NOT NULL UNIQUE,
    active INT DEFAULT 1
);

INSERT INTO users (name, email, active) VALUES
    ('Alice',  'alice@example.com',  1),
    ('Bob',    'bob@example.com',    1),
    ('Carol',  'carol@example.com',  0),
    ('Dave',   'dave@example.com',   1);
SELECT * FROM users
ORDER BY id;

Result

idnameemailactive
1Alicealice@example.com1
2Bobbob@example.com1
3Carolcarol@example.com0
4Davedave@example.com1

2. Migration: add a column

Add a created_at column. Existing rows get NULL since there is no default. 

ALTER TABLE users ADD COLUMN created_at DATE;
SELECT id, name, created_at
FROM users ORDER BY id;

Result

idnamecreated_at
1AliceNULL
2BobNULL
3CarolNULL
4DaveNULL

Backfill the new column with a default date.

UPDATE users SET created_at = '2025-01-01';

3. Migration: rename a column

Rename name to display_name for clarity.

ALTER TABLE users RENAME COLUMN name TO display_name;
SELECT id, display_name, email
FROM users ORDER BY id;

Result

iddisplay_nameemail
1Alicealice@example.com
2Bobbob@example.com
3Carolcarol@example.com
4Davedave@example.com

4. Migration: change a column type

The active column is an INT but should be BOOLEAN. Use ALTER COLUMN TYPE to convert it. 

ALTER TABLE users ALTER COLUMN active TYPE BOOLEAN;
SELECT id, display_name,
       active
FROM users ORDER BY id;

Result

iddisplay_nameactive
1Alicetrue
2Bobtrue
3Carolfalse
4Davetrue

5. Using CAST and :: in queries

The CAST() function and the :: postfix operator both convert between types. They are interchangeable. 

SELECT display_name,
       active::INT AS active_int,
       CAST(id AS TEXT)
         || '-' || display_name
         AS code
FROM users
ORDER BY id;

Result

display_nameactive_intcode
Alice11-Alice
Bob12-Bob
Carol03-Carol
Dave14-Dave

6. Transactional migration

Wrap a multi-step migration in a transaction. If any step fails, all changes are rolled back. 

BEGIN;

ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';

UPDATE users SET role = 'admin'
WHERE display_name = 'Alice';

COMMIT;
SELECT display_name, role
FROM users ORDER BY id;

Result

display_namerole
Aliceadmin
Bobuser
Caroluser
Daveuser

7. Nested transactions

mskql supports nested BEGIN blocks. The inner transaction can be rolled back independently while the outer one continues. 

BEGIN;

UPDATE users SET role = 'moderator'
WHERE display_name = 'Bob';

BEGIN;
UPDATE users SET role = 'moderator'
WHERE display_name = 'Carol';
ROLLBACK;

COMMIT;
SELECT display_name, role
FROM users ORDER BY id;

Result

display_namerole
Aliceadmin
Bobmoderator
Caroluser
Daveuser

Bob’s role was updated in the outer transaction (committed). Carol’s update was in the inner transaction (rolled back), so she stays as “user”.

8. Drop a column

Remove the old active column now that we have role.

ALTER TABLE users DROP COLUMN active;
SELECT * FROM users
ORDER BY id;

Result

iddisplay_nameemailcreated_atrole
1Alicealice@example.com2025-01-01admin
2Bobbob@example.com2025-01-01moderator
3Carolcarol@example.com2025-01-01user
4Davedave@example.com2025-01-01user

What you learned