ALTER TABLE — What You Can and Cannot Change

~12 min · schema, alter-table, migrations

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Half a feature, used carefully

SQLite's ALTER TABLE is intentionally limited compared to Postgres. The supported operations:

ALTER TABLE t RENAME TO new_name
ALTER TABLE t RENAME COLUMN old_name TO new_name (3.25+)
ALTER TABLE t ADD COLUMN c TYPE [constraints]
ALTER TABLE t DROP COLUMN c (3.35+)

That's it. You cannot change a column's type, you cannot add a constraint to an existing column, you cannot reorder columns. For those, the canonical pattern is the CTAS dance: create a new table with the new shape, copy the data over, drop the old, rename.

Warning: ADD COLUMN with a default expression that is non-constant (e.g., DEFAULT (random())) is rejected. The default must be a literal or a constant expression. Workaround: ADD with NULL default, then UPDATE to populate.

Code

Simple ALTERs that just work·sql

ALTER TABLE conversations RENAME COLUMN title TO display_title;
ALTER TABLE conversations ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0;
ALTER TABLE conversations DROP COLUMN deprecated_field;

The CTAS dance — change a column type safely·sql

BEGIN;

CREATE TABLE conversations_new (
  id          INTEGER PRIMARY KEY,
  display_title TEXT NOT NULL,
  brain         TEXT NOT NULL DEFAULT 'claude',
  pinned        INTEGER NOT NULL DEFAULT 0,
  created_at    INTEGER NOT NULL,            -- changed: TEXT -> INTEGER (epoch)
  updated_at    INTEGER NOT NULL
) STRICT;

INSERT INTO conversations_new
SELECT id, display_title, brain, pinned,
       unixepoch(created_at), unixepoch(updated_at)
FROM   conversations;

DROP TABLE conversations;
ALTER TABLE conversations_new RENAME TO conversations;

COMMIT;

External links

ALTER TABLE

Exercise

Take the messages table you've been building and execute three ALTERs: rename a column, add a new optional column, drop an unused one. Then practice the CTAS dance to change one column's type from TEXT to INTEGER. Confirm the data survived with row-count and sample comparisons before and after.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousINSERT — All the Shapes Next →Dates, Booleans, and Practical Types

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.