Syntax error bei Regel erstellen (INSERT UPDATE)

[christofj]

Hallo zusammen,

ich habe ein Skript welches mir Daten in meine DB einfügt. (mit dem INSERT)
Jetzt möchte ich ein Regel erstellen die prüft ob ein Datensatz mit der ID und einem Messpunk schon vorhanden ist.
Wenn ja, soll UPDATE anstatt INSERT ausgeführt werden und der Messwert aktualisiert werden.
Mit pgAdmin4 sieht der SQL code so aus und bricht mit "ERROR: syntax error at or near ";""ab:

CREATE OR REPLACE RULE "Sensor_X_Update" AS
ON INSERT TO public."SensordatenTab_X_Werte"
WHERE new.Sensor_IDX = Sensor_IDX AND new.motionnumber = motionnumber
DO INSTEAD
UPDATE SensordatenTab_X_Werte;

Kann mir einer sagen was fehlt?

 

[akretschmer]

Rules gelten in der Community als deprecated, laß das mal lieber. Welche Version ist am Start? INSERT ON CONFLICT ist die bessere Lösung.

 

[christofj]

PG 10.
Ab welcher version gibt es INSERT ON CONFLICT?
Ich arbeite mich in DBs gerade erst ein.

Danke für den Tipp

 

[akretschmer]

test=# create table messung(station int primary key, wert int);
CREATE TABLE
test=*# insert into messung values (1,10) on conflict (station) do update set wert = excluded.wert;
INSERT 0 1
test=*# select * from messung ;
 station | wert
---------+------
       1 |   10
(1 row)

test=*# insert into messung values (2,20) on conflict (station) do update set wert = excluded.wert;
INSERT 0 1
test=*# insert into messung values (1,15) on conflict (station) do update set wert = excluded.wert;
INSERT 0 1
test=*# select * from messung ;
 station | wert
---------+------
       2 |   20
       1 |   15
(2 rows)

test=*#

Geht ab 10. Oder 9.6?

Ja, 9.6. Grad getestet.

 

[castorp]

INSERT ... ON CONFLICT wurde in 9.5 eingeführt: PostgreSQL: Documentation: 9.5: INSERT

 

[akretschmer]

INSERT ... ON CONFLICT wurde in 9.5 eingeführt:

... doch schon so lange ;-)

 

[christofj]

ok, Danke. Dann kann ich es in Jens Hartiwigs von 2001 nicht finden ;-)

 

[akretschmer]

LOL.

2001 erschien 7.1.

 

[christofj]

Hallo nochmal,

wie lässt sich die UPDATE Anweisung mittracken? Ich möchte wissen wie oft ein Messwert aktualisiert wurde. Ich dachte in dem ich eine Spalte in der Tabelle mit serial anlege und diese nur bei einem UPDATE aufgerufen wird. Geht das überhaupt so? Ich weiß nämlich nicht wie dazu die Syntax aussehen soll.

 

[castorp]

Mit einem "simplen" UPDATE:

update "SensordatenTab_X_Werte"
   set motionnumber = 42,
       change_number = change_number + 1
where "Sensor_IDX"  = 1;

Oder als Teil des INSERTs:

insert into "SensordatenTab_X_Werte"("Sensor_IDX", motionnumber)
values (1,42)
on conflict ("Sensor_IDX") do update
  set motionnumber = excluded.motionnumber,
      change_number = "SensordatenTab_X_Werte".change_number + 1;

change_number ist die Spalte die mittracken soll wie oft der Datensatz geändert wurde.

Übrigens: Du solltest doppelte Anführungszeichen für Tabellen- oder Spaltennamen vermeiden. Letztendlich hast Du damit nur mehr Probleme als sie Vorteile bringen (ich persönlich finde, dass sie überhaupt keine Vorteile bringen....)

 

[christofj]

Ok, danke für die schnelle Antwort.

 

[akretschmer]

Wenn das ein SERIAL wäre, dann würdest Du für jede Station eine eigene Sequenz brauchen - eher schlecht.

test=# create table messung(station int primary key, wert int, counter int default 0);
CREATE TABLE
test=*# create or replace function counter_inc() returns trigger as $$begin new.counter:=new.counter+1; return new; end; $$language plpgsql;
CREATE FUNCTION
test=*# create trigger trg_update before update on messung for each row execute procedure counter_inc();
CREATE TRIGGER
test=*# insert into messung (station, wert) values (2,20) on conflict (station) do update set wert = excluded.wert;
INSERT 0 1
test=*# select * from messung ;
 station | wert | counter
---------+------+---------
       2 |   20 |       0
(1 row)

test=*# insert into messung (station, wert) values (2,20) on conflict (station) do update set wert = excluded.wert;
INSERT 0 1
test=*# select * from messung ;
 station | wert | counter
---------+------+---------
       2 |   20 |       1
(1 row)

test=*# insert into messung (station, wert) values (2,20) on conflict (station) do update set wert = excluded.wert;
INSERT 0 1
test=*# select * from messung ;
 station | wert | counter
---------+------+---------
       2 |   20 |       2
(1 row)

test=*#