Fehlerbehandlung "doppelter Schlüsselwert" in Verbindung mit COPY Befehl

Hallo!

Ich möchte eine CSV-Datei in eine PostgreSQL Tabelle übertragen, das ist soweit ja auch kein Problem. Ich lese die Datei einfach in einem Script per COPY Befehl ein. Das funktioniert auch wunderbar.

Die Quell CSV Dateienen werden wöchentlich automatisch auf das system gestellt und enthalten Messdaten mit Datumsangaben. Primärschlüssel in der Zieltabelle ist daher das Datumsfeld(datum+uhrzeit), da es zu jedem Datum nur einen Messwert geben kann.
Wie gesagt soweit alles kein Problem.

Allerdings wäre zu berücksichtigen dass unter Umständen(Fehler im Messgerät) eine CSV-Datei einmal noch ältere Datensätze enthält die in der Datenbank schon vorhanden sind. In diesem Fall würde der COPY Befehl ja abbrechen.
Ich habe mir jetzt gedacht wenn dies der Fall ist, fange ich die Exception ab und Lese die Datei zuerst in eine parallele Tabelle ein aus der dann die eigentliche Zieltabelle nur mit den dor noch nicht vorhanden Daten gefüllt wird.

Also kurz und stark vereinfacht:

BEGIN
  COPY tbl_messwerte FROM import.csv
EXEPTION 
  WHEN 'doppelter Schlüsselwert' THEN
  COPY tbl_messwerte_tmp FROM import.csv
  INSERT INTO tbl_messwerte SELECT tbl_messwerte_tmp WHERE schlüssel nicht doppelt
END

Da ich nicht so tief in PSQL drin bin ist die Frage, wäre das so praktikabel oder gibts da eine intelligentere/einfachere Lösung?
Ich hatte irgendwo mal gelesen, glaube hier im Forum, dass Exception Handling von der Performance her nicht das optimale wäre. Andererseits wäre zu bedenken, die Prozedur läuft vermutlich nur einmal je Woche und bei jeder datei handelt es sich um ca. 10.000 Datensätze. Es wären auch längere Zeiträume mit mehr Datenstätzen möglich, es sollten aber nicht mehr als 60.000 zeilen je Datei sein.

Man könnte sich ein Skript schreiben, das die Datei liest, prüft ob da schon was in der DB ist und wenn nicht, den Datensatz schreibt. Oder einfach nur den Fehler beim Schreiben händelt.

Grüssle Uwe

Danke für die Schnell Antwort!
Wie gesagt meine Kenntnisse in PostgreSQL halten sich leider in Grenzen, ich wüsste jetzt spontan nicht wie ich den Fehler direkt beim Schreiben behandeln soll (per Trigger?) - aber die Idee wäre sicherlich auch nicht verkehrt, einfach direkt beim reinschreiben in die Tabelle die doppelten Sätze zu verwerfen. Gibt es da eine praktikable Möglichkeit???



Mit Script schreiben dass die Datei liest und gegen die Datenbank prüft meinst du vermutlich ein externes Script, z.B. Shell oder Ähnliches - da fehlt mir aber leider das KnowHow - eine Lösung in Postgres wäre am einfachsten für mich.

Wenn du das in der DB lösen willst, würde ich die Daten immer erst in eine temporäre Tabelle lesen und dann per

 insert into <Tabelle> select * from <Temp_Tabelle> where [timestamp] not in (select [timestamp] from <Tabelle>)

in die Orginaltabelle schreiben. Pseudocode ohne jede Gewähr :wink:

Gruß Uwe

Ja, so hatte ich das ja quasi auch vor, allerdings nur dann, wenn der direkte Import aus der Datei fehlschlägt :slight_smile:

Da wäre jetzt die Frage, was performanter wäre - Generell erst in eine “Vorhof”-Tabelle und dann in die Zieltabelle oder nur bei Problemen. Wie gesagt im Regelfall sind keine Werte doppelt vorhanden. Nur falls das rückwirkend Daten nochmal übertragen werden müssen.

Einfach mit normalem SQL udn einer zusätzlichen Import-Tabelle, die den gleichen Aufbau wie die Ziel-Tabelle hat:

test=# select * from import;
 id | value
----+-------
  1 | val1
  2 | val2
  3 | val3
(3 rows)

test=*# select * from ziel;
 id | value
----+-------
(0 rows)

test=*# insert into ziel (select import.id, import.value from import left join ziel on (ziel.id=import.id) where ziel.id is null);
INSERT 0 3
test=*# truncate import;
TRUNCATE TABLE
test=*# copy import from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 3    val3
>> 4    val4
>> 5    val5
>> \.
test=*# insert into ziel (select import.id, import.value from import left join ziel on (ziel.id=import.id) where ziel.id is null);
INSERT 0 2
test=*# select * from ziel;
 id | value
