Inserare, pe actualizare duplicat în PostgreSQL? (Programare, Sql, Postgresql, Upsert, Sql Merge)

Teifion a intrebat.

Cu câteva luni în urmă am aflat dintr-un răspuns pe Stack Overflow cum să efectuez mai multe actualizări deodată în MySQL folosind următoarea sintaxă:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

Acum am trecut la PostgreSQL și se pare că acest lucru nu este corect. Se referă la toate tabelele corecte, așa că presupun că este o problemă de utilizare a unor cuvinte cheie diferite, dar nu sunt sigur unde în documentația PostgreSQL este acoperit acest lucru.

Pentru a clarifica, vreau să inserez mai multe lucruri și, dacă acestea există deja, să le actualizez.

Comentarii

    39

  • Oricine găsește această întrebare ar trebui să citească articolul lui Depesz „De ce este upsert-ul atât de complicat?”. Acesta explică extrem de bine problema și posibilele soluții. –  > Por Craig Ringer.
  • UPSERT va fi adăugat în Postgres 9.5: wiki.postgresql.org/wiki/… –  > Por tommed.
  • @tommed – a fost făcut: stackoverflow.com/a/34639631/4418 –  > Por warren.
16 răspunsuri
Stephen Denne

PostgreSQL începând cu versiunea 9.5 a UPSERT sintaxa, cu ON CONFLICT clauză. cu următoarea sintaxă (similară cu MySQL)

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

Căutând „upsert” în arhivele grupului de e-mailuri de la postgresql, am găsit un exemplu de realizare a ceea ce eventual doriți să faceți, în manualul:

Exemplul 38-2. Excepții cu UPDATE/INSERT

Acest exemplu utilizează gestionarea excepțiilor pentru a efectua fie UPDATE, fie INSERT, după caz:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

Este posibil să existe un exemplu despre cum se poate face acest lucru în masă, folosind CTE-uri în 9.1 și versiunile superioare, în manualul lista de discuții hackers:

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

A se vedea răspunsul lui a_horse_with_no_name pentru un exemplu mai clar.

Comentarii

  • Singurul lucru care nu-mi place la acest lucru este că ar fi mult mai lent, deoarece fiecare upsert ar fi propriul său apel individual în baza de date. –  > Por baash05.
  • @baash05 ar putea exista o modalitate de a face acest lucru în vrac, vezi răspunsul meu actualizat. –  > Por Stephen Denne.
  • Singurul lucru pe care l-aș face diferit ar fi să folosesc FOR 1..2 LOOP în loc de doar LOOP, astfel încât, dacă este încălcată o altă constrângere unică, nu se va roti la nesfârșit. –  > Por olamork.
  • Ce înseamnă excluded se referă în prima soluție de aici? –  > Por ichbinallen.
  • @ichbinallen în documentație Clauzele SET și WHERE din ON CONFLICT DO UPDATE au acces la rândul existent folosind numele tabelului (sau un alias) și la rândurile propuse pentru inserare folosind tabelul special exclus. În acest caz, tabelul special excluded vă oferă acces la valorile pe care ați încercat să le inserați în primul rând. –  > Por TMichel.
bovine

Atenție: acest lucru nu este sigur dacă este executat din mai multe sesiuni în același timp (a se vedea avertismentele de mai jos).


O altă modalitate inteligentă de a face un „UPSERT” în postgresql este de a face două instrucțiuni secvențiale UPDATE/INSERT care sunt concepute fiecare pentru a reuși sau a nu avea niciun efect.

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

UPDATE va avea succes dacă există deja un rând cu „id=3”, altfel nu are niciun efect.

INSERT va avea succes numai dacă rândul cu „id=3” nu există deja.

Puteți să le combinați pe acestea două într-un singur șir și să le executați pe amândouă cu o singură instrucțiune SQL executată din aplicația dumneavoastră. Executarea lor împreună într-o singură tranzacție este foarte recomandată.

Această operațiune funcționează foarte bine atunci când este executată în mod izolat sau pe o tabelă blocată, dar este supusă unor condiții de cursă, ceea ce înseamnă că ar putea eșua în continuare cu o eroare de cheie duplicată dacă un rând este inserat concomitent sau s-ar putea încheia fără niciun rând inserat atunci când un rând este șters concomitent. A SERIALIZABLE pe PostgreSQL 9.1 sau o versiune mai recentă o va gestiona în mod fiabil, dar cu prețul unei rate foarte ridicate de eșecuri de serializare, ceea ce înseamnă că va trebui să încercați din nou de multe ori. A se vedea de ce este upsert atât de complicat, care discută acest caz mai în detaliu.

