Menu

Upsert w PostgreSQL przez INSERT ... ON CONFLICT

Upsert w PostgreSQL przez INSERT ... ON CONFLICT: DO UPDATE z EXCLUDED, DO NOTHING, aktualizacje warunkowe, liczniki i odróżnianie wstawionych wierszy od zaktualizowanych.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

PostgreSQL nie ma słowa kluczowego UPSERT. Piszesz INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Gdy nowy wiersz naruszyłby ograniczenie unikalności na tej kolumnie, PostgreSQL zamiast tego aktualizuje istniejący wiersz. DO NOTHING go pomija.

A-100 już istniał, więc jego cena zmieniła się na 12.5. B-200 był nowy i został wstawiony. Jedno polecenie obsługuje oba przypadki i działa poprawnie, gdy inne sesje jednocześnie wstawiają te same klucze.

Wstawienie albo aktualizacja jednego wiersza

Polecenie ma trzy części:

INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
  • Cel konfliktu podaje kolumny unikalnego indeksu albo ograniczenia, na przykład klucza głównego. Musi dokładnie pasować do jednego z nich, inaczej PostgreSQL zgłasza "there is no unique or exclusion constraint matching the ON CONFLICT specification". Zamiast kolumn możesz podać ograniczenie: ON CONFLICT ON CONSTRAINT products_pkey.
  • EXCLUDED to wiersz, który próbowałeś wstawić. EXCLUDED.price to nowa cena, a products.price cena już zapisana.
  • DO UPDATE SET zmienia tylko wymienione kolumny. Pozostałe kolumny istniejącego wiersza zachowują wartości.

Unikalny indeks z klauzulą WHERE (indeks częściowy) też działa jako cel, jeśli powtórzysz jego warunek: ON CONFLICT (email) WHERE deleted_at IS NULL.

Pomijanie duplikatów przez DO NOTHING

DO NOTHING wstawia nowe wiersze i odrzuca kolidujące, bez błędu. Bez celu konfliktu pomija wiersz, który narusza dowolne ograniczenie unikalności.

RETURNING wypisuje tylko lin@example.com. Pominięty wiersz nie jest zwracany, więc DO NOTHING ... RETURNING pokazuje, które wiersze były nowe.

Zwiększanie licznika

Żeby zwiększyć zapisaną wartość zamiast ją zastąpić, dodaj EXCLUDED do bieżącej wartości. Bieżącą wartość poprzedź nazwą tabeli. Wewnątrz DO UPDATE samo views pasuje zarówno do tabeli, jak i do EXCLUDED, a PostgreSQL zatrzymuje się z błędem "column reference "views" is ambiguous".

Pierwsze polecenie wstawia wiersz z 1, a dwa kolejne dodają do niego, więc /home kończy z 3.

Aktualizacja tylko przy zmianie wartości

WHERE po DO UPDATE SET decyduje dla każdego wiersza, czy aktualizacja się wykona. Gdy jest fałszywe, wiersz zostaje bez zmian i nie liczy się ani jako wstawiony, ani jako zaktualizowany. Użyj tego, żeby trzymać tylko najnowsze dane albo nie zapisywać nowej wersji wiersza, gdy nic się nie zmieniło.

A-100 zostaje z 10, bo przychodzący wiersz jest starszy. B-200 przyjmuje 25. Żeby pomijać aktualizacje, które zapisałyby te same wartości, użyj WHERE prices.price IS DISTINCT FROM EXCLUDED.price, co obsługuje też NULL.

Odróżnianie wstawionych wierszy od zaktualizowanych

RETURNING działa przy upsert tak samo jak przy każdym wstawieniu. Żeby odróżnić oba przypadki, odczytaj kolumnę systemową xmax, która dla wiersza wstawionego przez to polecenie wynosi 0:

A-100 wraca z 8 i inserted równym false, a C-300 z 7 i true. xmax to wewnętrzny szczegół, a nie udokumentowane API, więc traktuj go jako wygodny skrót. Strona o RETURNING omawia tę klauzulę w całości.

Błędy, które spotkasz

  • "ON CONFLICT DO UPDATE command cannot affect row a second time": ten sam klucz występuje dwa razy w VALUES albo SELECT jednego polecenia. Najpierw usuń duplikaty, na przykład przez SELECT DISTINCT ON (sku) ....
  • "column reference is ambiguous": poprzedź kolumnę nazwą tabeli w wyrażeniach SET i w WHERE, jak wyżej.
  • Luki w identyfikatorach identity albo serial: wstawienie, które zamienia się w aktualizację, już pobrało wartość z sekwencji, więc identyfikatory przeskakują liczby. To normalne.

MERGE (PostgreSQL 15 i nowsze) też potrafi wstawić albo zaktualizować, ale nie chroni przed sytuacją, w której dwie sesje jednocześnie wstawiają ten sam klucz. Do upsert po kluczu używaj ON CONFLICT. Oficjalna dokumentacja INSERT wymienia każdą opcję.

Najczęściej zadawane pytania

Czy PostgreSQL ma polecenie UPSERT?

Nie ma słowa kluczowego UPSERT. PostgreSQL 9.5 dodał INSERT ... ON CONFLICT, które wstawia wiersz albo, gdy naruszyłby ograniczenie unikalności, aktualizuje istniejący wiersz (DO UPDATE) lub go pomija (DO NOTHING).

Czy INSERT ... ON CONFLICT jest bezpieczne przy współbieżnych wstawieniach?

Tak. Każdy proponowany wiersz kończy się wstawieniem albo aktualizacją, nawet gdy inne sesje w tej samej chwili wstawiają ten sam klucz. SELECT, a po nim INSERT albo UPDATE w kodzie aplikacji, takiej gwarancji nie daje.

MERGE czy ON CONFLICT do upsert?

Użyj ON CONFLICT do wstawienia albo aktualizacji po kluczu. MERGE (PostgreSQL 15 i nowsze) obsługuje więcej przypadków, na przykład usuwanie pasujących wierszy. Przy współbieżnych wstawieniach tego samego klucza może zakończyć się naruszeniem unikalności, czego ON CONFLICT unika.

Skąd błąd "there is no unique or exclusion constraint matching the ON CONFLICT specification"?

Kolumny w ON CONFLICT (...) muszą dokładnie odpowiadać unikalnemu indeksowi albo ograniczeniu. Dodaj ograniczenie UNIQUE albo unikalny indeks na tych kolumnach albo podaj kolumny takiego, które już istnieje.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij