PostgreSQL批量插入IDENTITY列使用DEFAULT失败原因咨询

你遇到的是PostgreSQL 11~13版本中关于GENERATED ALWAYS AS IDENTITY列的已知官方bug，并非操作逻辑错误。

具体根因 #

• GENERATED ALWAYS AS IDENTITY约束的设计逻辑是：禁止用户手动为该列赋值，仅允许两种合法赋值场景：插入语句不写该列、插入时显式指定该列值为DEFAULT，两种场景均由系统自动生成序列值。
• 旧版本的查询解析器存在逻辑漏洞：处理多行INSERT的VALUES列表时，错误将多个连续的DEFAULT关键字识别为用户主动传入的自定义值，触发了GENERATED ALWAYS的约束校验，导致执行报错。单条INSERT的解析逻辑不存在该漏洞，因此可以正常执行。
• 省略id列的批量插入可以正常执行的原因是：该场景下解析器会自动为所有未显式指定的列补充DEFAULT值，不会触发上述解析漏洞，因此可以正常执行。

解决方案 #

1. 临时兼容方案：保持现有省略IDENTITY列的批量插入写法即可，无需其他修改，不会影响数据正确性。
2. 永久修复方案：升级PostgreSQL版本到14及以上，该版本已完成该bug的官方修复。
3. 若需要继续使用11版本，可以关注官方后续的小版本更新，确认是否将该修复向后移植到对应稳定分支。

内容的提问来源于stack exchange，提问作者Mr.P