Как использовать ключ агрегации с нулевым значением и с UPSERT?

Единственный способ, который я вижу, - это использовать триггер, чтобы столбцы практически не допускали значения NULL, формально оставаясь обнуляемыми.

Таблица испытаний:

create table test
(
    id serial not null,
    stamp timestamp without time zone,
    foo_id integer,
    bar_id integer,
    another_column integer,
    constraint id_pk primary key (id),
    constraint test_agg_key_unique unique (stamp, foo_id, bar_id)
);

Курок:

create or replace function before_insert_on_test()
returns trigger language plpgsql as $$
begin
    new.stamp:= coalesce(new.stamp, '1980-01-01 01:01:01');
    new.foo_id:= coalesce(new.foo_id, -1);
    new.bar_id:= coalesce(new.bar_id, -1);
    return new;
end $$;

create trigger before_insert_on_test
before insert on test
for each row
execute procedure before_insert_on_test();

Дополнительный уникальный индекс не требуется:

insert into test values (default, null, 1, null, 0)
on conflict (stamp, foo_id, bar_id) do
    update set another_column = test.another_column+ 1
returning *;

 id |        stamp        | foo_id | bar_id | another_column 
----+---------------------+--------+--------+----------------
  1 | 1980-01-01 01:01:01 |      1 |     -1 |              0

insert into test values (default, null, 1, null, 0)
on conflict (stamp, foo_id, bar_id) do
    update set another_column = test.another_column+ 1
returning *;

 id |        stamp        | foo_id | bar_id | another_column 
----+---------------------+--------+--------+----------------
  1 | 1980-01-01 01:01:01 |      1 |     -1 |              1

Обратите внимание, что предложение where не требуется, поскольку update относится только к строке с конфликтом.

Обновление: альтернативное решение

Проблема проистекает из того факта, что составной уникальный индекс, содержащий элементы, допускающие значение NULL, обычно не является хорошей идеей. Вам следует отказаться от этого подхода и противостоять всей логике триггера.

Удалите уникальные индексы и создайте триггер:

create or replace function before_insert_on_test()
returns trigger language plpgsql as $$
declare
    found_id integer;
begin
    select id
    from test
    where
        coalesce(stamp, '1980-01-01 01:01:01') = coalesce(new.stamp, '1980-01-01 01:01:01')
        and coalesce(foo_id, -1) = coalesce(new.foo_id, -1)
        and coalesce(bar_id, -1) = coalesce(new.bar_id, -1)
    into found_id;
    if found then
        update test
        set another_column = another_column+ 1
        where id = found_id;
        return null;  -- abandon insert
    end if;
    return new;
end $$; 

create trigger before_insert_on_test
before insert on test
for each row
execute procedure before_insert_on_test();

Используйте просто insert без on conflict.

Вы можете попытаться ускорить срабатывание триггера с помощью (не уникального) индекса:

create index on test(coalesce(stamp, '1980-01-01 01:01:01'), coalesce(foo_id, -1), coalesce(bar_id, -1));

Прочитав этот вопрос: здесь, я нашел решение.

Спасибо, Эрвин Брандштеттер: https://dba.stackexchange.com/a/151438/107395

Решение :

Поэтому мне нужно создать индекс со всеми ключами и добавить COALESCE для каждого столбца, который может быть нулевым.

Итак, если это текст COALESCE(test_field, '') или если это число COALESCE(test_field, -1)

CREATE UNIQUE INDEX test_upsert_solution_idx
    ON test_upsert (name, status, COALESCE(test_field, ''), COALESCE(test_field2, '')...);

и в UPSERT удалите WHERE в DO UPDATE также добавьте COALESCE в ON CONFLICT:

INSERT INTO test_upsert as tu(name, status, test_field, identifier, count) 
VALUES ('test', 1, null, 'ident', 11)
ON CONFLICT (name, status, COALESCE(test_field, '')) 
 DO UPDATE  -- match expr. index
  SET count = COALESCE(tu.count + EXCLUDED.count, EXCLUDED.count, tu.count);

обработайте столбец с нулевым значением функцией isnull и присвойте им значение по умолчанию, например:

INSERT INTO public.test as t (id, stamp, foo_id, bar_id, ...)
VALUES (RANDOM_ID, '2016-01-01 01:01:01', 1, 1, ...)
ON CONFLICT (stamp, foo_id, bar_id, ...)
do update set another_column = isnull(t.another_column,0)  + 1
where t.stamp = '2016-01-01 01:01:01' and t.foo_id = 1 and t.bar_id= 1 and ...;