Блог

Я пытаюсь написать хранимую процедуру, в которой я могу ввести строку, даже если одно из значений в ключе равно нулю. Я прочитал документацию и обнаружил, что Postgres не работает при сравнении равенства нулевых значений.

Я также прочитал другие сообщения на форуме и заметил, что то, чего я хочу достичь, можно сделать с помощью частичного индекса. Я могу успешно получить нарушение ограничения, однако мой «on conflict» никогда не попадает, когда я передаю значение с нулевым днем рождения.

Я хочу иметь возможность передавать нулевой день рождения и обновлять идентификатор для человека, даже если его день рождения равен нулю.

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

Я создаю индекс и частичный индекс, чтобы он позволял принимать значение birthday равным null

 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')
  

я успешно вижу, что единственной строкой в базе данных является Bob с идентификатором 2.
однако, если я запускаю

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

единственная строка, которую я получаю, — это ID 3. вторая вставка для ID 4 никогда не обновляет существующую строку. Я получаю сообщение об ошибке нарушения, но обновление «при конфликте» никогда не выполняется.

может ли кто-нибудь указать мне правильное направление, как это сделать?

CONFLICT Не соответствует, потому что NULL не равно NULL . Это не PostgreSQL, это определено в стандарте SQL.

Используйте что-то вроде COALESCE(birthday, '0001-01-01') при вставке ваших данных, которые будут соответствовать; и удалите частичный индекс.

В вашем коде ошибка, в DO UPDATE...WHERE : ничего не названо _date , должно быть _birthday .