ALTERing a sqlite table to add a timestamp column with default value

It isn't possible to ALTER table ADD column foo timestamp default CURRENT_TIMESTAMP in sqlite, but are there any clever workarounds?

Solution

SQLite does not actually modify the table contents when adding a column, so the default must be a value that does not require reading the table.

To work around this, you could use a trigger instead of a default value:

Add the column without the default value:

ALTER TABLE MyTable ADD COLUMN foo timestamp DEFAULT NULL;

Use a trigger to set the default value:

ADD TRIGGER MyTable_foo_default
AFTER INSERT ON MyTable
FOR EACH ROW
WHEN NEW.foo IS NULL
BEGIN
    UPDATE MyTable
    SET foo = CURRENT_TIMESTAMP
    WHERE rowid = NEW.rowid;
END;

Alternatively, modify the table contents first so that all rows have a value, then set the default value:

Add the column without the default value:

ALTER TABLE MyTable ADD COLUMN foo timestamp DEFAULT NULL /* replace me */;

Set the column value to something in all rows (the actual value does not matter, the important thing is that all rows now have the new column):
```
UPDATE MyTable SET foo = CURRENT_TIMESTAMP;
```

Change the default value (documentation: PRAGMA writable_schema, sqlite_master):

PRAGMA writable_schema = on;

UPDATE sqlite_master
SET sql = replace(sql, 'DEFAULT NULL /* replace me */',
                       'DEFAULT CURRENT_TIMESTAMP')
WHERE type = 'table'
  AND name = 'MyTable';

PRAGMA writable_schema = off;

Reopen the database (otherwise, SQLite won't know about the new default value).