Problem PostgreSQL UPSERT z wartościami NULL

Mam problem z użyciem nowej funkcji UPSERT w Postgres 9.5

Mam tabelę, która służy do agregowania danych z innej tabeli. Klucz złożony składa się z 20 kolumn, z których 10 można zerować. Poniżej stworzyłem mniejszą wersję problemu, który mam, szczególnie z wartościami NULL.

CREATE TABLE public.test_upsert (
upsert_id serial,
name character varying(32) NOT NULL,
status integer NOT NULL,
test_field text,
identifier character varying(255),
count integer,
CONSTRAINT upsert_id_pkey PRIMARY KEY (upsert_id),
CONSTRAINT test_upsert_name_status_test_field_key UNIQUE (name, status, test_field)
);

Uruchomienie tego zapytania działa w razie potrzeby (Najpierw wstaw, a następnie kolejne wstawienia, po prostu zwiększ liczbę):

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun',1,'test value','ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1 
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value';

Jednak jeśli uruchomię to zapytanie, za każdym razem wstawiany jest 1 wiersz zamiast zwiększania liczby dla pierwszego wiersza:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun',1,null,'ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1  
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = null;

To jest mój problem. Muszę po prostu zwiększyć wartość licznika i nie tworzyć wielu identycznych wierszy z wartościami null.

Próba dodania częściowego unikalnego indeksu:

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);

Daje to jednak takie same wyniki, wstawianie wielu pustych wierszy lub komunikat o błędzie podczas próby wstawienia:

BŁĄD: nie ma ograniczenia unikalnego lub wykluczającego pasującego do specyfikacji ON CONFLICT

Próbowałem już dodać dodatkowe informacje o indeksie częściowym, takie jak WHERE test_field is not null OR identifier is not null. Jednak podczas wstawiania pojawia się komunikat o błędzie ograniczenia.

Wyjaśnij ON CONFLICT DO UPDATEzachowanie

Rozważ tutaj instrukcję :

Dla każdego pojedynczego wiersza proponowanego do wstawienia albo wstawianie jest kontynuowane, albo, jeżeli ograniczenie arbitra lub indeks określone przez conflict_targetjest naruszone, conflict_actionbrana jest alternatywa .

Odważny nacisk moje. Nie musisz więc powtarzać predykatów dla kolumn zawartych w unikalnym indeksie w WHEREklauzuli do UPDATE( conflict_action):

INSERT INTO test_upsert AS tu
       (name   , status, test_field  , identifier, count) 
VALUES ('shaun', 1     , 'test value', 'ident'   , 1)
ON CONFLICT (name, status, test_field) DO UPDATE
SET count = tu.count + 1;
WHERE tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value'

Unikalne naruszenie już określa, co dodałeś WHERE klauzula wymusiłaby nadmiarowo.

Wyjaśnij indeks częściowy

Dodaj WHEREklauzulę, aby stał się faktycznym indeksem częściowym, tak jak sam wspomniałeś (ale z odwróconą logiką):

CREATE UNIQUE INDEX test_upsert_partial_idx
ON public.test_upsert (name, status)
WHERE test_field IS NULL;  -- not: "is not null"

Aby użyć tego częściowego indeksu w UPSERT, potrzebujesz dopasowania takiego jak @ypercube pokazuje :conflict_target

ON CONFLICT (name, status) WHERE test_field IS NULL

Teraz wywnioskowano powyższy indeks częściowy. Jednak , jak zauważa również instrukcja :

[...] niepodzielny indeks częściowy (indeks unikalny bez predykatu) zostanie wywnioskowany (a zatem użyty przez ON CONFLICT), jeżeli taki indeks spełniający wszystkie pozostałe kryteria będzie dostępny.

Jeśli masz dodatkowy (lub tylko) indeks, tylko (name, status)on (również) zostanie użyty. Indeks na (name, status, test_field)wprost by nie wynika. To nie wyjaśnia twojego problemu, ale mogło zwiększyć zamieszanie podczas testowania.

Rozwiązanie

AIUI, żadne z powyższych nie rozwiązuje jeszcze twojego problemu . Przy indeksie częściowym wychwytywane byłyby tylko specjalne przypadki z dopasowanymi wartościami NULL. Inne duplikaty wierszy zostałyby wstawione, jeśli nie masz innych pasujących unikalnych indeksów / ograniczeń, lub podniosłyby wyjątek, jeśli tak zrobisz. Przypuszczam, że nie tego chcesz. Ty piszesz:

Klucz złożony składa się z 20 kolumn, z których 10 można zerować.

Co dokładnie uważasz za duplikat? Postgres (zgodnie ze standardem SQL) nie uważa dwóch wartości NULL za równe. Instrukcja:

