jak emulować „wstaw ignorowanie” i „przy zduplikowanej aktualizacji klucza” (scalanie sql) za pomocą postgresql?

Niektóre serwery SQL mają funkcję, która INSERTjest pomijana, jeśli naruszyłoby to ograniczenie klucza podstawowego / unikalnego. Na przykład MySQL ma INSERT IGNORE.

Jaki jest najlepszy sposób na emulację INSERT IGNOREi ON DUPLICATE KEY UPDATEużywanie PostgreSQL?

Spróbuj wykonać AKTUALIZACJĘ. Jeśli nie modyfikuje żadnego wiersza, co oznacza, że ​​nie istniał, więc zrób wstawianie. Oczywiście robisz to w ramach transakcji.

Możesz oczywiście zawinąć to w funkcję, jeśli nie chcesz umieszczać dodatkowego kodu po stronie klienta. Potrzebujesz także pętli dla bardzo rzadkich warunków wyścigu w tym myśleniu.

Przykład tego można znaleźć w dokumentacji: http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html , przykład 40-2 na dole.

To zwykle najłatwiejszy sposób. Możesz zrobić trochę magii z zasadami, ale prawdopodobnie będzie to dużo bardziej bałagan. Poleciłbym podejście polegające na zawijaniu funkcji każdego dnia.

Działa to dla wartości w jednym wierszu lub w kilku wierszach. Jeśli masz do czynienia z dużą liczbą wierszy, na przykład z podzapytania, najlepiej jest podzielić je na dwa zapytania, jedno dla WSTAWIANIA i jedno dla AKTUALIZACJI (oczywiście jako odpowiednie sprzężenie / podwybór - nie ma potrzeby wpisywania głównego filtruj dwukrotnie)

W PostgreSQL 9.5 jest to teraz natywna funkcjonalność (podobnie jak MySQL od kilku lat):

WSTAW ... W KONFLIKCIE NIC NIE ROBIĆ / AKTUALIZUJ („UPSERT”)

9.5 zapewnia obsługę operacji „UPSERT”. INSERT jest rozszerzony, aby akceptować klauzulę ON CONFLICT DO UPDATE / IGNORE. Ta klauzula określa alternatywne działanie, jakie należy podjąć w przypadku podwójnego naruszenia.

...

Dalszy przykład nowej składni:

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

Edycja: jeśli przegapiłeś odpowiedź Warrena, PG9.5 ma to teraz natywnie; czas na upgrade!

Opierając się na odpowiedzi Billa Karwina, aby wyjaśnić, jak wyglądałoby podejście oparte na regułach (przenoszenie z innego schematu w tej samej bazie danych i przy użyciu wielokolumnowego klucza podstawowego):

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";

Uwaga: reguła ma zastosowanie do wszystkich INSERToperacji, dopóki reguła nie zostanie usunięta, więc nie całkiem ad hoc.

Dla tych z Was, którzy mają Postgres 9.5 lub nowszy, nowa składnia ON CONFLICT DO NIC powinna działać:

INSERT INTO target_table (field_one, field_two, field_three ) 
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;

Dla tych z nas, którzy mają wcześniejszą wersję, to prawo łączenia będzie działać zamiast:

INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table 
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;

Aby uzyskać logikę wstawiania ignorowania , możesz zrobić coś jak poniżej. Zauważyłem, że po prostu wstawianie wartości dosłownych z instrukcji select działa najlepiej, a następnie możesz zamaskować zduplikowane klucze za pomocą klauzuli NOT EXISTS. Podejrzewam, że aby uzyskać aktualizację zduplikowanej logiki, potrzebna byłaby pętla pl / pgsql.

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
  ('935',' Citroën Brazil','Citroën'),
  ('ABC', 'Toyota', 'Toyota'),
  ('ZOM',' OM','OM')
  ) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
  WHERE NOT EXISTS (
    --ignore anything that has already been inserted
    SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)

INSERT INTO mytable(col1,col2) 
    SELECT 'val1','val2' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')

Wygląda na to, że PostgreSQL obsługuje obiekt schematu o nazwie a regułą .

http://www.postgresql.org/docs/current/static/rules-update.html

Możesz utworzyć regułę ON INSERTdla danej tabeli, robiąc to, NOTHINGjeśli istnieje wiersz z daną wartością klucza podstawowego, lub wykonując UPDATEzamiast tego, INSERTjeśli istnieje wiersz z daną wartością klucza podstawowego.

Sam tego nie próbowałem, więc nie mogę mówić z doświadczenia ani dawać przykładu.

Jak @hanmari wspomniał w swoim komentarzu. przy wstawianiu do tabel postgres, funkcja on konfliktu (..) nic nie robi, jest najlepszym kodem, aby nie wstawiać zduplikowanych danych .:

query = "INSERT INTO db_table_name(column_name)
         VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"

Linia kodu ON CONFLICT pozwoli instrukcji insert na dalsze wstawianie wierszy danych. Kod zapytania i wartości to przykład wstawienia daty z Excela do tabeli dbresy postgres. Mam ograniczenia dodane do tabeli postgres, której używam, aby upewnić się, że pole ID jest unikalne. Zamiast wykonywać usuwanie wierszy danych, które są takie same, dodaję wiersz kodu sql, który przenumeruje kolumnę ID, zaczynając od 1. Przykład:

q = 'ALTER id_column serial RESTART WITH 1'

Jeśli moje dane mają pole ID, nie używam go jako podstawowego identyfikatora / identyfikatora seryjnego, tworzę kolumnę ID i ustawiam ją na serial. Mam nadzieję, że te informacje są pomocne dla wszystkich. * Nie mam wyższego wykształcenia w zakresie programowania / kodowania. Wszystko, co wiem z kodowania, uczę się sam.

To rozwiązanie pozwala uniknąć używania reguł:

BEGIN
   INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION 
   WHEN unique_violation THEN
     UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;

ale ma wadę wydajności (patrz PostgreSQL.org ):

Wejście i wyjście bloku zawierającego klauzulę EXCEPTION jest znacznie droższe niż blok bez niej. Dlatego nie używaj EXCEPTION bez potrzeby.

Zbiorczo zawsze możesz usunąć wiersz przed wstawką. Usunięcie wiersza, który nie istnieje, nie powoduje błędu, więc jest bezpiecznie pomijany.

W przypadku skryptów importu danych, aby w pewnym sensie zastąpić „JEŚLI NIE ISTNIEJE”, istnieje nieco niezręczne sformułowanie, które jednak działa:

DO
$do$
BEGIN
PERFORM id
FROM whatever_table;

IF NOT FOUND THEN
-- INSERT stuff
END IF;
END
$do$;