问题描述

在开发应用的过程中，我们可能遇到业务需要增加一个字段、然后又决定删除该字段的情况。例如，

-- 初始化表
CREATE TABLE IF NOT EXISTS t(id int, age int);

-- 业务调整，增加size字段
ALTER TABLE t ADD COLUMN IF NOT EXISTS size int;

...
...
...

-- 业务调整，不需要size字段
ALTER TABLE t DROP COLUMN IF EXISTS size;

根据业务调整，我们首先增加了 size 字段，然后又删除了该字段，而且 DBA 并没有将 ADD COLUMN 和 DROP COLUMN 语句进行合并。

示例中的 IF NOT EXISTS 和 IF EXISTS 是为了支持部署脚本的重复执行。

随着代码在开发环境中的重复部署，脚本不停创建 size 字段并删除该字段，突然有一次部署返回了以下错误：

ERROR: tables can have at most 1600 columns

错误显示表 t 中的字段超过了 1600 个，但查看它的定义却只有 2 个字段。为什么会这样呢？

原因分析

数据库通常会存在一些限制，比如单个表中允许创建的字段数据。对于 PostgreSQL 而言，一个表中最多能创建 1600 个字段，而且还受进一步到具体数据类型的限制。

这个限制本身没有什么问题，因为绝大多数情况下我们不需要这么多的字段；但是 PostgreSQL 还有一个限制，那就是使用 ALTER TABLE DROP COLUMN 语句删除字段时，并不会真正物理删除字段，而是在内部标记为对 SQL 操作不可见。

此时，当我们对表中的数据进行操作时，PostgreSQL 会自动为这些被删除的自动设置 NULL 值。这种实现方法可以快速删除字段，但不会立即减少占用的磁盘空间。

关键在于，被删除的字段也会计算在字段数量限制中。这就导致了我们前面示例中的问题，表 t 看起来只有 2 个字段，但实际上它还包含了 1598 个隐藏字段。再次创建 size 字段时就返回了错误。

此时，要解决问题只能重建表 t，不然这个表就不能增加任何字段了。另外，为了预防出现类似问题，应该注释掉示例中的 ADD COLUMN 语句，因为后续业务变更明确不需要这个字段了。

额外福利

PostgreSQL 中的 ALTER TABLE DROP COLUMN 语句可以把表中的字段全部删除，剩下一个不包含任何字段的空表。例如：

CREATE TABLE t(id int);
ALTER TABLE t DROP COLUMN id;

SELECT * FROM t;

查询没有返回结果，但是也不会报错，表 t 是一个包含零个字段的表！😎

你觉得它是怎么实现的呢？

ITPub博客

PostgreSQL删除字段的一个小问题

问题描述

原因分析

额外福利

最新文章