Această abordare este, de asemenea, supusă actualizărilor pierdute în read committed izolare, cu excepția cazului în care aplicația verifică numărul de rânduri afectate și verifică dacă fie insert fie update a afectat un rând.

Comentarii

  • Răspuns scurt: dacă înregistrarea există, INSERT nu face nimic. Răspunsul lung: SELECT din INSERT va returna atâtea rezultate câte rezultate există în cadrul clauzei where. Adică cel mult unu (dacă numărul unu nu se regăsește în rezultatul subsecvenței), în caz contrar zero. Astfel, INSERT va adăuga fie unul, fie zero rânduri. –  > Por Peter Becker.
  • partea „where” poate fi simplificată prin utilizarea expresiei exists: ... where not exists (select 1 from table where id = 3); –  > Por Endy Tjahjono.
  • acesta ar trebui să fie răspunsul corect… cu câteva modificări minore, ar putea fi folosit pentru a face o actualizare în masă… Mă întreb dacă nu cumva ar putea fi folosit un tabel temporar… –  > Por baash05.
  • @keaplogik, acea limitare 9.1 este cu CTE (common table expressions) inscriptibile, care este descrisă într-un alt răspuns. Sintaxa folosită în acest răspuns este foarte elementară și este susținută de mult timp. –  > Por bovine.
  • Atenție, acest lucru face obiectul unor actualizări pierdute în read committed izolare, cu excepția cazului în care aplicația dumneavoastră verifică să se asigure că insert sau update au un număr de rânduri diferit de zero. A se vedea dba.stackexchange.com/q/78510/7788 –  > Por Craig Ringer.
a_horse_with_no_name

Cu PostgreSQL 9.1, acest lucru poate fi realizat utilizând un CTE care poate fi scris (expresie comună de tabel):

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

Consultați aceste intrări pe blog:


Rețineți că această soluție nu nu împiedică încălcarea unei chei unice, dar nu este vulnerabilă la actualizările pierdute.
Consultați continuarea realizată de Craig Ringer pe dba.stackexchange.com

Comentarii

  • @FrançoisBeausoleil: șansa unei condiții de cursă este mult mai mică decât în cazul abordării „try/handle exception” –  > Por a_horse_with_no_name.
  • @a_horse_with_no_name Cum adică, mai exact, șansa la condițiile de cursă este mult mai mică? Când execut această interogare concomitent cu aceleași înregistrări, primesc eroarea „duplicate key value violates unique constraint” de 100% din ori până când interogarea detectează că înregistrarea a fost inserată. Este acesta un exemplu complet? –  > Por Jeroen van Dijk.
  • @a_horse_with_no_name Soluția dvs. pare să funcționeze în situații simultane atunci când înfășurați instrucțiunea upsert cu următoarea blocare: BEGIN WORK; LOCK TABLE mytable IN SHARE ROW EXCLUSIVE MODE; <UPSERT HERE>; COMMIT WORK; –  > Por Jeroen van Dijk.
  • @JeroenvanDijk: mulțumesc. Ceea ce am vrut să spun cu „mult mai mic” este că, dacă mai multe tranzacții la acest lucru (și confirmă modificarea!), intervalul de timp dintre actualizare și inserare este mai mic, deoarece totul este doar o singură declarație. Puteți oricând să generați o încălcare a pk prin două instrucțiuni INSERT independente. Dacă blocați întreaga tabelă, serializați efectiv tot accesul la aceasta (lucru pe care l-ați putea realiza și cu nivelul de izolare serializabil). –  > Por a_calul_cu_nume_de_câine.
  • Această soluție este supusă unor actualizări pierdute dacă tranzacția de inserare se întoarce; nu există nicio verificare care să impună că UPDATE a afectat niciun rând. –  > Por Craig Ringer.
Craig Ringer

În PostgreSQL 9.5 și versiunile mai noi, puteți utiliza INSERT ... ON CONFLICT UPDATE.

Vezi documentația.

Un MySQL INSERT ... ON DUPLICATE KEY UPDATE poate fi reformulat direct într-un ON CONFLICT UPDATE. Niciuna dintre ele nu este o sintaxă standard SQL, ci sunt extensii specifice bazelor de date. Există motive întemeiate pentru care MERGE nu a fost folosit pentru acest lucru, nu a fost creată o nouă sintaxă doar pentru distracție. (Sintaxa MySQL are, de asemenea, probleme care înseamnă că nu a fost adoptată direct).

