INSERT-Anweisung

Die INSERT-Anweisung fügt neue Daten in eine Tabelle ein.

Beispiele

Die Werte 1, 2, 3 in tbl einfügen:

INSERT INTO tbl
    VALUES (1), (2), (3);

Das Ergebnis einer Abfrage in eine Tabelle einfügen:

INSERT INTO tbl
    SELECT * FROM other_tbl;

Werte in die Spalte i einfügen und den Standardwert in die anderen Spalten einfügen:

INSERT INTO tbl (i)
    VALUES (1), (2), (3);

Den Standardwert explizit in eine Spalte einfügen:

INSERT INTO tbl (i)
    VALUES (1), (DEFAULT), (3);

Unter der Annahme, dass tbl einen Primärschlüssel-/Unique-Constraint hat, bei Konflikt nichts tun:

INSERT OR IGNORE INTO tbl (i)
    VALUES (1);

Oder die Tabelle stattdessen mit den neuen Werten aktualisieren:

INSERT OR REPLACE INTO tbl (i)
    VALUES (1);

Syntax

INSERT INTO fügt neue Zeilen in eine Tabelle ein. Man kann eine oder mehrere durch Wertausdrücke angegebene Zeilen einfügen oder null oder mehr Zeilen, die aus einer Abfrage resultieren.

Einfügereihenfolge der Spalten

Es ist möglich, eine optionale Einfügereihenfolge der Spalten anzugeben; das kann entweder BY POSITION (der Standard) oder BY NAME sein. Jede Spalte, die nicht in der expliziten oder impliziten Spaltenliste vorhanden ist, wird mit einem Standardwert gefüllt, entweder ihrem deklarierten Standardwert oder NULL, wenn keiner vorhanden ist.

Ist der Ausdruck einer Spalte nicht vom korrekten Datentyp, wird eine automatische Typumwandlung versucht.

INSERT INTO ... [BY POSITION]

Die Reihenfolge, in der Werte in die Spalten der Tabelle eingefügt werden, wird durch die Reihenfolge bestimmt, in der die Spalten deklariert wurden. Das heißt, die von der VALUES-Klausel oder der Abfrage gelieferten Werte werden von links nach rechts der Spaltenliste zugeordnet. Das ist die Standardoption, die explizit mit der Option BY POSITION angegeben werden kann. Zum Beispiel:

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl
    VALUES (5, 42);

Die Angabe von BY POSITION ist optional und entspricht dem Standardverhalten:

INSERT INTO tbl
    BY POSITION
    VALUES (5, 42);

Um eine andere Reihenfolge zu verwenden, können Spaltennamen als Teil des Ziels angegeben werden, zum Beispiel:

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl (b, a)
    VALUES (5, 42);

Das Hinzufügen von BY POSITION ergibt dasselbe Verhalten:

INSERT INTO tbl
    BY POSITION (b, a)
    VALUES (5, 42);

Das fügt 5 in b und 42 in a ein.

INSERT INTO ... BY NAME

Mit dem Modifikator BY NAME werden die Namen der Spaltenliste der SELECT-Anweisung mit den Spaltennamen der Tabelle abgeglichen, um die Reihenfolge zu bestimmen, in der Werte in die Tabelle eingefügt werden sollen. Das erlaubt das Einfügen auch dann, wenn die Reihenfolge der Spalten in der Tabelle von der Reihenfolge der Werte in der SELECT-Anweisung abweicht oder bestimmte Spalten fehlen.

Zum Beispiel:

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a);
INSERT INTO tbl BY NAME (SELECT 22 AS b);
SELECT * FROM tbl;

a	b
32	42
NULL	22

Wichtig ist, dass bei INSERT INTO ... BY NAME die in der SELECT-Anweisung angegebenen Spaltennamen mit den Spaltennamen der Tabelle übereinstimmen müssen. Ist ein Spaltenname falsch geschrieben oder existiert er nicht in der Tabelle, tritt ein Fehler auf. In der SELECT-Anweisung fehlende Spalten werden mit dem Standardwert gefüllt.

ON CONFLICT-Klausel

Eine ON CONFLICT-Klausel kann verwendet werden, um bei Konflikten aus UNIQUE- oder PRIMARY KEY-Constraints eine bestimmte Aktion auszuführen. Ein Beispiel für einen solchen Konflikt zeigt das folgende Beispiel:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84);

Das wirft einen Fehler:

Constraint Error:
Duplicate key "i: 1" violates primary key constraint.

Die Tabelle enthält die zuerst eingefügte Zeile:

SELECT * FROM tbl;

i	j
1	42

Diese Fehlermeldungen können vermieden werden, indem Konflikte getrennt behandelt werden. DuckDB unterstützt zwei solche Klauseln: ON CONFLICT DO NOTHING und ON CONFLICT DO UPDATE SET ....

DO NOTHING-Klausel

Die DO NOTHING-Klausel bewirkt, dass der/die Fehler ignoriert werden und die Werte weder eingefügt noch aktualisiert werden. Zum Beispiel:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO NOTHING;

Diese Anweisungen werden erfolgreich abgeschlossen und lassen die Tabelle mit der Zeile <i: 1, j: 42> zurück.

INSERT OR IGNORE INTO

Die Anweisung INSERT OR IGNORE INTO ... ist eine kürzere Syntax-Alternative zu INSERT INTO ... ON CONFLICT DO NOTHING. Zum Beispiel sind die folgenden Anweisungen gleichwertig:

INSERT OR IGNORE INTO tbl
    VALUES (1, 84);
INSERT INTO tbl
    VALUES (1, 84) ON CONFLICT DO NOTHING;

DO UPDATE-Klausel (Upsert)

Die DO UPDATE-Klausel bewirkt, dass das INSERT stattdessen zu einem UPDATE der konfliktierenden Zeile(n) wird. Die folgenden SET-Ausdrücke bestimmen, wie diese Zeilen aktualisiert werden. Die Ausdrücke können die besondere virtuelle Tabelle EXCLUDED verwenden, die die konfliktierenden Werte für die Zeile enthält. Optional können Sie eine zusätzliche WHERE-Klausel angeben, die bestimmte Zeilen vom Update ausschließen kann. Die Konflikte, die diese Bedingung nicht erfüllen, werden stattdessen ignoriert.

Weil wir eine Möglichkeit brauchen, sowohl auf das einzufügende als auch auf das vorhandene Tupel zu verweisen, führen wir den besonderen Qualifizierer EXCLUDED ein. Ist der Qualifizierer EXCLUDED angegeben, bezieht sich die Referenz auf das einzufügende Tupel, andernfalls auf das vorhandene Tupel. Dieser besondere Qualifizierer kann in den WHERE-Klauseln und SET-Ausdrücken der ON CONFLICT-Klausel verwendet werden.

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl VALUES (1, 42);
INSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Beispiele

Ein Beispiel mit DO UPDATE ist das folgende:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	84

Das Umordnen von Spalten und die Verwendung von BY NAME sind ebenfalls möglich:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl (j, i)
    VALUES (168, 1)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl
    BY NAME (SELECT 1 AS i, 336 AS j)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	336

INSERT OR REPLACE INTO

Die Anweisung INSERT OR REPLACE INTO ... ist eine kürzere Syntax-Alternative zu INSERT INTO ... DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2, .... Das heißt, sie aktualisiert jede Spalte der vorhandenen Zeile auf die neuen Werte der einzufügenden Zeile. Zum Beispiel, gegeben die folgende Eingabetabelle:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);

Diese Anweisungen sind gleichwertig:

INSERT OR REPLACE INTO tbl
    VALUES (1, 84);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl (j, i)
    VALUES (84, 1)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl BY NAME
    (SELECT 84 AS j, 1 AS i)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Einschränkungen

Wenn die Klausel ON CONFLICT ... DO UPDATE verwendet wird und ein Konflikt auftritt, weist DuckDB intern den von dem Konflikt nicht betroffenen Spalten der Zeile NULL-Werte zu und weist anschließend ihre Werte erneut zu. Verwenden die betroffenen Spalten einen NOT NULL-Constraint, löst das einen Fehler NOT NULL constraint failed aus. Zum Beispiel:

CREATE TABLE t1 (id INTEGER PRIMARY KEY, val1 DOUBLE, val2 DOUBLE NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, val1 DOUBLE);
INSERT INTO t1
    VALUES (1, 2, 3);
INSERT INTO t2
    VALUES (1, 5);

INSERT INTO t1 BY NAME (SELECT id, val1 FROM t2)
    ON CONFLICT DO UPDATE
    SET val1 = EXCLUDED.val1;

Das schlägt mit folgendem Fehler fehl:

Constraint Error:
NOT NULL constraint failed: t1.val2

Zusammengesetzter Primärschlüssel

Wenn mehrere Spalten Teil des Eindeutigkeits-Constraints sein müssen, verwenden Sie eine einzelne PRIMARY KEY-Klausel, die alle relevanten Spalten enthält:

CREATE TABLE t1 (id1 INTEGER, id2 INTEGER, val1 DOUBLE, PRIMARY KEY (id1, id2));
INSERT OR REPLACE INTO t1
    VALUES (1, 2, 3);
INSERT OR REPLACE INTO t1
    VALUES (1, 2, 4);

Ein Konfliktziel definieren

Ein Konfliktziel kann als ON CONFLICT (conflict_target) angegeben werden. Das ist eine Gruppe von Spalten, auf denen ein Index oder ein Unique-/Schlüssel-Constraint definiert ist. Wird das Konfliktziel weggelassen, werden der/die PRIMARY KEY-Constraint(s) der Tabelle angesprochen.

Die Angabe eines Konfliktziels ist optional, außer bei Verwendung von DO UPDATE und wenn mehrere Unique-/Primärschlüssel-Constraints auf der Tabelle vorhanden sind.

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl
    VALUES (1, 20, 300);
SELECT * FROM tbl;

i	j	k
1	20	300

INSERT INTO tbl
    VALUES (1, 40, 700)
    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k;

i	j	k
1	20	1400

INSERT INTO tbl
    VALUES (1, 20, 900)
    ON CONFLICT (j) DO UPDATE SET k = 5 * EXCLUDED.k;

i	j	k
1	20	4500

Wenn ein Konfliktziel angegeben ist, können Sie das weiter mit einer WHERE-Klausel filtern, die von allen Konflikten erfüllt werden muss.

INSERT INTO tbl
    VALUES (1, 40, 700)
    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k WHERE k < 100;

RETURNING-Klausel

Die RETURNING-Klausel kann verwendet werden, um den Inhalt der eingefügten Zeilen zurückzugeben. Das kann nützlich sein, wenn einige Spalten beim Einfügen berechnet werden. Wenn die Tabelle zum Beispiel einen automatisch inkrementierenden Primärschlüssel enthält, enthält die RETURNING-Klausel den automatisch erzeugten Primärschlüssel. Das ist auch bei generierten Spalten nützlich.

Einige oder alle Spalten können explizit zur Rückgabe ausgewählt und optional mit Aliassen umbenannt werden. Statt einfach eine Spalte zurückzugeben, können auch beliebige nicht-aggregierende Ausdrücke zurückgegeben werden. Alle Spalten können mit dem Ausdruck * zurückgegeben werden, und Spalten oder Ausdrücke können zusätzlich zu allen von * zurückgegebenen Spalten zurückgegeben werden.

Zum Beispiel:

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1
    SELECT 42
    RETURNING *;

i
42

Ein komplexeres Beispiel, das einen Ausdruck in der RETURNING-Klausel enthält:

CREATE TABLE t2 (i INTEGER, j INTEGER);
INSERT INTO t2
    SELECT 2 AS i, 3 AS j
    RETURNING *, i * j AS i_times_j;

i	j	i_times_j
2	3	6

Das nächste Beispiel zeigt eine Situation, in der die RETURNING-Klausel hilfreicher ist. Zuerst wird eine Tabelle mit einer Primärschlüsselspalte angelegt. Dann wird eine Sequenz angelegt, damit dieser Primärschlüssel beim Einfügen neuer Zeilen hochgezählt werden kann. Beim Einfügen in die Tabelle kennen wir die von der Sequenz erzeugten Werte noch nicht, daher ist es wertvoll, sie zurückzugeben. Weitere Informationen finden Sie auf der Seite CREATE SEQUENCE.

CREATE TABLE t3 (i INTEGER PRIMARY KEY, j INTEGER);
CREATE SEQUENCE 't3_key';
INSERT INTO t3
    SELECT nextval('t3_key') AS i, 42 AS j
    UNION ALL
    SELECT nextval('t3_key') AS i, 43 AS j
    RETURNING *;

i	j
1	42
2	43