If I specify a default when adding a non-null column to a table ...
ALTER TABLE foo
ADD COLUMN bar INT DEFAULT 42 NOT NULL
... does the bar column continue to have a default value? Or is the default only used while adding the column, to specify values in existing rows?
The documentation says:
The column-def rule defines the characteristics of the new column.
So the default value applies to the column, not to the operation:
sqlite> CREATE TABLE foo(x);
sqlite> ALTER TABLE foo ADD COLUMN bar INT DEFAULT 42 NOT NULL;
sqlite> INSERT INTO foo(x) VALUES(1);
sqlite> SELECT * FROM foo;
1|42
