这是本节的多页打印视图。点击此处打印.

CREATE TABLE

1: Column-definition
2: Column-name-list
3: Column-constraint
4: 分区子句
5: Table-constraint

在逻辑架构中创建一个表。

语法

使用列定义创建：

CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table
   ( column-definition[,...] [, table-constraint [,...]] )
   [ ORDER BY column[,...] ]
   [ segmentation-spec ]
   [ KSAFE [safety] ]
   [ partition-clause]
   [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
   [ DISK_QUOTA quota ]

从其他表创建：


CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table
   { AS-clause | LIKE-clause }
   [ DISK_QUOTA quota ]

AS 子句：

[ ( column-name-list ) ]
[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
AS  [ /*+ LABEL */ ] [ AT {{< codevar >}}epoch{{< /codevar >}} ] query [ ENCODED BY column-ref-list ] [ segmentation-spec ]

LIKE 子句：

LIKE [[database.]schema.]existing-table
  [ {INCLUDING | EXCLUDING} PROJECTIONS ]
  [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]

参数

IF NOT EXISTS

如果存在同名对象，请不要创建它并继续。如果忽略此选项且对象存在，Vertica 将生成 ROLLBACK 错误消息。在上述两种情况下，如果对象已经存在，则不会创建它。

IF NOT EXISTS 子句适用于以下 SQL 脚本：可在某个对象不存在时创建该对象。

请参阅 ON_ERROR_STOP 了解相关信息。

[database.]schema

数据库和架构。默认架构为 public。如果指定一个数据库，它必须是当前数据库。

表

要创建的表的名称，在架构的所有序列、表、投影、视图和模型的名称中必须是唯一的。

column-definition

列名称、数据类型和可选约束。一个表最多可以有 9800 列。表中至少有一列必须是标量类型或原生数组。

table-constraint

表级别约束，与列约束相反。

ORDER BY column[,...]

对外部表无效，指定 SELECT 列表中的列，基于该列表对为此表自动创建的超投影进行排序。ORDER BY 子句不能包含限定符 ASC 或 DESC。Vertica 始终按升序存储投影数据。

如果省略 ORDER BY 子句，Vertica 将使用 SELECT 列表顺序作为投影排序顺序。

segmentation-spec

对外部表无效，指定如何为此表的自动投影分发数据。提供以下子句之一：

hash‑segmentation‑clause：指定将数据均匀分段并分布在群集节点上。Vertica 建议对大表进行分段。
unsegmented‑clause：指定以创建未分段的投影。

如果省略此子句，Vertica 将生成自动投影，并使用默认哈希分段。

KSAFE [safety]

对外部表无效，指定为此表创建的自动投影的 K-safety，其中 k‑num 必须等于或大于系统 K-safety。如果您省略此选项，投影将使用系统 K-safety 水平。

partition-clause

对外部表无效，通过 PARTITION BY 子句对表数据存储进行逻辑划分：

PARTITION BY partition-expression
  [ GROUP BY group-expression ] [ ACTIVEPARTITIONCOUNT integer ]

column-name-list

只有从查询 ( AS query) 创建表时有效，定义映射到查询输出的列名称。如果忽略此列表，Vertica 将使用查询输出列名称。

该子句和 ENCODED BY 子句互斥。列名称列表对外部表无效。

column-name-list 中的名称必须与查询列的个数相同。

例如：

CREATE TABLE customer_occupations (name, profession)
   AS SELECT customer_name, occupation FROM customer_dimension;

{INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES

默认继承此表的架构权限：

INCLUDE PRIVILEGES 指定在其架构上设置的表继承权限。如果启用了架构权限继承，则这是默认行为。
EXCLUDE PRIVILEGES 禁用从架构继承权限。

有关详细信息，请参阅继承的权限。

AS query

根据查询结果创建并加载一个表，按如下方式指定：


AS  [ /*+ LABEL */ ] [ AT {{< codevar >}}epoch{{< /codevar >}} ] query

查询不能包含复杂类型的列。

ENCODED BY column-ref-list

该列表中的列来自源表且以逗号分隔，其中每列由以下一个或两个编码选项限定：

ACCESSRANK integer：覆盖列的默认访问等级，对于确定列访问的优先级很有用。请参阅确定列访问速度的优先级。
ENCODING encoding-type: 指定要在列上使用的编码的类型。默认编码类型为“AUTO”。

此选项和 column-name-list 是互斥的。此选项对于外部表无效。

LIKE existing-table

通过复制现有表创建表。您可以使用以下选项之一限定 LIKE 子句：

EXCLUDING PROJECTIONS（默认值）：不要从源表中复制投影。
INCLUDING PROJECTIONS：从新表的源表中复制当前投影。
{INCLUDE|EXCLUDE} [SCHEMA] PRIVILEGES：请参阅以上描述）。

DISK_QUOTA配额

字符串，一个整数，后跟支持的单位：K、M、G 或 T。导致表的使用率超出设置的配额的数据加载、DML 和 ILM 操作失败。有关详细信息，请参阅磁盘配额。

如果未指定，则表没有配额。

特权

超级用户可以设置磁盘配额。

非超级用户：

对表架构的 CREATE 权限
如果创建包含命名序列的表：
- 对于序列对象的 SELECT 权限
- 对于序列架构的 USAGE 权限
如果使用 LIKE 子句创建表，则为源表所有者

复杂类型的限制

原生表中使用的复杂类型除了其参考页面上列出的个别类型的限制外，还有一些限制：

原生表必须至少有一个基本类型或原生数组（基本类型的一维数组）的列。如果一个 Flex 表有真实的列，它也必须至少有一个满足这个限制的列。
复杂类型列不能在 ORDER BY 或 PARTITION BY 子句中使用，也不能用作 FILLER 列。
复杂类型列不能有约束。
返回复杂类型的表达式不能用作投影列，并且投影不能按复杂类型的列进行分段或排序。
具有复杂类型列的表不能使用 DEFAULT 和 SET USING。

示例

以下示例将在公共架构中创建一个表：

CREATE TABLE public.Premium_Customer
(
    ID IDENTITY ,
    lname varchar(25),
    fname varchar(25),
    store_membership_card int
);

以下示例将使用 LIKE 从该表创建一个新表：

=> CREATE TABLE All_Customers LIKE Premium_Customer;
CREATE TABLE

以下示例将使用 AS 子句从一个表中选择要在新表中使用的列：

=> CREATE TABLE cust_basic_profile AS SELECT
     customer_key, customer_gender, customer_age, marital_status, annual_income, occupation
     FROM customer_dimension WHERE customer_age>18 AND customer_gender !='';
CREATE TABLE
=> SELECT customer_age, annual_income, occupation FROM cust_basic_profile
     WHERE customer_age > 23 ORDER BY customer_age;
 customer_age | annual_income |     occupation
--------------+---------------+--------------------
           24 |        469210 | Hairdresser
           24 |        140833 | Butler
           24 |        558867 | Lumberjack
           24 |        529117 | Mechanic
           24 |        322062 | Acrobat
           24 |        213734 | Writer
           ...

以下示例将使用数组列创建一个表：

=> CREATE TABLE orders(
    orderkey    INT,
    custkey     INT,
    prodkey     ARRAY[VARCHAR(10)],
    orderprices ARRAY[DECIMAL(12,2)],
    orderdate   DATE
);

以下示例使用 ROW 复杂类型：

=> CREATE TABLE inventory
    (store INT, products ROW(name VARCHAR, code VARCHAR));

以下示例使用配额：

=> CREATE SCHEMA internal DISK_QUOTA '10T';
CREATE SCHEMA

=> CREATE TABLE internal.sales (...) DISK_QUOTA '5T';
CREATE TABLE

=> CREATE TABLE internal.leads (...) DISK_QUOTA '12T';
WARNING 0: Table leads has disk quota greater than its schema internal

另请参阅

1 - Column-definition

指定将应用于某一列的名称、数据类型和约束。

语法

column-name data-type
    [ column‑constraint ][...]
    [ ENCODING encoding-type ]
    [ ACCESSRANK integer ]

参数

column-name: 要创建或添加的列的名称。
data-type: Vertica 支持的数据类型。

提示
在 CREATE TABLE 语句中指定最大列宽时，请对任何字符串类型使用以字节（八位字节）为单位的宽度。每个 UTF-8 字符可能需要 4 个字节，但欧洲语言的每个字符一般需要多于一个字节，而东方语言的每个字符一般需要少于三个字节。
column‑constraint: Vertica 支持的约束类型，例如，NOT NULL 或 UNIQUE。有关常规信息，请参阅约束。
ENCODING encoding-type: 列编码类型，默认设置为 AUTO。
ACCESSRANK {{< codevar >}}integer{{< /codevar >}}: 覆盖列的默认访问等级。使用此参数可提高或降低 Vertica 访问列的速度。有关详细信息，请参阅覆盖默认的列等级。

示例

以下示例在 Employee_Dimension 架构中创建了一个名为 public 的表，及其相关联的超投影。Employee_key 列被指定为主键，并为 Employee_gender 列定义指定了 RLE 编码。

=> CREATE TABLE public.Employee_Dimension (
    Employee_key                   integer PRIMARY KEY NOT NULL,
    Employee_gender                varchar(8) ENCODING RLE,
    Courtesy_title                 varchar(8),
    Employee_first_name            varchar(64),
    Employee_middle_initial        varchar(8),
    Employee_last_name             varchar(64)
);

2 - Column-name-list

用于在通过查询创建表或临时表时重命名列；还可用于指定列的编码类型和访问等级。

语法

column-name-list
    [ ENCODING encoding‑type ]
    [ ACCESSRANK integer ]
    [ GROUPED ( column‑reference[,...] ) ]

参数

column‑name: 为列指定新名称。
ENCODING encoding‑type: 指定要在列上使用的编码的类型。默认编码类型为“AUTO”。
ACCESSRANK integer: 覆盖列的默认访问等级，对于确定列访问的优先级很有用。请参阅确定列访问速度的优先级。
GROUPED: 将两个或多个列分组。有关详细信息，请参阅 GROUPED 子句。

要求

列表中的列不能指定该列的数据类型或任何约束。这些是从已查询表中派生的。
如果查询输出包含表达式简单列以外的表达式（例如，常数或函数），则必须为该表达式指定别名，或者列名称列表中必须包括所有已查询列。
CREATE TABLE 可以在列名称列表或查询的 ENCODED BY 子句之一中指定编码类型和访问等级，但不能同时在两者中指定。例如，以下 CREATE TABLE 语句将在列名称列表中的两列上设置编码和访问等级：
```
=> CREATE TABLE promo1 (state ENCODING RLE ACCESSRANK 1, zip ENCODING RLE,...)
     AS SELECT * FROM customer_dimension ORDER BY customer_state;
```
下一个语句在查询的 ENCODED BY 子句中指定相同的编码和访问等级。
```
=> CREATE TABLE promo2
     AS SELECT * FROM customer_dimension ORDER BY customer_state
     ENCODED BY customer_state ENCODING RLE ACCESSRANK 1, customer_zip ENCODING RLE;
```

3 - Column-constraint

为某一列的元数据添加约束。有关详细信息，请参阅约束。

语法

[ { AUTO_INCREMENT | IDENTITY } [ (args) ] ]
[ CONSTRAINT constraint‑name ] {
   [ CHECK (expression) [ ENABLED | DISABLED ] ]
   [ [ DEFAULT expression ] [ SET USING expression } | DEFAULT USING expression ]
   [ NULL | NOT NULL ]
   [ { PRIMARY KEY [ ENABLED | DISABLED ] REFERENCES table [( column )] } ]
   [ UNIQUE [ ENABLED | DISABLED ] ]
}

参数

注意

您可以通过使用关键字 ENABLED 或 DISABLED 进行限定来指定强制执行某些约束。请参阅下面的强制执行约束。

AUTO_INCREMENT | IDENTITY

创建一个表列，其值将由数据库自动生成和管理。不能更改或加载此列中的值。只能在一个表列上设置此约束。

AUTO_INCREMENT 和 IDENTITY 是同义词。有关此约束和可选实参的详细信息，请参阅 AUTO_INCREMENT 和 IDENTITY 序列。

这些选项对临时表无效。

CONSTRAINT constraint‑name

为约束分配名称，对以下约束有效：

PRIMARY KEY
REFERENCES （外键）
CHECK
UNIQUE

如果忘记为这些约束分配名称，Vertica 会分配其自己的名称。有关详细信息，请参阅对约束进行命名。

Vertica 建议对所有约束进行命名。

CHECK (expression)

添加检查条件 expression，该条件将返回布尔值。

DEFAULT

指定此列的默认值：

DEFAULT default‑expr

如果操作忽略了此列中的值，Vertica 将评估 DEFAULT 表达式并在加载操作时设置此列。有关有效表达式的详细信息，请参阅定义列值。

SET USING

指定通过指定的表达式设置此列中的值：

SET USING using‑expr

仅当调用了函数 REFRESH_COLUMNS 时，Vertica 才会评估 SET USING 表达式并刷新列值。有关有效表达式的详细信息，请参阅定义列值。

DEFAULT USING

定义具有 DEFAULT 和 SET USING 约束的列，为两者指定相同的表达式。 DEFAULT USING 列支持的表达式与 SET USING 列相同，并遵从相同的限制。

NULL | NOT NULL

指定列是否可以包含 null 值：

NULL：允许列中出现 null 值。如果您在主键列上设置此约束，Vertica 将忽略该约束并将其设置为 NOT NULL。
NOT NULL：指定列在插入和更新操作期间必须设置为一个值。如果列既没有默认值，也没有为其提供值，则 INSERT 或 UPDATE 将返回错误。

如果忽略此约束，则除主键列之外的所有列的默认值将为 NULL，Vertica 始终将其设置为 NOT NULL。

外部表： 如果指定 NOT NULL 且列包含 null 值，则查询可能会返回错误或产生异常行为。仅当您确定列不包含 null 值时，再为外部表列指定 NOT NULL。

PRIMARY KEY

将此列标识为表的主键。

REFERENCES

将此列标识为外键：

REFERENCES table [column]

其中 column 是 table 中的主键。如果省略了 column，Vertica 将引用 table 中的主键。

UNIQUE

要求列数据对于所有表行都是唯一的。

特权

表所有者或用户 WITH GRANT OPTION 是授予者。

对表的 REFERENCES 权限，可创建引用此表的外键约束
包含表的架构上的 USAGE 权限

强制执行约束

可以使用关键字 ENABLED 或 DISABLED 限定以下约束：

PRIMARY KEY
UNIQUE
CHECK

如果省略了 ENABLED 或 DISABLED，Vertica 会通过检查相应的配置参数来确定是否自动启用约束：

EnableNewPrimaryKeysByDefault
EnableNewUniqueKeysByDefault
EnableNewCheckConstraintsByDefault

有关详细信息，请参阅约束强制执行。

4 - 分区子句

通过表定义中的 PARTITION BY 子句指定表数据的分区：

PARTITION BY partition‑expression [ GROUP BY group‑expression ] [ active‑partition‑count‑expr ]

PARTITION BY partition‑expression

对于每个表行，解析为从一个或多个表列派生的分区键。

当心

避免对 LONG VARBINARY 和 LONG VARCHAR 列上的表进行分区。这样做会对性能产生不利影响。

GROUP BY group‑expression

对于每个表行，解析为从分区键派生的分区组键。Vertica 使用组键将分区合并到单独的分区组中。GROUP BY 必须使用与 PARTITION BY 相同的表达式。例如：

...PARTITION BY (i+j) GROUP BY (
     CASE WHEN (i+j) < 5 THEN 1
          WHEN (i+j) < 10 THEN 2
          ELSE 3);

有关按组对表数据进行分区的详细信息，请参阅分区分组和分层分区。

active‑partition‑count‑expr

指定此表的活动分区数量，具体如下：

在 CREATE TABLE 的分区子句中：
```
ACTIVEPARTITIONCOUNT integer
```
在 ALTER TABLE 的分区子句中：
```
SET ACTIVEPARTITIONCOUNT integer
```

此设置将取代配置参数 ActivePartitionCount。有关用法的详细信息，请参阅活动和非活动分区。

分区要求和限制

PARTITION BY 表达式可以指定叶表达式、函数和运算符。以下要求和限制适用：

所有的表投影必须包含表达式引用的所有列；否则 Vertica 将无法解析表达式。
表达式可以引用多个列，但是它必须在每一行解析为一个非 null 值。

注意
可以使用函数 ZEROIFNULL 来避免出现 null 相关错误。此函数可以检查 PARTITION BY 表达式中的 null 值并将其评估为 0。例如： CREATE TABLE t1 (a int, b int) PARTITION BY (ZEROIFNULL(a)); CREATE TABLE
所有叶表达式都必须为常数或表列。
所有其他表达式必须为函数和运算符。以下限制适用于函数： * 必须为不可变，即在不同时间和区域设置以及其他会话 - 或特定环境条件下返回相同的值。 * 不可以为聚合函数。 * 不可以为 Vertica 元函数。
表达式不可以包含查询。
表达式不可以包含用户定义的数据类型，如 Geometry。

GROUP BY 表达式不支持 modulo (%) 运算。

示例

以下语句创建 store_orders 表并将数据加载到其中。CREATE TABLE 语句包含一个简单的分区子句，指定按年份对数据进行分区：

=> CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
UNSEGMENTED ALL NODES
PARTITION BY YEAR(order_date);
CREATE TABLE
=> COPY store_orders FROM '/home/dbadmin/export_store_orders_data.txt';
41834

当 COPY 将新表数据加载到 ROS 存储中时，Tuple Mover 会将每年的订单划分为单独的分区，且将这些分区合并到 ROS 容器中，以执行表的分区子句。

在这种情况下，Tuple Mover 为加载的数据创建四个分区键（2017、2016、2015 和 2014），并将数据相应划分到单独的 ROS 容器中：

=> SELECT dump_table_partition_keys('store_orders');
... Partition keys on node v_vmart_node0001
  Projection 'store_orders_super'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2017
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2016
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2015
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2014

 Partition keys on node v_vmart_node0002
  Projection 'store_orders_super'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2017
...

(1 row)

当新数据加载到 store_orders 中时，Tuple Mover 会将其合并到适当的分区中，以此根据需要创建新的分区键。

另请参阅

分区表

5 - Table-constraint

为表元数据添加约束。您可以使用 CREATE TABLE 指定表约束，或使用 ALTER TABLE 将约束添加到现有表。有关详细信息，请参阅设置约束。

注意

向视图中所引用的表添加约束不会对视图产生影响。

语法

[ CONSTRAINT constraint-name ]
{
... PRIMARY KEY (column[,... ]) [ ENABLED | DISABLED ]
... | FOREIGN KEY (column[,... ] ) REFERENCES table [ (column[,...]) ]
... | UNIQUE (column[,...]) [ ENABLED | DISABLED ]
... | CHECK (expression) [ ENABLED | DISABLED ]
}

参数

CONSTRAINT constraint‑name

为约束分配名称。Vertica 建议对所有约束进行命名。

PRIMARY KEY

定义一个或多个 NOT NULL 列作为主键，如下所示：

PRIMARY KEY (column[,...]) [ ENABLED | DISABLED]

可以使用关键字 ENABLED 或 DISABLED 来限定此子句。请参阅下面的强制执行约束。

如果您没有命名主键约束，Vertica 会分配名称 C_PRIMARY。

FOREIGN KEY

添加引用完整性约束，以将一个或多个列定义为外键，如下所示：

FOREIGN KEY (column[,... ]) REFERENCES table [(column[,... ])]

如果省略了 column，Vertica 将引用 table 中的主键。

如果您没有命名外键约束，Vertica 会分配名称 C_FOREIGN。

重要

添加外键约束需要以下权限（除了 ALTER TABLE 也需要的权限外）：

对引用表的 REFERENCES 权限
对引用表架构的 USAGE 权限

UNIQUE

指定一个列或一组列中的数据对于所有表行都是唯一的，如下所示：

UNIQUE (column[,...]) [ENABLED | DISABLED]

可以使用关键字 ENABLED 或 DISABLED 来限定此子句。请参阅下面的强制执行约束。

如果您没有命名唯一约束，Vertica 会分配名称 C_UNIQUE。

CHECK

指定检查条件，即一个返回布尔值的表达式，如下所示：

CHECK (expression) [ENABLED | DISABLED]

可以使用关键字 ENABLED 或 DISABLED 来限定此子句。请参阅下面的强制执行约束。

如果您没有命名检查约束，Vertica 会分配名称 C_CHECK。

权限

非超级用户：表所有者，或以下权限：

对架构的 USAGE 权限
对表的 ALTER 权限
对表的 SELECT 权限，用于在表上启用或禁用约束实施

强制执行约束

表可以使用关键字 ENABLED 或 DISABLED 指定 Vertica 是否自动强制执行主键、唯一键或检查约束。如果省略了 ENABLED 或 DISABLED，Vertica 会通过检查相应的配置参数来确定是否自动启用约束：

EnableNewPrimaryKeysByDefault
EnableNewUniqueKeysByDefault
EnableNewCheckConstraintsByDefault

有关详细信息，请参阅约束强制执行。

示例

以下示例将使用主键约束创建一个表 (t01)。

CREATE TABLE t01 (id int CONSTRAINT sampleconstraint PRIMARY KEY);
CREATE TABLE

此示例没有使用约束创建相同表，然后添加约束 ALTER TABLE ADD CONSTRAINT

CREATE TABLE t01 (id int);
CREATE TABLE

ALTER TABLE t01 ADD CONSTRAINT sampleconstraint PRIMARY KEY(id);
WARNING 2623:  Column "id" definition changed to NOT NULL
ALTER TABLE

以下示例创建一个包含两列的表 (addapk)，向表中添加第三列，然后在第三列上添加主键约束。

=> CREATE TABLE addapk (col1 INT, col2 INT);
CREATE TABLE

=> ALTER TABLE addapk ADD COLUMN col3 INT;
ALTER TABLE

=> ALTER TABLE addapk ADD CONSTRAINT col3constraint PRIMARY KEY (col3) ENABLED;
WARNING 2623:  Column "col3" definition changed to NOT NULL
ALTER TABLE

使用示例表 addapk，检查是否启用了主键约束（is_enabled 为 t）。

=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk');

 constraint_name | column_name | constraint_type | is_enabled
-----------------+-------------+-----------------+------------
 col3constraint  | col3        | p               | t
(1 row)

此示例使用 ALTER TABLE ALTER CONSTRAINT 禁用约束。

=> ALTER TABLE addapk ALTER CONSTRAINT col3constraint DISABLED;

检查主键现在是否已禁用（is_enabled 为 f）。

=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk');

 constraint_name | column_name | constraint_type | is_enabled
-----------------+-------------+-----------------+------------
 col3constraint  | col3        | p               | f
(1 row)

有关约束的一般讨论，请参阅约束。有关创建和命名约束的其他示例，请参阅对约束进行命名。