ALTERING sqlite table to add timestamp column with default value

SQLite doesn't actually modify the contents of the table when adding a column, so the default should be a value that doesn't require reading the table.

To get around this, you can use a trigger instead of the default:

• Add a column with no default value:

  ALTER TABLE MyTable ADD COLUMN foo timestamp DEFAULT NULL;
  
        
  
          
          
          
        
  
      

• Use a trigger to set the default:

  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, first change the contents of the table so that all rows have a value and then set the default:

• Add a column with no default value:

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

• Set the column value in all rows (the actual value doesn't matter, it is important that all rows now have a new column):

  UPDATE MyTable SET foo = CURRENT_TIMESTAMP;
  
        
  
          
          
          
        
  
      

• Change the default (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 be aware of the new default).