PostgreSQL11+删除列默认值后原有行仍保留默认值的原因是什么

你观察到的现象是PostgreSQL 11+ 处理带非空约束的新增字段的内部逻辑决定的，具体原因如下：

1. 新增带默认值非空字段的懒加载逻辑 #

你执行 ALTER TABLE foo ADD COLUMN bar integer NOT NULL DEFAULT 10; 时，PostgreSQL 为了避免大表全表重写导致的性能问题，不会立刻把默认值10写入所有存量行的磁盘存储中，只会做两个元数据修改：

• 给bar字段标记NOT NULL约束
• 将默认值10存入系统表pg_attribute对应bar字段记录的attmissingval属性中

此时你查询存量行时，PostgreSQL会自动对没有实际存储bar值的行，拼接attmissingval中存储的默认值返回，所以你查出来所有行的bar都是10，但此时这些值并没有真正写入磁盘的行数据里。

2. 删除默认值时的强制填充逻辑 #

当你执行ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;时，因为bar字段有NOT NULL约束，PostgreSQL不能在删除默认值、同时清空attmissingval属性后，让存量行查询时返回空值（违反非空约束），所以会在执行删除默认值的操作时，自动触发一次全表字段填充：
把所有还没有实际存储bar值的存量行，都将原来的默认值10真正写入磁盘的行存储中，之后再清空attmissingval、删除字段的默认值属性。

所以此时你再查询，返回的10已经是实际写入行内的持久化值，自然不会因为默认值被删除而变化。

补充验证：如果新增的是不带NOT NULL约束的字段，加默认值后再删除默认值，存量行不会触发填充，查询会返回NULL，和本场景的差异就是非空约束的强制要求导致的行为不同。

内容的提问来源于stack exchange，提问作者Artur Shyshko