De exemplu, configurația dată:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

interogarea MySQL:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

devine:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

Diferențe:

  • Tu trebuie să să specificați numele coloanei (sau numele constrângerii unice) care urmează să fie utilizat pentru verificarea unicității. Acesta este ON CONFLICT (columnname) DO

  • Cuvântul cheie SET trebuie utilizat, ca și cum ar fi vorba de un cuvânt normal UPDATE declarație normală

Are și câteva caracteristici frumoase:

  • Puteți avea un WHERE în cadrul clauzei UPDATE (ceea ce vă permite să transformați efectiv ON CONFLICT UPDATE în ON CONFLICT IGNORE pentru anumite valori)

  • Valorile propuse pentru inserare sunt disponibile ca variabilă de rând EXCLUDED, care are aceeași structură ca și tabelul țintă. Puteți obține valorile originale din tabel folosind numele tabelului. Astfel, în acest caz EXCLUDED.c va fi 10 (pentru că asta este ceea ce am încercat să inserăm) și "table".c va fi 3 pentru că aceasta este valoarea curentă din tabel. Puteți utiliza oricare dintre ele sau ambele în SET expresii și WHERE clauza.

Pentru informații despre upsert, consultați Cum se face UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) în PostgreSQL?

Comentarii

  • Am analizat soluția PostgreSQL 9.5, așa cum ați descris mai sus, deoarece mă confruntam cu lacune în câmpul de incrementare automată în timp ce, sub MySQL’s ON DUPLICATE KEY UPDATE. Am descărcat Postgres 9.5 și am implementat codul dvs. dar, în mod ciudat, aceeași problemă apare sub Postgres: câmpul serial al cheii primare nu este consecutiv (există goluri între inserții și actualizări.). Aveți vreo idee despre ce se întâmplă aici? Este acest lucru normal? Aveți vreo idee cum să evitați acest comportament? Vă mulțumesc. –  > Por W.M..
  • @W.M. Acest lucru este destul de inerent unei operațiuni de upsert. Trebuie să evaluați funcția care generează secvența înainte de a încerca inserarea. Deoarece astfel de secvențe sunt concepute pentru a funcționa concomitent, ele sunt exceptate de la semantica normală a tranzacțiilor, dar chiar dacă nu ar fi așa, generarea nu este apelată într-o subtransacțiune și nu se întoarce înapoi, ci se finalizează în mod normal și se angajează împreună cu restul operațiunii. Astfel, acest lucru s-ar întâmpla chiar și în cazul implementărilor de secvențe „fără goluri”. Singura modalitate prin care BD ar putea evita acest lucru ar fi să amâne evaluarea generării secvenței până după verificarea cheilor. –  > Por Craig Ringer.
  • @W.M., ceea ce ar crea propriile probleme. Practic, ești blocat. Dar dacă vă bazați pe faptul că serial / auto_incrementul este fără goluri, aveți deja bug-uri. Puteți avea lacune de secvență din cauza rollback-urilor, inclusiv a erorilor tranzitorii – reporniri sub sarcină, erori de client în mijlocul tranzacției, blocări etc. Nu trebuie să vă bazați niciodată, niciodată, pe SERIAL / SEQUENCE sau AUTO_INCREMENT pe faptul că nu aveți lacune. Dacă aveți nevoie de secvențe fără goluri, acestea sunt mai complexe; de obicei, trebuie să folosiți un tabel de contorizare. Google vă va spune mai multe. Dar rețineți că secvențele fără spații împiedică orice concurență de inserție. –  > Por Craig Ringer.
  • @W.M. Dacă aveți neapărat nevoie de secvențe fără goluri și de upsert, ați putea utiliza abordarea upsert bazată pe funcții discutată în manual împreună cu o implementare a secvențelor fără goluri care utilizează un tabel de contorizare. Deoarece BEGIN ... EXCEPTION ... se execută într-o subacțiune care se anulează în caz de eroare, creșterea secvenței dvs. ar fi anulată dacă se anulează secvența INSERT eșuează. –  > Por Craig Ringer.
  • Mulțumesc foarte mult @Craig Ringer, a fost destul de informativ. Mi-am dat seama că pot pur și simplu să renunț la cheia primară cu incrementare automată. Am făcut o cheie primară compusă din 3 câmpuri și, pentru nevoile mele particulare actuale, nu este nevoie de un câmp cu incrementare automată fără goluri. Vă mulțumesc încă o dată, informațiile pe care le-ați furnizat îmi vor economisi timp în viitor, încercând să împiedic un comportament natural și sănătos al DB. Înțeleg mai bine acum. –  > Por W.M..
