我有一个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时冲突不会触发。我读过关于使用触发器的文章。请问触发冲突的最佳解决方案是什么?
2条答案
按热度按时间ltqd579y1#
波斯格雷斯15
...添加子句
NULLS NOT DISTINCT
。您的案例现在可以开箱即用:参见:
14岁或以上的Postgres
NULL
值被视为彼此不相等,因此永远不会触发UNIQUE
违规。这意味着当前表定义未执行您要求它执行的操作。可能已经存在多个具有(col2, col3) = (1, NULL)
的行。在当前设置中,ON CONFLICT
永远不会为col3 IS NULL
触发。您可以使用两个部分
UNIQUE
索引来强制UNIQUE
约束,如下所述:适用于您的案例:
ON CONFLICT ... DO UPDATE
只能基于单个UNIQUE
索引或约束。只有ON CONFLICT DO NOTHING
变体才能用作"捕获所有"。请参见:它会 * 似乎 * 像你想要的是目前不可能的,但有一个...
完美的解决方案
有了两个部分
UNIQUE
索引,就可以根据col3
的输入值使用正确的语句:在任何情况下都有效。
even适用于
col3
的NULL
和NOT NULL
值任意混合的多个输入行。而且甚至不会比普通语句花费更多,因为每行只进入两个UPSERT中的一个。
这是那些"Eurika!"查询中的一个,所有的东西都会点击,排除万难。:)
①注意CTE
input
中的::real
的显式强制转换。以下相关答案解释了原因:②最后的
WHERE
子句是可选的,但强烈推荐。如果它实际上没有改变任何东西,那么使用UPDATE
将是一种浪费。请参见:de90aj5v2#
如果你能找到一个永远不可能合法存在于
col3
中的值(确保有一个check约束),你可以使用一个唯一索引:并在
INSERT
中使用它: