CREATE TABLE-Anweisung

Die CREATE TABLE-Anweisung legt eine Tabelle im Katalog an.

Beispiele

Eine Tabelle mit zwei ganzzahligen Spalten (i und j) anlegen:

CREATE TABLE t1 (i INTEGER, j INTEGER);

Eine Tabelle mit einem Primärschlüssel anlegen:

CREATE TABLE t1 (id INTEGER PRIMARY KEY, j VARCHAR);

Eine Tabelle mit einem zusammengesetzten Primärschlüssel anlegen:

CREATE TABLE t1 (id INTEGER, j VARCHAR, PRIMARY KEY (id, j));

Eine Tabelle mit verschiedenen Typen, Constraints und Standardwerten anlegen:

CREATE TABLE t1 (
    i INTEGER NOT NULL DEFAULT 0,
    decimalnr DOUBLE CHECK (decimalnr < 10),
    date DATE UNIQUE,
    time TIMESTAMP
);

Eine Tabelle mit CREATE TABLE ... AS SELECT (CTAS) anlegen:

CREATE TABLE t1 AS
    SELECT 42 AS i, 84 AS j;

Eine Tabelle aus einer CSV-Datei anlegen (Spaltennamen und Typen automatisch erkennen):

CREATE TABLE t1 AS
    SELECT *
    FROM read_csv('path/file.csv');

Wir können die FROM-first-Syntax verwenden, um SELECT * wegzulassen:

CREATE TABLE t1 AS
    FROM read_csv('path/file.csv');

Das Schema von t2 nach t1 kopieren:

CREATE TABLE t1 AS
    FROM t2
    LIMIT 0;

Beachten Sie, dass nur die Spaltennamen und Typen nach t1 kopiert werden; andere Informationen (Indexes, Constraints, Standardwerte usw.) werden nicht kopiert.

Temporäre Tabellen

Temporäre Tabellen sind sitzungsbezogen, das heißt, nur die Verbindung, die sie angelegt hat, kann auf sie zugreifen, und sobald die Verbindung zu DuckDB geschlossen wird, werden sie automatisch gelöscht (ähnlich wie zum Beispiel in PostgreSQL).

Sie können mit der Anweisung CREATE TEMP TABLE oder CREATE TEMPORARY TABLE angelegt werden (siehe Diagramm unten) und gehören zum Schema temp.main. Obwohl davon abgeraten wird, können ihre Namen mit den Namen regulärer Datenbanktabellen überlappen. In diesen Fällen haben temporäre Tabellen bei der Namensauflösung Vorrang, und ein vollständig qualifizierter Name ist erforderlich, um auf eine reguläre Tabelle zu verweisen, z. B. memory.main.t1.

Temporäre Tabellen liegen im Speicher statt auf der Festplatte, auch wenn Sie sich mit einer persistenten DuckDB verbinden. Ist jedoch die Konfiguration temp_directory gesetzt, werden Daten auf die Festplatte ausgelagert, wenn der Speicher knapp wird.

Eine temporäre Tabelle aus einer CSV-Datei anlegen (Spaltennamen und Typen automatisch erkennen):

CREATE TEMP TABLE t1 AS
    SELECT *
    FROM read_csv('path/file.csv');

Temporären Tabellen erlauben, überschüssigen Speicher auf die Festplatte auszulagern:

SET temp_directory = '/path/to/directory/';

CREATE OR REPLACE

Die Syntax CREATE OR REPLACE erlaubt das Anlegen einer neuen Tabelle oder das Überschreiben einer vorhandenen Tabelle durch die neue. Das ist eine Kurzform dafür, die vorhandene Tabelle zu löschen und dann die neue anzulegen.

Eine Tabelle mit zwei ganzzahligen Spalten (i und j) anlegen, auch wenn t1 bereits existiert:

CREATE OR REPLACE TABLE t1 (i INTEGER, j INTEGER);

IF NOT EXISTS

Die Syntax IF NOT EXISTS legt die Tabelle nur an, wenn sie noch nicht existiert. Existiert die Tabelle bereits, wird nichts unternommen und die vorhandene Tabelle bleibt in der Datenbank.

Eine Tabelle mit zwei ganzzahligen Spalten (i und j) nur anlegen, wenn t1 noch nicht existiert:

CREATE TABLE IF NOT EXISTS t1 (i INTEGER, j INTEGER);

CREATE TABLE ... AS SELECT (CTAS)

DuckDB unterstützt die Syntax CREATE TABLE ... AS SELECT, auch bekannt als „CTAS“:

CREATE TABLE nums AS
    SELECT i
    FROM range(0, 3) t(i);

Diese Syntax kann mit dem CSV-Reader, der Kurzform zum direkten Lesen aus CSV-Dateien ohne Angabe einer Funktion, der FROM-first-Syntax und der HTTP(S)-Unterstützung kombiniert werden und ergibt knappe SQL-Befehle wie den folgenden:

CREATE TABLE flights AS
    FROM 'https://duckdb.org/data/flights.csv';

Das CTAS-Konstrukt funktioniert auch mit dem Modifikator OR REPLACE und ergibt Anweisungen CREATE OR REPLACE TABLE ... AS:

CREATE OR REPLACE TABLE flights AS
    FROM 'https://duckdb.org/data/flights.csv';

Das Schema kopieren

Sie können eine Kopie des Tabellenschemas (nur Spaltennamen und Typen) wie folgt anlegen:

CREATE TABLE t1 AS
    FROM t2
    WITH NO DATA;

Oder:

CREATE TABLE t1 AS
    FROM t2
    LIMIT 0;

Es ist nicht möglich, Tabellen mit CTAS-Anweisungen mit Constraints (Primärschlüssel, Check-Constraints usw.) anzulegen.