Paul Scheltema

Căutam același lucru când am venit aici, dar lipsa unei funcții generice de „upsert” m-a deranjat puțin, așa că m-am gândit că ai putea să treci sql-ul de actualizare și de inserare ca argumente pe acea funcție din manual.

care ar arăta astfel:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

și poate că pentru a face ceea ce ați vrut inițial să faceți, „upsert” pe loturi, ați putea folosi Tcl pentru a împărți sql_update și a face o buclă cu actualizările individuale, iar impactul asupra performanței va fi foarte mic. http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

cel mai mare cost este execuția interogării din codul dumneavoastră, în ceea ce privește baza de date, costul de execuție este mult mai mic.

Comentarii

  • Tot trebuie să executați această operațiune într-o buclă de reluare și este predispusă la curse cu un sistem concurent. DELETE cu excepția cazului în care blocați tabelul sau sunteți în SERIALIZABLE izolarea tranzacției pe PostgreSQL 9.1 sau mai mare. –  > Por Craig Ringer.
user80168

Nu există o comandă simplă pentru a face acest lucru.

Cea mai corectă abordare este să folosiți o funcție, cum ar fi cea de la docs.

O altă soluție (deși nu este atât de sigură) este să faceți update cu return, să verificați care rânduri au fost update-uri și să inserați restul rândurilor

Ceva de genul:

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

presupunând că id:2 a fost returnat:

insert into table (id, column) values (1, 'aa'), (3, 'cc');

Bineînțeles că va ieși din joc mai devreme sau mai târziu (în mediul concurent), deoarece există o condiție de cursă clară aici, dar de obicei va funcționa.

Iată un exemplu articol mai lung și mai cuprinzător pe această temă.

Comentarii

  • Dacă utilizați această opțiune, asigurați-vă că verificați dacă id-ul este returnat chiar dacă actualizarea nu face nimic. Am văzut baze de date care optimizează interogări de genul „Update table foo set bar = 4 where bar = 4”. –  > Por thelem.
Ch’marr

Personal, am stabilit o „regulă” atașată la instrucțiunea insert. Să zicem că ați avea un tabel „dns” care înregistrează accesările dns pentru fiecare client în parte:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

Ați fi vrut să puteți reintroduce rânduri cu valori actualizate sau să le creați dacă nu existau deja. Aveați în cheie client_id și ora. Ceva de genul acesta:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Actualizare: Acest lucru poate eșua dacă au loc inserări simultane, deoarece va genera excepții unique_violation. Cu toate acestea, tranzacția nefinalizată va continua și va reuși, iar dumneavoastră trebuie doar să repetați tranzacția finalizată.

Cu toate acestea, în cazul în care există tone de inserții care au loc în permanență, veți dori să blocați tabela în jurul instrucțiunilor de inserție: Blocarea SHARE ROW EXCLUSIVE va împiedica orice operațiune care ar putea insera, șterge sau actualiza rânduri în tabelul țintă. Cu toate acestea, actualizările care nu actualizează cheia unică sunt sigure, așa că, dacă nicio operațiune nu va face acest lucru, utilizați în schimb blocaje consultative.

De asemenea, comanda COPY nu utilizează RULES, așa că, dacă inserați cu COPY, va trebui să utilizați în schimb triggers.

Mise

Eu folosesc această funcție de fuziune

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

Comentarii

  • Este mai eficient să efectuați pur și simplu operațiunea update mai întâi și apoi să verificați numărul de rânduri actualizate. (A se vedea răspunsul lui Ahmad) –  > Por a_calul_cu_nume_de_câine.
Felipe FMMobile

Am personalizat funcția „upsert” de mai sus, dacă doriți să faceți INSERT AND REPLACE :

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

Și după pentru a executa, faceți ceva de genul acesta :

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

Este important să puneți o virgulă de dolar dublă pentru a evita erorile compilatorului

  • verificați viteza…

alexkovelsky

