postgresql 基于具有NULL值的UNIQUE约束的UPSERT

8hhllhi2  于 2023-02-12  发布在  PostgreSQL
关注(0)|答案(2)|浏览(202)

我有一个Postgres表,它对多个列有唯一约束,其中一个列可以是NULL。我只想在每个组合的该列中允许一个记录为NULL。

create table my_table (
   col1 int generated by default as identity primary key,
   col2 int not null,
   col3 real,
   col4 int,
   constraint ux_my_table_unique unique (col2, col3)
);

我有一个upsert查询,当它遇到在col2和col3中具有相同值的记录时,我想更新col4:

insert into my_table (col2, col3, col4) values (p_col2, p_col3, p_col4)
on conflict (col2, col3) do update set col4=excluded.col4;

但是当col3为NULL时冲突不会触发。我读过关于使用触发器的文章。请问触发冲突的最佳解决方案是什么?

ltqd579y

ltqd579y1#

波斯格雷斯15

...添加子句NULLS NOT DISTINCT。您的案例现在可以开箱即用:

ALTER TABLE my_table
  DROP CONSTRAINT IF EXISTS ux_my_table_unique
, ADD CONSTRAINT ux_my_table_unique UNIQUE NULLS NOT DISTINCT (col2, col3);

INSERT INTO my_table (col2, col3, col4)
VALUES (p_col2, p_col3, p_col4)
ON     CONFLICT (col2, col3) DO UPDATE
SET    col4 = EXCLUDED.col4;

参见:

  • 创建具有空列的唯一约束

14岁或以上的Postgres

NULL值被视为彼此不相等,因此永远不会触发UNIQUE违规。这意味着当前表定义未执行您要求它执行的操作。可能已经存在多个具有(col2, col3) = (1, NULL)的行。在当前设置中,ON CONFLICT永远不会为col3 IS NULL触发。
您可以使用两个部分UNIQUE索引来强制UNIQUE约束,如下所述:

  • 创建具有空列的唯一约束

适用于您的案例:

CREATE UNIQUE INDEX my_table_col2_uni_idx ON my_table (col2)
WHERE col3 IS NULL;

CREATE UNIQUE INDEX my_table_col2_col3_uni_idx ON my_table (col2, col3)
WHERE col3 IS NOT NULL;
    • 但是**ON CONFLICT ... DO UPDATE只能基于单个UNIQUE索引或约束。只有ON CONFLICT DO NOTHING变体才能用作"捕获所有"。请参见:
  • 如何在PostgreSQL中使用RETURNING和ON CONFLICT?

它会 * 似乎 * 像你想要的是目前不可能的,但有一个...

完美的解决方案

有了两个部分UNIQUE索引,就可以根据col3的输入值使用正确的语句:

WITH input(col2, col3, col4) AS (
   VALUES
     (3, NULL::real, 5)  -- ①
   , (3, 4, 5)
   )
, upsert1 AS (
   INSERT INTO my_table AS t(col2, col3, col4)
   SELECT * FROM input          WHERE col3 IS NOT NULL
   ON     CONFLICT (col2, col3) WHERE col3 IS NOT NULL  -- matching index_predicate!
   DO     UPDATE
   SET    col4 = EXCLUDED.col4
   WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4  -- ②
   )
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input    WHERE col3 IS NULL
ON     CONFLICT (col2) WHERE col3 IS NULL  -- matching index_predicate!
DO     UPDATE SET col4 = EXCLUDED.col4
WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4;  -- ②
  • db〈〉小提琴here *

在任何情况下都有效。
even适用于col3NULLNOT NULL值任意混合的多个输入行。
而且甚至不会比普通语句花费更多,因为每行只进入两个UPSERT中的一个。
这是那些"Eurika!"查询中的一个,所有的东西都会点击,排除万难。:)
①注意CTE input中的::real的显式强制转换。以下相关答案解释了原因:

  • 更新多行时转换NULL类型

②最后的WHERE子句是可选的,但强烈推荐。如果它实际上没有改变任何东西,那么使用UPDATE将是一种浪费。请参见:

  • 如何(或可以)对多列执行SELECT DISTINCT操作?
de90aj5v

de90aj5v2#

如果你能找到一个永远不可能合法存在于col3中的值(确保有一个check约束),你可以使用一个唯一索引:

CREATE UNIQUE INDEX ON my_table (
   col2,
   coalesce(col3, -1.0)
);

并在INSERT中使用它:

INSERT INTO my_table (col2, col3, col4)
VALUES (p_col2, p_col3, p_col4)
ON CONFLICT (col2, coalesce(col3, -1.0))
DO UPDATE SET col4 = excluded.col4;

相关问题