Given the following commands:
create table foo(id serial primary key);
insert into foo values (1), (2), (3), (4);
alter table foo add column bar integer not null default 10;
alter table foo alter column bar drop default;
When I execute select * from foo; I get:
id | bar
----+------
1 | 10
2 | 10
3 | 10
4 | 10
How is this possible if starting with postgresql 11+, it evaluates not null defaults lazily?
Column defaults are stored in the system catalog pg_attrdef, while column definitions are stored in pg_attribute. If you drop the default value, the pg_attrdef row is deleted, but the pg_attribute row isn't.
The secret behind what you observe is that the “missing attribute value” is stored in pg_attribute, so it is not affected:
SELECT attmissingval
FROM pg_attribute
WHERE attrelid = 'foo'::regclass
AND attname = 'bar';
attmissingval
═══════════════
{10}
(1 row)
Breaking down each alter statement:
alter table foo add column bar integer not null default 10
This adds the new column, and due to not null default 10, each row is given 10 for the column value.
At this point, all existing rows have 10 and all future rows will be given 10 if a value is not provided on insert.
alter table foo alter column bar drop default
This does not affect existing rows. It only affects future rows being inserted, now requiring a value because the constraint not null remains, but the default does not.