Inserisci se non esiste, altrimenti restituisce id in postgresql

Ho una semplice tabella in PostgreSQL che ha tre colonne:

  • id chiave primaria seriale
  • chiave varchar
  • valore varchar

Ho già visto questa domanda qui su SO: Insert, su aggiornamento duplicato in PostgreSQL? ma mi chiedo solo come ottenere l’id se esiste, invece di aggiornare. Se la pratica standard è quella di “inserire” o “aggiornare se esiste”, perché? Il costo di eseguire SELECT (LIMIT 1) è maggiore di un UPDATE?

Ho il codice seguente

INSERT INTO tag ("key", "value") SELECT 'key1', 'value1' WHERE NOT EXISTS ( SELECT id,"key","value" FROM tag WHERE key = 'key1' AND value = 'value1' ); 

che funziona nel senso che non inserisce se esiste, ma mi piacerebbe ottenere l’id. C’è una clausola “RETURNING id” o qualcosa di simile che potrei toccare lì?

Sì, sta returning

 INSERT INTO tag ("key", "value") SELECT 'key1', 'value1' WHERE NOT EXISTS ( SELECT id, "key", "value" FROM node_tag WHERE key = 'key1' AND value = 'value1' ) returning id, "key", "value" 

Per restituire la riga se già esiste

 with s as ( select id, "key", "value" from tag where key = 'key1' and value = 'value1' ), i as ( insert into tag ("key", "value") select 'key1', 'value1' where not exists (select 1 from s) returning id, "key", "value" ) select id, "key", "value" from i union all select id, "key", "value" from s 

Se la riga non esiste, verrà restituito l’altro già esistente.

BTW, se la coppia “chiave” / “valore” lo rende unico allora è la chiave primaria, e non c’è bisogno di una colonna id. A meno che una o entrambe le coppie “chiave” / “valore” possano essere nulle.

 with vals as ( select 'key5' as key, 'value2' as value ) insert into Test1 (key, value) select v.key, v.value from vals as v where not exists (select * from Test1 as t where t.key = v.key and t.value = v.value) returning id 

sql fiddle demo

Ed è ansible memorizzare il valore restituito alle variabili in forma di … RETURNING field1, field2, … INTO var1, var2, …

RESTITUZIONE restituirà normalmente una query che restituirebbe l’errore “la query non ha destinazione per i dati dei risultati” se la si chiama in plpgsql senza utilizzare il set di risultati restituito.