PostgreSQL如何正确新增UUID类型表字段

需要在已有的PostgreSQL数据表中新增一个UUID类型字段，最初编写的SQL语句误用了修改列类型的语法：

ALTER TABLE core.example add COLUMN newcolumn SET DATA TYPE UUID USING (uuid_generate_v4())

执行后返回报错：

ERROR:  type modifier is not allowed for type "uuid"
LINE 1: ALTER TABLE core.example add COLUMN newsi UUID  (uuid_genera...

实际需求为创建全新列，而非修改已有列的数据类型。

编写的SQL混淆了「新增列」和「修改已有列数据类型」的两种语法：

• SET DATA TYPE、USING 是修改已有列类型时专用的子句，不适用于新增列场景
• UUID类型本身不支持传入类型修饰参数，语法解析时会把后面括号里的内容错当成类型修饰符，最终触发报错

如果你的PostgreSQL版本低于13，需要先启用UUID生成函数的扩展（13及以上高版本可跳过这步，直接用内置生成函数）：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

场景1：新增可空的UUID列，自动填充默认UUID #

执行以下语句即可，新增列时设置默认值后，数据库会自动给表现有所有行填充随机生成的UUID，后续新插入行如果不指定该列值，也会自动生成UUID：

-- PostgreSQL 12及更低版本，使用uuid-ossp扩展提供的生成函数
ALTER TABLE core.example ADD COLUMN newcolumn UUID DEFAULT uuid_generate_v4();

-- PostgreSQL 13及以上版本，直接使用内置的gen_random_uuid()，无需额外安装扩展
ALTER TABLE core.example ADD COLUMN newcolumn UUID DEFAULT gen_random_uuid();

场景2：新增非空的UUID列 #

先执行上面新增列+默认值的语句，因为默认值会自动给已有行填充对应值，不会出现空值，之后再追加非空约束即可：

ALTER TABLE core.example ALTER COLUMN newcolumn SET NOT NULL;

• 新增列时不要使用SET DATA TYPE/USING语法，这部分语法仅在修改已存在列的类型时使用，USING的作用是指定旧列值到新类型的转换规则，和新增列场景完全无关
• 给新增列设置DEFAULT值后，PostgreSQL执行DDL时会自动完成全表现有行的默认值填充，不需要额外手写UPDATE语句更新数据

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