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

Question

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

Answer 1

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).