This question is out of curiosity, just to learn more about the internals of
PostgreSQL.
The goal was to add a not null bool column filled with "false", but with
"true" as the default for new rows.
The naïve approach would be:
ALTER TABLE foo ADD COLUMN slow bool NOT NULL DEFAULT true;
UPDATE foo SET slow = false;
This takes a certain, non-negligible amount of time.
This on the other hand achieves the same result and is almost instant:
ALTER TABLE foo ADD COLUMN fast bool NOT NULL DEFAULT false;
ALTER TABLE foo ALTER COLUMN fast SET DEFAULT true;
Where does the difference come from, how are those handled internally?
Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=56595e8ee397a5bc48b84277da3133
a9
