PostgreSQL中UPDATE ON CONFLICT触发NOT NULL约束违反问题排查

问题背景 #

我有一张player表，DDL如下（仅修改了列名和顺序）：

create table if not exists player (
    id varchar primary key,
    col1 boolean not null default false,
    col2 json not null default '{}',
    col3 varchar not null,
    col4 varchar not null,
    col5 json not null default '{}',
    col6 boolean not null default false
);

执行以下UPSERT语句时（已存在相同id的行，预期执行UPDATE逻辑）：

insert into player(id, col1, col2)
values (val_id, val1, val2)
on conflict(id)
do update set col1=excluded.col1, col2=excluded.col2

PostgreSQL抛出错误：

ERROR: ... null value in column "col3" of relation "player" violates not-null constraint

确认执行前col3已有值，给col3添加默认值后错误消失，但我不需要默认值，请问原因是什么？

核心原因 #

PostgreSQL处理ON CONFLICT逻辑时，会先尝试构建完整的excluded行（对应INSERT操作要插入的行），这一步会严格检查所有字段的约束，包括NOT NULL。

你的INSERT语句只指定了id、col1、col2三个字段，对于没有默认值的col3、col4，PostgreSQL会自动将它们设为NULL来构建excluded行，这直接触发了NOT NULL约束，导致语句失败——哪怕最终实际执行的是UPDATE操作，这一步前置的约束检查也会先报错。

当你给col3添加默认值后，构建excluded行时会用默认值填充，不会出现NULL，约束检查通过，后续的UPDATE逻辑也能正常执行。

解决方案（无需添加默认值） #

不需要给非空字段加默认值的话，有两种可行方式：

• 方式一：在INSERT字段列表中包含所有非空字段，用原行的值填充
  修改INSERT语句，把col3、col4也加入字段列表，并用子查询获取原行对应的值：

  insert into player(id, col1, col2, col3, col4)
  values (val_id, val1, val2, (select col3 from player where id = val_id), (select col4 from player where id = val_id))
  on conflict(id)
  do update set col1=excluded.col1, col2=excluded.col2
  

  这样构建excluded行时，col3、col4会有合法值，不会触发约束，UPDATE时也只会修改col1和col2，其他字段保留原行数据。

• 方式二：在UPDATE中显式保留原字段值
  另一种思路是，在UPDATE子句中明确指定所有非空字段保留原行的值，适合字段较少的场景：

  insert into player(id, col1, col2)
  values (val_id, val1, val2)
  on conflict(id)
  do update set 
      col1=excluded.col1, 
      col2=excluded.col2,
      col3=player.col3,
      col4=player.col4,
      col5=player.col5,
      col6=player.col6
  

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