Similar cu răspunsul cel mai popular, dar funcționează puțin mai rapid:

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(sursa: http://www.the-art-of-web.com/sql/upsert/)

Comentarii

  • Acest lucru va eșua dacă este rulat concomitent în două sesiuni, deoarece niciuna dintre actualizări nu va vedea un rând existent, astfel încât ambele actualizări vor atinge zero rânduri, deci ambele interogări vor emite o inserție. –  > Por Craig Ringer.
Christian Hang-Hicks

În conformitate cu documentația PostgreSQL a INSERT declarație, manipularea declarației ON DUPLICATE KEY cazului nu este suportată. Partea respectivă a sintaxei este o extensie proprietară MySQL.

Comentarii

  • @Lucian MERGE este, de asemenea, de fapt mai mult o operațiune OLAP; consultați stackoverflow.com/q/17267417/398670 pentru explicații. Nu definește o semantică de concurență și majoritatea celor care o folosesc pentru upsert nu fac decât să creeze bug-uri. –  > Por Craig Ringer.
benno

Am aceeași problemă pentru gestionarea setărilor de cont ca perechi nume-valoare. criteriul de proiectare este că diferiți clienți ar putea avea seturi de setări diferite.

Soluția mea, similară cu cea a JWP, este de a șterge și înlocui în masă, generând înregistrarea de fuziune în cadrul aplicației.

Acest lucru este destul de rezistent la gloanțe, este independent de platformă și, deoarece nu există niciodată mai mult de aproximativ 20 de setări pe client, aceasta reprezintă doar 3 apeluri de baze de date cu sarcină destul de redusă – probabil cea mai rapidă metodă.

Alternativa actualizării rândurilor individuale – verificarea excepțiilor, apoi inserarea – sau o combinație a acestora este un cod hidos, lent și adesea se întrerupe deoarece (așa cum am menționat mai sus) gestionarea excepțiilor SQL non-standard se schimbă de la o bază de date la alta – sau chiar de la o versiune la alta.

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

Comentarii

  • Bine ați venit la SO. Frumoasă introducere! 🙂 –  > Por Don Question.
  • Acest lucru este mai mult ca REPLACE INTO decât INSERT INTO ... ON DUPLICATE KEY UPDATE, ceea ce ar putea cauza o problemă dacă folosiți declanșatoare. Veți ajunge să rulați mai degrabă declanșatoare/reguli de ștergere și inserție, decât cele de actualizare. –  > Por cHao.
Ahmad
CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT

jwp

Pentru fuzionarea seturilor mici, utilizarea funcției de mai sus este bună. Cu toate acestea, dacă fuzionați cantități mari de date, v-aș sugera să vă uitați la http://mbk.projects.postgresql.org

Cea mai bună practică actuală pe care o cunosc este:

  1. COPIAȚI datele noi/actualizate în tabelul temporar (sigur, sau puteți face INSERT dacă costul este ok)
  2. Achiziționați un blocaj [opțional] (consultarea este preferabilă blocajelor de tabel, IMO)
  3. Merge. (partea distractivă)

Joey Adams

Editează: Acest lucru nu funcționează așa cum era de așteptat. Spre deosebire de răspunsul acceptat, aceasta produce încălcări ale cheii unice atunci când două procese apelează în mod repetat upsert_foo concomitent.

Eureka! Am găsit o modalitate de a face acest lucru într-o singură interogare: folosiți UPDATE ... RETURNING pentru a testa dacă a fost afectat vreun rând:

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

UPDATE trebuie făcută într-o procedură separată, deoarece, din păcate, aceasta este o eroare de sintaxă:

... WHERE NOT EXISTS (UPDATE ...)

Acum funcționează așa cum am dorit:

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');

Comentarii

  • Puteți să le combinați într-o singură instrucțiune dacă folosiți un CTE care poate fi scris. Dar, la fel ca majoritatea soluțiilor postate aici, aceasta este greșită și va eșua în prezența actualizărilor simultane. –  > Por Craig Ringer.
Audrius Meskauskas

UPDATE va returna numărul de rânduri modificate. Dacă folosiți JDBC (Java), puteți verifica această valoare în raport cu 0 și, dacă niciun rând nu a fost afectat, puteți lansa în schimb INSERT. Dacă folosiți un alt limbaj de programare, poate că numărul de rânduri modificate poate fi totuși obținut, verificați documentația.

Este posibil ca acest lucru să nu fie la fel de elegant, dar aveți un SQL mult mai simplu, care este mai ușor de utilizat din codul de apelare. În mod diferit, dacă scrieți scriptul de zece linii în PL/PSQL, probabil că ar trebui să aveți un test unitar de un fel sau altul doar pentru acesta.