Check-Constraints

Ein CHECK-Constraint ist ein Ausdruck, der von den Werten jeder Zeile der Tabelle erfüllt werden muss.

CREATE TABLE t1 (
    id INTEGER PRIMARY KEY,
    percentage INTEGER CHECK (0 <= percentage AND percentage <= 100)
);
INSERT INTO t1 VALUES (1, 5);
INSERT INTO t1 VALUES (2, -1);

Constraint Error:
CHECK constraint failed: t1

INSERT INTO t1 VALUES (3, 101);

Constraint Error:
CHECK constraint failed: t1

CREATE TABLE t2 (id INTEGER PRIMARY KEY, x INTEGER, y INTEGER CHECK (x < y));
INSERT INTO t2 VALUES (1, 5, 10);
INSERT INTO t2 VALUES (2, 5, 3);

Constraint Error:
CHECK constraint failed: t2

CHECK-Constraints können auch als Teil der CONSTRAINTS-Klausel hinzugefügt werden:

CREATE TABLE t3 (
    id INTEGER PRIMARY KEY,
    x INTEGER,
    y INTEGER,
    CONSTRAINT x_smaller_than_y CHECK (x < y)
);
INSERT INTO t3 VALUES (1, 5, 10);
INSERT INTO t3 VALUES (2, 5, 3);

Constraint Error:
CHECK constraint failed: t3

Fremdschlüssel-Constraints

Ein FOREIGN KEY ist eine Spalte (oder ein Satz von Spalten), die auf den Primärschlüssel einer anderen Tabelle verweist. Fremdschlüssel prüfen die referenzielle Integrität, d. h. der referenzierte Primärschlüssel muss beim Einfügen in der anderen Tabelle existieren.

CREATE TABLE t1 (id INTEGER PRIMARY KEY, j VARCHAR);
CREATE TABLE t2 (
    id INTEGER PRIMARY KEY,
    t1_id INTEGER,
    FOREIGN KEY (t1_id) REFERENCES t1 (id)
);

Beispiel:

INSERT INTO t1 VALUES (1, 'a');
INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 2);

Constraint Error:
Violates foreign key constraint because key "id: 2" does not exist in the referenced table

Fremdschlüssel können auf zusammengesetzten Primärschlüsseln definiert werden:

CREATE TABLE t3 (id INTEGER, j VARCHAR, PRIMARY KEY (id, j));
CREATE TABLE t4 (
    id INTEGER PRIMARY KEY, t3_id INTEGER, t3_j VARCHAR,
    FOREIGN KEY (t3_id, t3_j) REFERENCES t3(id, j)
);

Beispiel:

INSERT INTO t3 VALUES (1, 'a');
INSERT INTO t4 VALUES (1, 1, 'a');
INSERT INTO t4 VALUES (2, 1, 'b');

Constraint Error:
Violates foreign key constraint because key "id: 1, j: b" does not exist in the referenced table

Fremdschlüssel können auch auf eindeutigen Spalten definiert werden:

CREATE TABLE t5 (id INTEGER UNIQUE, j VARCHAR);
CREATE TABLE t6 (
    id INTEGER PRIMARY KEY,
    t5_id INTEGER,
    FOREIGN KEY (t5_id) REFERENCES t5(id)
);

Einschränkungen

Fremdschlüssel haben die folgenden Einschränkungen.

Fremdschlüssel mit kaskadierendem Löschen (FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE) werden nicht unterstützt.

Das Einfügen in Tabellen mit selbstreferenzierenden Fremdschlüsseln wird derzeit nicht unterstützt und führt zu folgendem Fehler:

Constraint Error:
Violates foreign key constraint because key "..." does not exist in the referenced table.

Generierte Spalten

Die Syntax [type] [GENERATED ALWAYS] AS (expr) [VIRTUAL|STORED] legt eine generierte Spalte an. Die Daten in dieser Art von Spalte werden aus ihrem Ausdruck erzeugt, der andere (reguläre oder generierte) Spalten der Tabelle referenzieren kann. Da sie durch Berechnungen entstehen, können diese Spalten nicht direkt beschrieben werden.

DuckDB kann den Typ der generierten Spalte anhand des Rückgabetyps des Ausdrucks ableiten. Dadurch können Sie den Typ beim Deklarieren einer generierten Spalte weglassen. Es ist möglich, einen Typ explizit festzulegen, aber Einfügungen in die referenzierten Spalten können fehlschlagen, wenn der Typ nicht in den Typ der generierten Spalte gecastet werden kann.

Generierte Spalten gibt es in zwei Varianten: VIRTUAL und STORED. Die Daten virtueller generierter Spalten werden nicht auf der Festplatte gespeichert, sondern bei jeder Referenz der Spalte (über eine Select-Anweisung) aus dem Ausdruck berechnet.

Die Daten gespeicherter generierter Spalten werden auf der Festplatte gespeichert und jedes Mal berechnet, wenn sich die Daten ihrer Abhängigkeiten ändern (über eine INSERT- / UPDATE- / DROP-Anweisung).

Derzeit wird nur die Variante VIRTUAL unterstützt, und sie ist auch die Standardoption, wenn das letzte Feld leer gelassen wird.

Die einfachste Syntax für eine generierte Spalte:

Der Typ wird aus dem Ausdruck abgeleitet, und die Variante ist standardmäßig VIRTUAL:

CREATE TABLE t1 (x FLOAT, two_x AS (2 * x));

Dieselbe generierte Spalte vollständig angegeben:

CREATE TABLE t1 (x FLOAT, two_x FLOAT GENERATED ALWAYS AS (2 * x) VIRTUAL);

Syntax