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 DEFAULT 0,
    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-first 语法省略 SELECT *：

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

将 t2 的表结构复制到 t1：

CREATE TABLE t1 AS
    FROM t2
    LIMIT 0;

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

临时表

临时表的作用域是会话级别，这意味着只有创建它们的特定连接可以访问它们，并且当与 Goose 的连接关闭后，它们会被自动删除（例如与 PostgreSQL 类似）。

可以使用 CREATE TEMP TABLE 或 CREATE TEMPORARY TABLE 语句（见下图）创建临时表，它们属于 temp.main 模式。虽然不推荐，但它们的名称可以与常规数据库表重名。在这种情况下，名称解析会优先匹配临时表；要引用常规表，必须使用完整限定名，例如 memory.main.t1。

即使连接的是持久化 Goose，临时表也驻留在内存而非磁盘中；但如果设置了 temp_directory 配置，在内存受限时数据会溢写到磁盘。

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

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

允许临时表将超出内存的数据写入磁盘：

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

CREATE OR REPLACE

CREATE OR REPLACE 语法允许创建新表，或用新表覆盖已有表。这相当于先删除已有表，再创建新表的简写形式。

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

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)

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

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

该语法可以与 CSV 读取器、无需指定函数即可直接读取 CSV 文件的简写方式、FROM-first 语法 以及 HTTP(S) 支持 组合使用，从而写出如下简洁的 SQL 命令：

CREATE TABLE flights AS
    FROM 'https://pub.kumose.cc/goose/data/flights.csv';

CTAS 结构也可与 OR REPLACE 修饰符一起使用，形成 CREATE OR REPLACE TABLE ... AS 语句：

CREATE OR REPLACE TABLE flights AS
    FROM 'https://pub.kumose.cc/goose/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] 语法会创建生成列。这类列中的数据由其表达式生成，表达式可引用表中的其他列（普通列或生成列）。由于其值由计算产生，因此不能直接向这些列插入数据。

Goose 可以根据表达式返回类型推断生成列的类型。因此，在声明生成列时可以省略类型。也可以显式指定类型，但如果引用列的类型无法转换为生成列类型，插入可能会失败。

生成列有两种：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);

语法