PostgreSQL中能否用ON CONFLICT DO NOTHING规避非空约束冲突？

嘿，这个误区其实挺普遍的——很多人会误以为ON CONFLICT DO NOTHING能处理所有插入错误，但实际上它的能力是有明确范围的！

为什么ON CONFLICT DO NOTHING管不了非空约束？ #

ON CONFLICT子句的设计目标是处理唯一约束（包括主键）或唯一索引的冲突场景（比如主键重复、唯一键重复）。而非空约束是列级的强制校验，当你尝试插入一个非空字段为NULL的行时，PostgreSQL会在执行冲突检测之前就直接抛出错误——这时候ON CONFLICT根本没机会生效。

那怎么实现“违反非空约束时忽略该行”？ #

这里有几个实用的方案，根据你的场景选就行：

1. 插入前过滤不符合要求的行（最推荐）

不管你是从外部数据源插入，还是用VALUES子句批量插入，都可以通过INSERT ... SELECT的方式，在SELECT环节就把缺少非空字段的记录筛掉。

举个例子，假设你的表users有非空字段name，要插入一批数据：

-- 从子查询/外部表插入时过滤
INSERT INTO users (id, name)
SELECT id, name
FROM your_source_table
WHERE name IS NOT NULL; -- 直接排除name为空的行

-- 如果是VALUES子句批量插入，转成子查询再过滤
INSERT INTO users (id, name)
SELECT id, name
FROM (
  VALUES
    (1, 'Alice'),
    (2, NULL), -- 这条会被过滤掉
    (3, 'Bob')
) AS temp_data (id, name)
WHERE name IS NOT NULL;

这种方式性能最优，因为是批量处理，没有额外的异常捕获开销。

2. 用PL/pgSQL捕获异常跳过错误行（适合复杂场景）

如果你的插入逻辑比较复杂，没办法提前过滤（比如动态生成的数据），可以写一个PL/pgSQL函数，逐行插入并捕获NOT_NULL_VIOLATION异常，跳过错误的行：

CREATE OR REPLACE FUNCTION safe_insert_users(data jsonb)
RETURNS void AS $$
DECLARE
  rec record;
BEGIN
  -- 把传入的JSON数组转成记录集循环处理
  FOR rec IN SELECT * FROM jsonb_to_recordset(data) AS x(id INT, name TEXT) LOOP
    BEGIN
      INSERT INTO users (id, name) VALUES (rec.id, rec.name);
    EXCEPTION WHEN NOT_NULL_VIOLATION THEN
      -- 遇到非空约束错误时，直接跳过当前行
      CONTINUE;
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

调用的时候把数据转成JSONB传入就行：

SELECT safe_insert_users('[
  {"id":1, "name":"Alice"},
  {"id":2, "name":null},
  {"id":3, "name":"Bob"}
]'::jsonb);

这种方法的缺点是逐行处理，批量插入时性能不如过滤法，但胜在灵活。

额外提醒 #

如果你的业务场景其实允许某些字段为空，只是之前误加了非空约束，那直接修改表结构去掉非空约束可能是更彻底的解决办法——但如果确实需要保留约束、只跳过错误行，上面的两种方法就足够用了。

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