Przykładowa tabela i dane
CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
CONSTRAINT col2_unique UNIQUE (col2)
);
INSERT INTO dupes values(1,1,'a'),(2,2,'b');
Odtwarzanie problemu
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2
Nazwijmy to Q1. Wynik to
ERROR: duplicate key value violates unique constraint "col2_unique"
DETAIL: Key (col2)=(2) already exists.
konflikt_target może wykonywać unikalne wnioskowanie o indeksie. Podczas wykonywania wnioskowania składa się z co najmniej jednej kolumny nazwa_kolumny_indeksu i / lub wyrażenia wyrażenia_indeksu oraz opcjonalnego przypisania_indeksu. Wszystkie unikalne indeksy nazwa_tabeli, które, bez względu na kolejność, zawierają dokładnie kolumny / wyrażenia określone w celu_konfliktu, są wywnioskowane (wybrane) jako indeksy arbitrów. Jeśli określono index_predicate, to jako kolejny wymóg wnioskowania musi spełniać indeksy arbitrów.
Daje to wrażenie, że poniższe zapytanie powinno zadziałać, ale tak nie jest, ponieważ w rzeczywistości wymagałoby to wspólnego, unikalnego indeksu dla kol1 i kol2. Jednak taki indeks nie gwarantowałby, że col1 i col2 będą niepowtarzalne indywidualnie, co jest jednym z wymagań PO.
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2
Nazwijmy to zapytanie Q2 (kończy się to błędem składniowym)
Czemu?
Postgresql zachowuje się w ten sposób, ponieważ to, co powinno się stać, gdy wystąpi konflikt w drugiej kolumnie, nie jest dobrze zdefiniowane. Możliwości jest wiele. Na przykład w powyższym zapytaniu Q1, czy należy aktualizować postgresql, col1
gdy występuje konflikt col2
? Ale co, jeśli to prowadzi do kolejnego konfliktu col1
? jak postgresql ma sobie z tym poradzić?
Rozwiązanie
Rozwiązaniem jest połączenie ON CONFLICT ze staromodnym UPSERTem .
CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
END;
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
Musisz zmodyfikować logikę tej zapisanej funkcji, aby aktualizowała kolumny dokładnie tak, jak chcesz. Wywołaj to jak
SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');