----+-------
  1 | val1
  2 | val2
  3 | val3
  4 | val4
  5 | val5
(5 rows)

Andreas

So in etwa hatte ich das ja bisher. Nur bin ich erst in dem Fall dass der direkte Import auf die Nase fällt diesen Weg gegangen. Wäre es also besser die Fehlerbehandlung raus zu lassen und direkt über die zweite Tabelle zu gehen? Warum (man lernt ja bekanntlich gern dazu und ich weiss gerne warum ich etwas so oder so mache :wink: )?

@akretschmer

insert into ziel (select import.id, import.value from import left join ziel on (ziel.id=import.id) [B]where ziel.id is null[/B]);

Ich glaube ich verstehe den Join hier nicht ganz, speziell das ‘where ziel.id is null’ ich hätte das so gemacht:

  INSERT INTO ziel SELECT * FROM import WHERE NOT id IN (SELECT id FROM ziel);

Wo liegt hier genau der Unterschied, weshalb?

Das geht auch, wird aber intern in sehr, sehr viele OR-Bedingungen aufgelöst (zumindest in nicht ganz aktuellen PG-Versionen), was nicht unbedingt gut performt.

Der JOIN ist ein LEFT (OUTER), links sind also alle aus der import-Tabelle und rechts die aus Ziel. Interessant sind aber nur die, wo rechts, also in ziel, die Verbindungsspalte leer, also NULL ist.

test=*# select * from import ;
 id | value
----+-------
  1 | val1
  2 | val2
  3 | val3
(3 rows)

test=*# select * from ziel;
 id | value
----+-------
  3 | val3
(1 row)

test=*# select import.id, import.value, ziel.id from import left join ziel on (ziel.id=import.id);
 id | value | id
----+-------+----
  1 | val1  |
  2 | val2  |
  3 | val3  |  3
(3 rows)

test=*# select import.id, import.value, ziel.id from import left join ziel on (ziel.id=import.id) where ziel.id is null;
 id | value | id
----+-------+----
  1 | val1  |
  2 | val2  |
(2 rows)

Also, prinzipiell geht Dein Ansatz auch, aber in älteren Versionen (iirc unter 8.3) führt das intern zu sehr vielen einzelnen OR-Bedingungen, in neueren Versionen würde PG aus Deiner Version meine Version machen und abarbeiten.

Andreas

Alles klar, danke! Dann werde ich glaube ich hab’s Verstanden. Wir haben PostgreSQL 8.3.3 laufen, von daher wäre es ja dann im Endeffekt egal welche Version ich durchführe.

Und “Einfach mit normalem SQL” statt einer Fuction nehme ich jetzt mal an, weil reiner SQL Code “schneller” ist als die Function mit Exception Handling.

Danke für die Erläuterungen.

[edit]:
Etwas fällt mir da noch ein. Sollte eigentlich nicht vorkommen, aber angenommen in der Import Tabelle bzw. in der Quell-Datei wäre ein Wert doppelt, also doppelte ID, dann würde das auch auf die Nase fallen, oder? Also wenn in der Importtabelle der Primärschlüssel auf ID sitzt ja sowieso (unique_violation beim COPY schon), aber angenommen man spart sich den PK, würde das aber beim SQL INSERT spätestens knallen, oder?

Ja. Dann bist Du aber wieder exakt an dem Ausgangspunkt wie ganz am Anfang.

Andreas

Dann darfst Du es Dir bequem machen … das ist alles andere als zeitkritisch.

Es würde reichen, wenn du dir den constraint definierst und in einem before-insert trigger selbst per

select .. from .. where key = impor_id

prüfst ob du den constraint verletzen wuerdest. Wenn das der Fall ist, kannst du die aktuelle Row einfach verwerfen (die function des before-inser-trigger liefert NULL zurueck → Insert wird nicht ausgefuehrt)

Alles ohne Exception handling - wobei ich der Meinung bin, dass Du Dir auch dieses leisten darfst bei deiner Problemstellung

Wie gesagt zeitkritisch ist es nicht. Ich werds jetzt aber mit der zweiten Tabelle und rein SQL lösen. Da werden nach und nach noch mehr ähnliche Jobs dazu kommen, dass die Zeit dann irgendwann vielleicht doch ins Gewicht fällt.
Aber es geht ja auch generell ums Verständnis, was wie warum, von daher Danke für die Hilfe zusammen.