CREATE TABLE 语句 – DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / 语句

CREATE TABLE 语句

CREATE TABLE 语句用于在目录中创建表。

示例

创建一个包含两个整型列（i 和 j）的表

CREATE TABLE t1 (i INTEGER, j INTEGER);

创建一个带有主键的表

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

创建一个带有复合主键的表

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

创建一个带有各种不同类型和约束的表

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

使用 CREATE TABLE ... AS SELECT (CTAS) 创建表

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

从 CSV 文件创建表（自动检测列名和类型）

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

我们可以使用 FROM-优先语法来省略 SELECT *

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

将 t2 的模式复制到 t1

CREATE TABLE t1 AS
    FROM t2
    LIMIT 0;

请注意，只有列名和类型被复制到 t2，其他信息（索引、约束、默认值等）不会被复制。

可以使用 CREATE TEMP TABLE 或 CREATE TEMPORARY TABLE 语句创建临时表（参见下图）。临时表是会话范围的（例如类似于 PostgreSQL），这意味着只有创建它们的特定连接才能访问它们，并且一旦与 DuckDB 的连接关闭，它们将自动被删除。临时表驻留在内存中而不是磁盘上（即使连接到持久化的 DuckDB），但如果连接时或使用 SET 命令设置了 temp_directory 配置，则当内存受限时数据将被溢写到磁盘。

从 CSV 文件创建临时表（自动检测列名和类型）

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

允许临时表将多余内存卸载到磁盘

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

临时表是 temp.main 模式的一部分。虽然不推荐，但它们的名称可能与常规数据库表的名称重叠。在这种情况下，请使用它们的完全限定名称（例如 temp.main.t1）进行消歧。

`CREATE OR REPLACE`

CREATE OR REPLACE 语法允许创建新表或用新表覆盖现有表。这是删除现有表然后创建新表的简写形式。

创建一个包含两个整型列（i 和 j）的表，即使 t1 已存在

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

`IF NOT EXISTS`

IF NOT EXISTS 语法只会在表不存在时才继续创建表。如果表已存在，则不会执行任何操作，现有表将保留在数据库中。

仅当 t1 尚不存在时，才创建一个包含两个整型列（i 和 j）的表

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

`CREATE TABLE ... AS SELECT` (CTAS)

DuckDB 支持 CREATE TABLE ... AS SELECT 语法，也称为“CTAS”

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

此语法可以与 CSV 读取器、无需指定函数即可直接从 CSV 文件读取的简写形式、FROM-优先语法以及 HTTP(S) 支持结合使用，从而生成如下所示的简洁 SQL 命令

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

CTAS 构造也适用于 OR REPLACE 修饰符，从而生成 CREATE OR REPLACE TABLE ... AS 语句

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

复制模式

您可以按如下方式创建表的模式副本（仅包含列名和类型）

CREATE TABLE t1 AS
    FROM t2
    WITH NO DATA;

或

CREATE TABLE t1 AS
    FROM t2
    LIMIT 0;

无法使用带有约束（主键、检查约束等）的 CTAS 语句创建表。

检查约束

CHECK 约束是一个表达式，表中每一行的值都必须满足该表达式。

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 子句的一部分添加

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

外键约束

FOREIGN KEY 是一个引用另一个表主键的列（或列的集合）。外键检查参照完整性，即在插入时，引用的主键必须在另一个表中存在。

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)
);

示例

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

外键可以在复合主键上定义

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)
);

示例

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

外键也可以在唯一列上定义

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)
);

限制

外键有以下限制。

不支持带有级联删除的外键（FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE）。

目前不支持向具有自引用外键的表中插入数据，这将导致以下错误：

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

生成列

[type] [GENERATED ALWAYS] AS (expr) [VIRTUAL|STORED] 语法将创建一个生成列。此类列中的数据由其表达式生成，该表达式可以引用表的其他（常规或生成）列。由于它们是由计算生成的，因此不能直接向这些列插入数据。

DuckDB 可以根据表达式的返回类型推断生成列的类型。这允许您在声明生成列时省略类型。可以显式设置类型，但如果类型无法转换为生成列的类型，则插入到引用列中可能会失败。

生成列有两种类型：VIRTUAL 和 STORED。虚拟生成列的数据不存储在磁盘上，而是每次引用该列时（通过 select 语句）根据表达式计算。

存储生成列的数据存储在磁盘上，并在其依赖项的数据发生变化时（通过 INSERT / UPDATE / DROP 语句）每次计算。

目前，只支持 VIRTUAL 类型，如果最后一个字段留空，它也是默认选项。

生成列的最简单语法

类型从表达式中推导，变体默认为 VIRTUAL

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

为完整性考虑，完整指定相同的生成列

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

示例

临时表