with - psql commands




Inserisci, su aggiornamento duplicato in PostgreSQL? (11)

Diversi mesi fa ho imparato da una risposta su Stack Overflow come eseguire più aggiornamenti contemporaneamente in MySQL usando la seguente sintassi:

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);

Ora sono passato a PostgreSQL e apparentemente questo non è corretto. Si riferisce a tutte le tabelle corrette, quindi presumo che si tratti di parole chiave diverse ma non sono sicuro di dove sia inclusa la documentazione di PostgreSQL.

Per chiarire, voglio inserire diverse cose e se esistono già per aggiornarle.


Con PostgreSQL 9.1 questo può essere ottenuto utilizzando un CTE scrivibile ( espressione di tabella comune ):

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)

Vedi questi post di blog:

Si noti che questa soluzione non impedisce una violazione della chiave univoca, ma non è vulnerabile agli aggiornamenti persi.
Vedi il seguito di Craig Ringer su dba.stackexchange.com


Ho lo stesso problema per la gestione delle impostazioni dell'account come coppie di valori nominali. I criteri di progettazione prevedono che client diversi possano avere set di impostazioni differenti.

La mia soluzione, simile a JWP, è quella di eliminare e sostituire in blocco, generando il record di unione all'interno dell'applicazione.

Questo è abbastanza a prova di proiettile, indipendente dalla piattaforma e dal momento che non ci sono mai più di circa 20 impostazioni per client, questo è solo 3 chiamate db con carico piuttosto basso, probabilmente il metodo più veloce.

L'alternativa dell'aggiornamento di singole righe - il controllo delle eccezioni e l'inserimento - o una combinazione di codice odioso, lento e spesso interrotto perché (come accennato sopra) gestione delle eccezioni SQL non standard che cambia da db a db - o anche da rilasciare al rilascio.

 #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

In PostgreSQL 9.5 e versioni successive è possibile utilizzare INSERT ... ON CONFLICT UPDATE .

Vedi la documentazione .

Un INSERT ... ON DUPLICATE KEY UPDATE MySQL INSERT ... ON DUPLICATE KEY UPDATE può essere riformulato direttamente su ON CONFLICT UPDATE . Né la sintassi SQL-standard, sono entrambe estensioni specifiche del database. Ci sono buone ragioni per cui MERGE non è stato usato per questo , una nuova sintassi non è stata creata solo per divertimento. (La sintassi di MySQL ha anche problemi che indicano che non è stata adottata direttamente).

ad es. configurazione data:

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

la query MySQL:

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

diventa:

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

differenze:

  • È necessario specificare il nome della colonna (o il nome del vincolo univoco) da utilizzare per il controllo di unicità. È ON CONFLICT (columnname) DO

  • È necessario utilizzare la parola chiave SET , come se si trattasse di una normale istruzione UPDATE

Ha anche alcune caratteristiche interessanti:

  • Puoi avere una clausola WHERE sul tuo UPDATE (ti permette di attivare ON CONFLICT UPDATE in ON CONFLICT IGNORE per determinati valori)

  • I valori proposti per l'inserimento sono disponibili come variabile di riga EXCLUDED , che ha la stessa struttura della tabella di destinazione. È possibile ottenere i valori originali nella tabella utilizzando il nome della tabella. Quindi in questo caso EXCLUDED.c sarà 10 (perché è quello che abbiamo provato ad inserire) e "table".c sarà 3 perché quello è il valore corrente nella tabella. È possibile utilizzare uno o entrambi nelle espressioni SET e nella clausola WHERE .

Per lo sfondo su upsert vedi Come UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?


Io uso questa unione di funzioni

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

Per unire piccoli set, usare la funzione sopra è ok. Tuttavia, se si stanno unendo grandi quantità di dati, suggerirei di cercare http://mbk.projects.postgresql.org

L'attuale best practice di cui sono a conoscenza è:

  1. COPIA i dati nuovi / aggiornati nella tabella temporanea (certo, o puoi fare INSERIRE se il costo è ok)
  2. Acquisisci blocco [facoltativo] (l'avviso è preferibile ai blocchi di tabella, IMO)
  3. Unire. (la parte divertente)

Personalmente, ho impostato una "regola" allegata all'istruzione insert. Supponiamo che tu abbia una tabella "dns" che registra gli hit dns per cliente su base per volta:

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

Volevi essere in grado di reinserire le righe con valori aggiornati o crearle se non esistessero già. Digitato customer_id e ora. Qualcosa come questo:

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));

Aggiornamento: questo ha il potenziale per fallire se si verificano inserimenti simultanei, poiché genererà eccezioni unique_violation. Tuttavia, la transazione non terminata continuerà e avrà esito positivo e sarà sufficiente ripetere la transazione terminata.

Tuttavia, se ci sono tonnellate di inserimenti che si verificano continuamente, è necessario inserire un blocco di tabella attorno alle istruzioni di inserimento: Il blocco ESCLUSIVO SHARE ROW impedirà qualsiasi operazione che potrebbe inserire, eliminare o aggiornare righe nella tabella di destinazione. Tuttavia, gli aggiornamenti che non aggiornano la chiave univoca sono sicuri, quindi se non si esegue alcuna operazione, utilizzare invece i blocchi di avviso.

Inoltre, il comando COPY non usa REGOLE, quindi se stai inserendo con COPY, dovrai invece utilizzare i trigger.



Simile alla risposta più gradita, ma funziona leggermente più veloce:

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)

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


stavo cercando la stessa cosa quando sono venuto qui, ma la mancanza di una funzione generica "upsert" mi ha disturbato un po 'così ho pensato che potevi semplicemente passare l'aggiornamento e inserire sql come argomenti su quella funzione dal manuale

sarebbe simile a questo:

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;
$$;

e forse per fare ciò che inizialmente volevi fare, batch "upsert", potresti usare Tcl per dividere sql_update e fare il loop dei singoli aggiornamenti, il risultato della preformance sarà molto piccolo vedi http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

il costo più alto è l'esecuzione della query dal codice, sul lato del database il costo di esecuzione è molto più piccolo


Attenzione: questo non è sicuro se eseguito da più sessioni contemporaneamente (vedi avvertenze di seguito).

Un altro modo intelligente di fare un "UPSERT" in postgresql è di fare due istruzioni UPDATE / INSERT sequenziali che sono progettate per avere successo o non avere alcun effetto.

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);

L'UPDATE avrà successo se esiste già una riga con "id = 3", altrimenti non ha alcun effetto.

L'INSERT avrà successo solo se la riga con "id = 3" non esiste già.

È possibile combinare questi due in un'unica stringa ed eseguirli entrambi con una singola istruzione SQL eseguita dall'applicazione. Si consiglia di eseguirli insieme in una singola transazione.

Funziona molto bene quando viene eseguito in isolamento o su una tabella bloccata, ma è soggetto a condizioni di competizione, il che significa che potrebbe ancora fallire con errore della chiave duplicata se una riga viene inserita contemporaneamente, oppure potrebbe terminare senza riga inserita quando una riga viene cancellata contemporaneamente . Una transazione SERIALIZABLE su PostgreSQL 9.1 o versioni successive la gestirà in modo affidabile al costo di un tasso di errore di serializzazione molto alto, il che significa che dovrai riprovare molto. Scopri perché è così complicato , che discute questo caso in modo più dettagliato.

Questo approccio è anche soggetto a aggiornamenti persi nell'isolamento di read committed meno che l'applicazione non controlli i conteggi delle righe interessate e verifichi che l' insert o l' update interessato una riga .


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






sql-merge