时间戳

首页 > 解决方案 > 具有空约束值的 Postgres UPSERT 行不起作用

postgresql - 具有空约束值的 Postgres UPSERT 行不起作用

问题描述

我试图编写一个存储过程，即使键中的一个值为空，我也可以在其中插入一行。我已经阅读了文档，发现 Postgres 不适用于比较空值的相等性。

我还阅读了其他论坛帖子，并注意到我想要实现的目标可以通过部分索引来完成。我能够成功地违反约束，但是当我传入一个生日为空的值时，我的“冲突中”永远不会受到影响。

我希望能够传入一个空生日并更新一个人的 ID，即使他们的生日为空。

(
    id bigint not null,
    name text,
    birthday date
);

我创建了一个索引和部分索引，以便它允许生日为空

CREATE UNIQUE INDEX name_birthday_key on people (name, birthday);
CREATE UNIQUE INDEX birthday_null_key on people (name) where birthday is null;

create or replace procedure store_person(_identifier bigint, _name character varying, _birthday date)
    language plpgsql
as
$$
begin
   insert into people (
    id, name, birthday
    )
    values (
    _identifier, _name, _birthday
    )
    on conflict (name, birthday)
    do update
    set
        id = _identifier
        where people.birthday = _date and people.name = _name;
end
$$;

如果我运行：

call public.store_person(1, 'Bob', '1955-01-09')
call public.store_person(2, 'Bob', '1955-01-09')

我成功地看到数据库中唯一的行是 ID 为 2 的 Bob。但是，如果我运行

call public.store_person(3, 'Joe', null)
call public.store_person(4, 'Joe', null)

我得到的唯一行是 ID 3。 ID 4 的第二个插入永远不会更新现有行。我确实收到了违规错误，但从未命中“冲突”更新。

有人可以指出我如何做到这一点的正确方向吗？

标签： postgresqlconstraintsupsert

解决方案

CONFLICT不匹配，因为不NULL等于NULL。这不是 PostgreSQL 的东西，它是在 SQL 标准中定义的。

在插入数据时使用类似的东西COALESCE(birthday, '0001-01-01')，这将匹配；并删除部分索引。

您的代码有一个错误，在DO UPDATE...WHERE: there's nothing named _date, should be _birthday。

推荐阅读