How does postgres 11+ maintain default value after the default is dropped?

user_11014993 0
Viewed 295

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?

postgresql-11 postgresql
2 Answers

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

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.

user_2561960
Related