如何在PostgreSQL中用INSERT INTO SELECT搭配DEFAULT生成主键

直接解决方案 #

在INSERT...SELECT语法中，你可以在SELECT子句里使用DEFAULT关键字来引用目标表列的默认值，只需在INSERT的列列表中明确包含主键列即可。针对你的场景，修正后的SQL语句如下：

INSERT INTO target (id, name)
SELECT DISTINCT DEFAULT, field_name
FROM source
WHERE source.field_name IS NOT NULL
RETURNING *;

原理说明 #

你的原语句未指定id列，PostgreSQL会尝试插入NULL，但target.id是非空主键，因此触发NotNullViolation错误。通过在INSERT中明确指定id和name列，并在SELECT中用DEFAULT作为id的值，PostgreSQL会自动调用目标表id列定义的默认生成逻辑（即uuid.uuid4()对应的PostgreSQL函数），为每条新记录生成合法的UUID主键。

替代方案（直接调用UUID生成函数） #

如果你需要更直观地控制UUID生成逻辑，也可以直接调用PostgreSQL的uuid_generate_v4()函数（需确保已启用uuid-ossp扩展）：

-- 先确保扩展已启用（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 执行插入
INSERT INTO target (id, name)
SELECT DISTINCT uuid_generate_v4(), field_name
FROM source
WHERE source.field_name IS NOT NULL
RETURNING *;

这种方式和使用DEFAULT效果一致，但DEFAULT更贴合表的原始设计，无需额外依赖扩展的启用状态（只要表已定义默认值即可）。

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