Ich finde, dass die Verwendung von beschreibbaren CTEs, um ein Upsert in PostgreSQL zu emulieren, eine recht elegante Lösung darstellt, bis wir in Postgres tatsächlich Upsert / Merge erhalten. (Siehe: Ссылка )
Allerdings gibt es ein Problem: Wie kann ich den Standardwert einfügen? Die Verwendung von NULL hilft natürlich nicht, da NULL explizit als NULL eingefügt wird, anders als beispielsweise bei MySQL. Ein Beispiel:
Ich möchte zum Beispiel, dass die Spalte legacy den Standardwert für die zweite VALUES -Zeile annimmt.
Ich habe ein paar Dinge ausprobiert, wie explizit DEFAULT in der VALUES-Liste zu verwenden, was nicht funktioniert, weil das CTE keine Ahnung hat, in was es eingefügt wird. Ich habe auch coalesce(col, DEFAULT) in der Einfügung versucht Aussage, die auch nicht zu funktionieren schien. Also, ist es möglich zu tun, was ich will?
Postgres 9.5 implementiert UPSERT . Siehe unten.
Das ist ein kniffliges Problem. Sie stoßen auf diese Einschränkung ( pro Dokumentation ):
In einer
VALUES-Liste, die auf der obersten Ebene einesINSERTerscheint, an Ausdruck kann durchDEFAULTersetzt werden, um das Ziel anzugeben Der Standardwert der Spalte sollte eingefügt werden.DEFAULTkann nicht verwendet werden, wennVALUESwird in anderen Kontexten angezeigt.
Kühne Betonung meiner. Standardwerte werden nicht ohne eine einzufügende Tabelle definiert. Daher gibt es keine direkte Lösung für Ihre Frage, aber es gibt eine Reihe möglicher alternativer Routen, abhängig von den genauen Anforderungen .
Sie konnten diese aus dem Systemkatalog % co_de abrufen % like @Patrick kommentierte oder von pg_attrdef . Beende die Anweisungen hier:
Aber dann haben Sie immer noch nur eine Liste von Zeilen mit einer Textdarstellung des Ausdrucks, um den Standardwert zu berechnen. Sie müssten Anweisungen dynamisch erstellen und ausführen, um Werte zu erhalten, mit denen Sie arbeiten können. Mühsam und unordentlich. Stattdessen können wir eingebaute Postgres-Funktionalität das für uns machen lassen :
Fügen Sie eine Dummy-Zeile ein und lassen Sie sie zur Verwendung der generierten Standardwerte zurückkehren:
%Vor%information_schema.columns oder STABLE Standardausdrücke Die meisten IMMUTABLE -Funktionen funktionieren genauso, aber es gibt keine Garantien. Die Funktionsfamilie VOLATILE gilt als stabil, da sich ihre Werte innerhalb einer Transaktion nicht ändern. current_timestamp (oder auf andere Standardvorgaben, die aus einer Sequenz gezeichnet werden). Aber das sollte kein Problem sein, weil Sie normalerweise nicht direkt in serial columns schreiben. Diese sollten nicht in serial Statements aufgeführt werden. INSERT Spalten: Die Sequenz wird immer noch um den einzelnen Aufruf erweitert, um eine Standardzeile zu erhalten, was zu einer Lücke in der Nummerierung führt. Auch das sollte kein Problem sein, da in serial columns generell Lücken zu erwarten sind. Zwei weitere Probleme können gelöst werden:
Wenn Sie Spalten definiert haben serial , müssen Sie Dummy-Werte einfügen und im Ergebnis durch NOT NULL ersetzen.
Wir möchten die Dummy-Zeile eigentlich nicht einfügen. Wir könnten später (in der gleichen Transaktion) löschen, aber das kann mehr Nebenwirkungen haben, wie Trigger NULL . Es gibt einen besseren Weg:
Klonen Sie eine temporäre Tabelle mit Spaltenstandards und fügen Sie sie in das ein:
%Vor%Gleiches Ergebnis, weniger Nebenwirkungen. Da Standardausdrücke wortwörtlich kopiert werden, zeichnet der Klon aus den gleichen Sequenzen, falls vorhanden. Aber andere Nebenwirkungen von der unerwünschten Reihe oder den Auslösern werden vollständig vermieden.
Kredit an Igor für die Idee:
ON DELETE Einschränkungen Sie müssten Dummy-Werte für NOT NULL Spalten angeben, weil ( pro Dokumentation ):
Nicht-Null-Bedingungen werden immer in die neue Tabelle kopiert.
Enthalten Sie entweder die in der NOT NULL -Anweisung oder (besser) beseitigen Sie die Einschränkungen:
Es gibt einen schnellen und schmutzigen Weg mit Superuser-Rechten:
%Vor%Es ist nur eine temporäre Tabelle ohne Daten und keinen anderen Zweck und wird am Ende der Transaktion gelöscht. Also ist die Abkürzung verlockend. Die Grundregel lautet jedoch: Manipulieren Sie niemals direkt Systemkataloge.
Schauen wir uns also einen sauberen Weg an :
Automatisieren Sie mit dynamischem SQL in einer INSERT -Anweisung. Sie benötigen nur die regulären Berechtigungen , die Sie garantiert haben, seit die gleiche Rolle die temporäre Tabelle erstellt hat.
Viel sauberer und immer noch sehr schnell.Führen Sie die Pflege mit dynamischen Befehlen aus und beachten Sie die SQL-Injection. Diese Aussage ist sicher. Ich habe mehrere verwandte Antworten mit mehr Erklärungen gepostet.
Sie benötigen nur DO , wenn gleichzeitige Transaktionen versuchen, in dieselbe Tabelle zu schreiben.
Bei Bedarf werden nur NULL-Werte in der Spalte LOCK in den Eingabezeilen für den Fall legacy ersetzt. Kann leicht für andere Spalten oder auch in INSERT erweitert werden. Zum Beispiel könnten Sie auch UPDATE bedingt verwenden: nur wenn der Eingabewert UPDATE ist. Ich habe eine Kommentarzeile zum NOT NULL hinzugefügt.
Nebenbei: Sie müssen -Werte nicht in einer anderen Zeile als der ersten in einem UPDATE -Ausdruck umwandeln, da Typen von der ersten -Zeile abgeleitet werden.
implementiert UPSERT mit VALUES . Dies vereinfacht die Bedienung weitgehend:
Wir können die INSERT .. ON CONFLICT .. DO NOTHING | UPDATE -Klausel direkt an VALUES anhängen, was das Schlüsselwort INSERT zulässt. Bei eindeutigen Verstößen gegen DEFAULT werden stattdessen Postgres-Updates durchgeführt. Wir können ausgeschlossene Zeilen in (id) verwenden. Das Handbuch:
Die
UPDATEundSETKlauseln inWHEREhaben Zugriff auf die vorhandene Zeile mit dem Namen der Tabelle (oder einem Alias) und Zeilen zum Einfügen mit der speziellen TabelleON CONFLICT DO UPDATEvorgeschlagen.
Und:
Beachten Sie, dass die Auswirkungen aller pro Zeile
excludedTrigger sind in ausgeschlossenen Werten reflektiert, da diese Effekte beigetragen haben könnten die Zeile wird von der Einfügung ausgeschlossen.
Sie haben verschiedene Optionen für BEFORE INSERT : Sie können ...
UPDATE -Klausel zum WHERE hinzu, um nur in ausgewählte Zeilen zu schreiben. UPDATE
COALESCE(l.legacy, EXCLUDED.legacy) lautet: NOT NULL
Aber es gibt keine Möglichkeit, COALESCE(EXCLUDED.legacy, l.legacy) -Werte und -Werte zu erkennen, die tatsächlich in DEFAULT bereitgestellt werden. Nur die resultierenden INSERT Zeilen sind sichtbar. Wenn Sie die Unterscheidung benötigen, greifen Sie auf die vorherige Lösung zurück, in der Sie beide zur Verfügung haben.
Tags und Links sql merge postgresql upsert sql-insert