INSERT ON CONFLICT UPDATE

[Thallius]

Hi,

ich habe eine Tabelle mit einer Spalte id serial. Wenn ich nun einen INSERT mache und keine id angebe, dann macht er schön eine neuen Eintrag mit neuer Id.
Bei mySQL war es sehr schön, dass ich bei einem INSERT ON DUPLICATE KEY auch null bei der id angeben konnte. Dadurch konnte ich mit einem query sowohl einen alten Eintrag updaten (id != null) oder eben einen neuen Eintrag hinzufügen (id=null).

Geht das in Postgre gar nicht?

 

[akretschmer]

postgres=# create table thallius(id serial primary key, t text);
CREATE TABLE
postgres=# insert into thallius (t) values ('text1');
INSERT 0 1
postgres=# insert into thallius (t) values ('text2');
INSERT 0 1
postgres=# select * from thallius;
 id |   t   
----+-------
  1 | text1
  2 | text2
(2 rows)
postgres=# insert into thallius values (2, 'neu') on conflict (id) do update set t = excluded.t;
INSERT 0 1
postgres=# select * from thallius;
 id |   t   
----+-------
  1 | text1
  2 | neu
(2 rows)

postgres=#

So?

 

[Thallius]

DAs wäre für einen Update genau. Aber wenn ich nun einen neuen Datensatz einfügen will dessen id ich ja nicht kenne da es ihn noch nicht gibt würde ich halt in mySQL einfach

insert into thallius values (null, 'neu') on conflict (id) do update set t = excluded.t;

angeben und würde einen neuen Datensatz erhalten dessen id ich mir dann mit lastInsertID holen kann.

 

[akretschmer]

so ganz verstehe ich grad Dein Problem nicht. Du kannst bei PG auch sagen: "id int generated always as identity primary key", damit signalisierst Du, daß die ID *IMMER* vom System zu generieren ist - was ich als sehr sauber empfinde. Die vergebene ID kannst Du natürlich innerhalb der Session erfragen, oder aber Du verwendest ein Konstrukt mit RETURNING.

 

[Thallius]

ok machen wir es einfach. Ein Beispiel wie ich es mit MySQL machen kann:

Ich habe einen REST Service für eine Userverwaltung.

die Tabelle enthält

create table user (id int auto_increment primary key, name text);

in der Tabelle befindet sich ein User

insert into user (name) values ('Max');

somit bekommt Max die Id 1.

Nun kann man in der Applikation einen existierenden User ändern und einen neuen User anlegen. Wenn ich einen existierende User editiere, dann erfrage ich den User beim REST Service an mit seiner Id.

select id, Name from User where id = $1

in der Applikation ändere ich nun den namen und speichere den User wieder. Ich schicke an den REST Service also ein JSON kodiertes Object

{
id: 1,
name: "Moritz"
}

und speicher diesen mit

insert into user (id, name) values ($1, $2) on duplicate key update name = $2

Nun wird der Eintrag mit der Id 1 von Max auf Moritz geändert.

Wenn nun in der Applikation ein neuer User angelegt wird, erstelle ich dort einfach ein Object

{
id: null,
name: "Eva"
}

und sende das an den gleichen REST command.

Dieser macht dann den gleichen query

insert into user (id, name) values ($1, $2) on duplicate key update name = $2

du wirst es jetzt wahrscheinlich einen bug nennen, ich nenne es ein geniales feature, denn MySQL macht hier einen insert als ob keine Id angegeben wurde.
ich habe nach dem query also einen zweiten Eintrag in der dB mit id=2 und name=“Eva“.

ich muss also überhaupt keine Sonderbehandlung machen und brauche keine verschiedenen queries.

kurz, knackig und leicht zu verstehen

 

[akretschmer]

also so?

postgres=# create table bla(id serial primary key, t text);
CREATE TABLE
postgres=# create or replace function trg1() returns trigger as $$begin raise notice 'trigger'; insert into bla(id,t) values (default, new.t); return null;end;$$ language plpgsql;
CREATE FUNCTION
postgres=# create trigger trg1 before insert on bla for each row when (new.id is null) execute function trg1();
CREATE TRIGGER
postgres=# insert into bla(t) values ('max');
INSERT 0 1
postgres=# select * from bla;
 id |  t  
----+-----
  1 | max
(1 row)

postgres=# insert into bla(id,t) values (1,'moritz') on conflict (id) do update set t = excluded.t;
INSERT 0 1
postgres=# select * from bla;
 id |   t    
----+--------
  1 | moritz
(1 row)

postgres=# insert into bla(id,t) values (null,'eva') on conflict (id) do update set t = excluded.t;
NOTICE:  trigger
INSERT 0 0
postgres=# select * from bla;
 id |   t    
----+--------
  1 | moritz
  2 | eva
(2 rows)

postgres=#

Der konditionelle Trigger (mit WHEN-Condition) feuert, wenn id auf NULL ist und setzt die id dann auf default. Damit sollte sich das wie gezeigt analog zu MySQL verhalten.

 

[castorp]

du wirst es jetzt wahrscheinlich einen bug nennen,

Ist es auch.

Du schickst explizit einen NULL Wert an die Datenbank und die sagt. "Nö, Dein NULL mag ich nicht, ich denke mir was anders aus".

Letztendlich ist das auch nichts anders als wenn Du z.B. versuchst die Zahl 0 zu speichern, aber die Datenbank denkt sich, "42 ist eigentlich viel besser, das speichere ich jetzt".

Es gibt keine Datenbank ausser MySQL, die still und heimlich Werte des Benutzers ignoriert und einfach was anderes verwendet.

 

[dabadepdu]

Ich schließe mich da an. Dieser scheinbare Komfort ist ein Regelbruch. DB sind aber genau dazu da, Regeln einzuhalten und das zu garantieren. Also möchte ich eine DB, die da an gewissen Stellen ausnahmen macht? Nein! Und wenn man mal mit Ausnahmen angefangen hat, wie will man dann noch überblicken, was man eingehalten hat oder einhalten kann?

Positiv betrachtet:
Du kannst mit einem (einzigen) Trigger, den Du überall dort verdrahtest, wo Du es haben möchtest, dieses mySQL Verhalten nachbauen. Also falls es um "aufwandsarme" Migration eines Altsystems geht, wäre das ein ziemlich kleiner Aufwand, einen Trigger zu erstellen und ihn gezielt dort zu nutzen wo das gebraucht wird (Und dann unter klaren Bedingungen stattfindet).

Ich persönlich mag Sequenzen viel lieber und baue die als Default in den PK der Tabelle. Es ist nicht das gleiche wie ein Serial, aber auch der ist im Verhalten ziemlich nah an dem, was Du suchst, Du müsstest nur für den Fall NULL die Feldangabe weglassen. Das wäre ziemlich normal, naheliegent und transparent. (Und würde ggF. bereits durch den Restservice gemacht werden können)