Zasadniczo naruszenie unikalnego ograniczenia jest naruszane, jeśli w tabeli jest więcej niż jeden wiersz, w którym wartości wszystkich kolumn zawartych w ograniczeniu są równe. Jednak dwie wartości zerowe nigdy nie są uważane za równe w tym porównaniu. Oznacza to, że nawet w obecności wyjątkowego ograniczenia można przechowywać zduplikowane wiersze zawierające wartość zerową w co najmniej jednej z ograniczonych kolumn. To zachowanie jest zgodne ze standardem SQL, ale słyszeliśmy, że inne bazy danych SQL mogą nie przestrzegać tej reguły. Dlatego należy zachować ostrożność podczas opracowywania aplikacji, które mają być przenośne.

Związane z:

• Zezwalaj na null w unikalnej kolumnie

Zakładam, że chcesz, abyNULLwartości we wszystkich 10 zerowalnych kolumnach były uważane za równe. Eleganckie i praktyczne jest pokrycie pojedynczej nullowej kolumny dodatkowym indeksem częściowym, jak pokazano tutaj:

• Wielokolumnowe ograniczenie PostgreSQL i wartości NULL

Ale szybko wymyka się to spod kontroli w przypadku bardziej zerowych kolumn. Będziesz potrzebował częściowego indeksu dla każdej odrębnej kombinacji zerowalnych kolumn. Dla tylko 2 z nich to 3 indeksów cząstkowych dla (a), (b)i (a,b). Liczba rośnie wykładniczo wraz z 2^n - 1. Dla 10 zerowalnych kolumn, aby pokryć wszystkie możliwe kombinacje wartości NULL, potrzebujesz już 1023 indeksów częściowych. Nie idź

Proste rozwiązanie: zastąp wartości NULL i zdefiniuj zaangażowane kolumny NOT NULL, a wszystko działałoby dobrze z prostymUNIQUE ograniczeniem.

Jeśli nie jest to opcja, sugeruję indeks wyrażenia, COALESCEaby zastąpić NULL w indeksie:

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

Pusty ciąg ( '') jest oczywistym kandydatem do typów znaków, ale można użyć dowolnego wartość prawną, które albo nigdy nie pojawia się lub może być złożona z NULL według twojej definicji „wyjątkowy”.

Następnie użyj tego oświadczenia:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun', 1, null        , 'ident', 11)  -- works with
     , ('bob'  , 2, 'test value', 'ident', 22)  -- and without NULL
ON     CONFLICT (name, status, COALESCE(test_field, '')) DO UPDATE  -- match expr. index
SET    count = COALESCE(tu.count + EXCLUDED.count, EXCLUDED.count, tu.count);

Podobnie jak @ypercube zakładam, że faktycznie chcesz dodać countdo istniejącej liczby. Ponieważ kolumna może mieć wartość NULL, dodanie wartości NULL spowoduje ustawienie kolumny NULL. Jeśli zdefiniujesz count NOT NULL, możesz uprościć.

Innym pomysłem byłoby po prostu porzucenie argumentu conflict_target z oświadczenia, aby objąć wszystkie unikalne naruszenia . Następnie możesz zdefiniować różne unikalne indeksy dla bardziej wyrafinowanej definicji tego, co powinno być „unikalne”. Ale to nie będzie latać ON CONFLICT DO UPDATE. Instrukcja jeszcze raz:

Na ON CONFLICT DO NOTHINGto jest opcjonalne określenie conflict_target; po pominięciu obsługiwane są konflikty ze wszystkimi możliwymi do użycia ograniczeniami (i unikalnymi indeksami). Ponieważ należy podać cel ON CONFLICT DO UPDATEkonfliktu .

Myślę, że problem polega na tym, że nie masz częściowego indeksu, a ON CONFLICTskładnia nie pasuje do test_upsert_upsert_id_idxindeksu, ale inne unikalne ograniczenie.

Jeśli zdefiniujesz indeks jako częściowy (z WHERE test_field IS NULL):

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status)
WHERE test_field IS NULL ;

a te wiersze są już w tabeli:

INSERT INTO test_upsert as tu
    (name, status, test_field, identifier, count) 
VALUES 
    ('shaun', 1, null, 'ident', 1),
    ('maria', 1, null, 'ident', 1) ;

wtedy zapytanie powiedzie się:

INSERT INTO test_upsert as tu
    (name, status, test_field, identifier, count) 
VALUES 
    ('peter', 1,   17, 'ident', 1),
    ('shaun', 1, null, 'ident', 3),
    ('maria', 1, null, 'ident', 7)
ON CONFLICT 
    (name, status) WHERE test_field IS NULL   -- the conflicting condition
DO UPDATE SET
    count = tu.count + EXCLUDED.count 
WHERE                                         -- when to update
    tu.name = 'shaun' AND tu.status = 1 ;     -- if you don't want all of the
                                              -- updates to happen

z następującymi wynikami:

('peter', 1,   17, 'ident', 1)  -- no conflict: row inserted

('shaun', 1, null, 'ident', 3)  -- conflict: no insert
                           -- matches where: row updated with count = 1+3 = 4

('maria', 1, null, 'ident', 1)  -- conflict: no insert
                     -- doesn't match where: no update