基于空值唯一约束的UPSERT [英] UPSERT based on UNIQUE constraint with NULL values

问题描述

我有一个Postgres表，该表对多个列具有唯一约束，其中一列可以为空。对于每种组合，我只想允许该列中有一条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查询，当COL4在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;

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

推荐答案

如果您可以在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;

这篇关于基于空值唯一约束的UPSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！