CREATE TABLE AS

根据查询结果定义一个新表。

概要

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
        [ (column_name [, ...] ) ]
        [ USING MARS ]
        [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
        [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
        [ TABLESPACE tablespace_name ]
        AS query
        [ WITH [ NO ] DATA ]
        [ DISTRIBUTED BY (column [, ... ] ) | DISTRIBUTED RANDOMLY | DISTRIBUTED REPLICATED ]

其中storage_parameter是:

   appendoptimized={TRUE|FALSE}
   blocksize={8192-2097152}
   orientation={COLUMN|ROW}
   compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE}
   compresslevel={1-19 | 1}
   fillfactor={10-100}
   [oids=FALSE]
   tagkey={column,...}
   timekey={column}
   timebucket={interval}

描述

CREATE TABLE AS创建一个表, 并用SELECT命令计算的数据填充该表。 表列具有与SELECT的输出列关联的名称和数据类型,但是您可以通过提供新列名称的显式列表来覆盖列名称。

CREATE TABLE AS创建一个新表并仅对查询进行一次取值以填充一次新表。 新表将不会跟踪对查询源表的后续更改。

参数

GLOBAL | LOCAL

  • 忽略兼容性。 这些关键字已弃用。

TEMPORARY | TEMP

  • 如果指定,则将新表创建为临时表。 临时表在会话结束时或在当前事务结束时自动删除(请参见ON COMMIT)。 临时表存在时,具有相同名称的现有永久表在当前会话中不可见,除非使用模式限定名称引用它们。 在临时表上创建的所有索引也会自动成为临时索引。

UNLOGGED

  • 如果指定,该表将创建为未记录表。 写入未记录表的数据不会写入预写(WAL)日志,这使它们比普通表快得多。 但是,未记录表的内容不会复制到mirror实例。 同样,未记录的表也不是崩溃安全的。 segment实例崩溃或异常关闭后,该segment上未记录表的数据将被截断。 在未记录表上创建的所有索引也会自动成为未记录索引。

table_name

  • 要创建的新表的名称(可以由模式指定)。

column_name

  • 新表中的列名。如果未提供列名,则它们取自查询的输出列名。

WITH ( storage_parameter=value )

  • WITH子句可用于设置表或其索引的存储选项。 请注意,您还可以通过在分区规范中声明WITH子句,在特定分区或子分区上设置不同的存储参数。 可以使用以下存储选项:
    • appendoptimized — 设置为TRUE可将表创建为追加优化的表。 如果为FALSE或未声明,则将表创建为常规堆存储表。
    • blocksize — 设置为表中每个块的大小(以字节为单位)。 blocksize必须介于8192和2097152字节之间,并且是8192的倍数。默认值为32768。该参数仅在appendoptimized=TRUE时生效。
    • orientation — 设置为column以用于列式存储,或设置为row(默认)以用于行式存储。 仅当appendoptimized=TRUE时,此选项才有效。 堆存储表只能是面向行的。
    • compresstype — 设置为ZLIB(默认值),ZSTD或QUICKLZ1以指定使用的压缩类型。 值NONE禁用压缩。 Zstd提供速度或良好的压缩率,可通过compresslevel选项进行调整。 提供QuickLZ和zlib是为了向后兼容。 在通常的工作负载上,Zstd的性能优于这些压缩类型。 仅当appendoptimized=TRUE时,compresstype选项才有效。
    • compresslevel — 对于附加优化表的Zstd压缩,请将其设置为1(最快压缩)到19(最高压缩率)之间的整数值。 对于zlib压缩,有效范围是1到9。 QuickLZ压缩级别只能设置为1。 如果未声明,则默认值为1。 compresslevel选项仅在appendoptimized=TRUE时有效。
    • fillfactor — 有关此索引存储参数的更多信息,请参见CREATE INDEX。
    • oids=FALSE — 这是默认值,每个数据行内没有分配oid。 MatrixDB强烈建议您在创建表时不要启用OIDS。 在大型表上(例如典型的MatrixDB数据库系统中的表),对表行使用OID可能会导致32位OID计数器的折回。 一旦计数器回绕,就不能再认为OID是唯一的,这不仅使它们对用户应用程序无用, 而且还会在MatrixDB数据库系统catalog表中引起问题。 此外,从表中排除OID会使表每行存储在磁盘上所需的空间减少了每行4个字节,从而略微提高了性能。 面向列的表不允许使用OIDS。
    • tagkey - Mars表的参数,用来指定设备标识列
    • timekey - Mars表的参数,用来指定时间戳列
    • timebucket - Mars表的参数,用来标识每个行组的时间范围

ON COMMIT

  • 可以使用ON COMMIT控制事务块末尾的临时表的行为。 这三个选项是:
    PRESERVE ROWS — 临时表的事务结束时不会采取任何特殊操作。这是默认行为。
    DELETE ROWS — 临时表中的所有行将在每个事务块的末尾删除。 本质上,每次提交都会自动执行一次TRUNCATE。
    DROP — 临时表将在当前事务块的末尾删除。

TABLESPACE tablespace_name

  • tablespace_name参数是要在其中创建新表的表空间的名称。 如果未指定,则使用数据库的默认表空间。

AS query

  • SELECT, TABLE或VALUES命令, 或运行准备的SELECT或VALUES查询的EXECUTE命令。

DISTRIBUTED BY ({column [opclass]}, [ ... ] )

DISTRIBUTED RANDOMLY

DISTRIBUTED REPLICATED

  • 用于声明表的MatrixDB数据库分发策略。 DISTRIBUTED BY使用具有一个或多个声明为分发键的列的哈希分发。 为了获得最均匀的数据分配,分发键应为表的主键或唯一列(或一组列)。 如果无法做到这一点,则可以选择DISTRIBUTED RANDOMLY,它将数据轮询发送到segment实例。
    DISTRIBUTED REPLICATED将表中的所有行复制到所有MatrixDB数据库segment。 它不能与分区表或从其他表继承的表一起使用。
    如果在创建表时未指定DISTRIBUTED BY子句, 则MatrixDB数据库服务器配置参数gp_create_table_random_default_distribution将控制默认表分发策略。 如果未指定分发策略,MatrixDB数据库将遵循以下规则来创建表:
    • 如果Postgres查询优化器创建了表,并且该参数的值是off,则根据命令确定表分配策略。
    • 如果Postgres查询优化器创建了表,并且该参数的值为on,则表分配策略是随机的。
    • 如果GPORCA创建表,则表分配策略是随机的。参数值无效。

注解

该命令在功能上类似于SELECT INTO,但它是首选的, 因为它不太可能与SELECT INTO语法的其他用法混淆。 此外,CREATE TABLE AS提供了SELECT INTO提供的功能的超集。

CREATE TABLE AS可用于从外部表数据源快速加载数据。 请参阅CREATE EXTERNAL TABLE。

示例

创建一个新表films_recent,该表仅包含表films中的最新条目:

CREATE TABLE films_recent AS SELECT * FROM films WHERE 
date_prod >= '2007-01-01';

使用预编译语句创建一个新的临时表films_recent,该表仅包含表films中的最新条目。 新表具有OID,并将在提交时删除:

PREPARE recentfilms(date) AS SELECT * FROM films WHERE 
date_prod > $1;
CREATE TEMP TABLE films_recent ON COMMIT DROP AS 
EXECUTE recentfilms('2007-01-01');

兼容性

CREATE TABLE AS符合SQL标准,但以下情况除外:

  • 该标准要求在子查询子句两边加上括号;在MatrixDB数据库中,这些括号是可选的。
  • 该标准定义了WITH [NO] DATA子句;MatrixDB数据库当前未实现此功能。 MatrixDB数据库提供的行为等同于标准的WITH DATA情况。 可以通过将LIMIT 0附加到查询来模拟WITH NO DATA。
  • MatrixDB数据库对临时表的处理方式与标准处理方式有所不同。 有关详细信息,请参见CREATE TABLE。
  • WITH子句是MatrixDB数据库扩展。 storage参数和OIDs都不在标准中。创建OID系统列的语法已被弃用。
  • 表空间的MatrixDB数据库概念不是该标准的一部分。 TABLESPACE子句是扩展。

另见

ALTER TABLE, CREATE TABLE, DROP TABLE