CODEWITHSUNDEEP
sqlitetimestampalter
user3791372
user3791372

Reputation: 4695

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?

Upvotes: 17

Views: 7074

Answers (1)

CL.
CL.

Reputation: 180270

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:

  1. Add the column without the default value:

    ALTER TABLE MyTable ADD COLUMN foo timestamp DEFAULT NULL;

  2. 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:

  1. Add the column without the default value:

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

  2. 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;

  3. 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;
  4. Reopen the database (otherwise, SQLite won't know about the new default value).

Upvotes: 26

Related Questions