更新时间:2026-10-21 GMT+08:00
分享

CREATE TABLE AS

功能描述

根据查询结果进行创建表。

CREATE TABLE AS创建一个表并且用来自SELECT命令的结果填充该表。该表的字段和SELECT输出字段的名称及数据类型相关。不过用户可以通过指定字段名称列表进行覆盖SELECT输出字段的名称。

CREATE TABLE AS和创建视图有些相似,CREATE TABLE AS会创建一个新表并且只将SELECT命令的第一次结果写入新表中。这个表之后的数据将不会根据源表变化而改变;而对于视图,只要视图被查询,那么视图的定义SELECT语句将会被重新计算。

注意事项

  • 通过该命令创建的新表,只继承SELECT输出字段的名称及数据类型。
  • 无法通过该命令进行创建分区表操作。
  • 创建表过程中,如果数据库系统发生故障,系统恢复后可能无法自动清理已创建的磁盘文件,或残留大小为0的空文件。
  • 在为数据对象增加或者变更ILM策略的时候,如果追加了行级表达式,需要注意行表达式目前只支持白名单中列出的函数。具体白名单函数列表请参见行表达式函数白名单。

    在ILM策略的ON ( EXPR )行级表达式支持的函数中,存在部分函数的输出可能会受兼容性参数的影响。例如,upper函数在B兼容模式下设置b_format_version='5.7'和b_format_dev_version='s2'后,将无法转大写。

语法格式

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ { ENGINE [ = ] { InnoDB | 'InnoDB' | "InnoDB" } } [ ,... ] [ ... ] ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
    [ ILM ADD POLICY ROW STORE COMPRESS 
 { ADVANCED [ MEDIUM | HIGH ] ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )] 
 | TURBO [ LOW | MEDIUM | HIGH ] UNIT AFTER n { hour | day | month | year } OF NO MODIFICATION 
 | NONE}]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ];

参数说明

  • UNLOGGED

    指定该关键字,创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中,相较于普通表速度更快。但是非日志表在冲突、执行操作系统重启、数据库重启、主备切换、切断电源操作或异常关机后会被全部清空,会造成数据丢失的风险。非日志表中的内容也不会被复制到备用服务器中。在非日志表中创建的索引也不会被自动记录。

    • 使用场景:非日志表不能保证数据的安全性,用户应该在确保数据已经做好备份的前提下使用,例如系统升级时进行数据的备份。
    • 故障处理:当异常关机等操作导致非日志表上的索引发生数据丢失时,用户应该对发生错误的索引进行重建。
  • GLOBAL | LOCAL

    创建临时表时可以在TEMP或TEMPORARY前指定GLOBAL或LOCAL关键字。如果指定GLOBAL关键字,GaussDB会创建全局临时表,否则创建本地临时表。

  • TEMPORARY | TEMP

    指定TEMP或TEMPORARY关键字,创建的表为临时表。临时表分为全局临时表(GLOBAL)和本地临时表(LOCAL)。创建临时表时如果指定GLOBAL关键字则为全局临时表,否则为本地临时表。

    • 全局临时表(GLOBAL)的元数据对所有会话可见,会话结束后元数据继续存在。会话与会话之间的用户数据、索引和统计信息相互隔离,每个会话只能看到和更改自己提交的数据。全局临时表有两种模式:
      • 一种是基于会话级别的(ON COMMIT PRESERVE ROWS),当会话结束时自动清空用户数据。
      • 一种是基于事务级别的(ON COMMIT DELETE ROWS),当执行COMMIT或ROLLBACK时自动清空用户数据。

      建表时如果没有指定ON COMMIT选项,默认为会话级别。与本地临时表不同,全局临时表建表时可以指定非pg_temp开头的Schema。

    • 本地临时表(LOCAL)只在当前会话可见,本会话结束后会自动删除。由于临时表只在当前会话创建,对于涉及对临时表操作的DDL语句,会产生DDL失败的报错。因此,建议DDL语句中不要对临时表进行操作。
      • 本地临时表(LOCAL)通过每个会话中以pg_temp开头的Schema进行保证只对当前会话可见,因此,不建议用户在日常操作中手动删除以pg_temp,pg_toast_temp开头的Schema。
      • 如果建表时不指定TEMPORARY | TEMP关键字,而指定表的Schema为当前会话以pg_temp开头的Schema,则此表会被创建为临时表。
      • ALTER/DROP全局临时表和索引,如果存在会话正在使用(全局临时表和索引)时,禁止执行ALTER/DROP操作。
      • 全局临时表的DDL只会影响当前会话的用户数据和索引,例如TRUNCATE、REINDEX、ANALYZE只对当前会话有效。
  • IF NOT EXISTS

    判断是否存在同名的表。如果指定IF NOT EXISTS关键字,创建表前会在当前Schema中查找是否已有名字相同的依赖关系relation。若存在同名的relation,则不会新建并返回NOTICE提示。未指定IF NOT EXISTS关键字时,若Schema中存在同名relation,则返回ERROR告警。

  • table_name

    创建的表名。

    取值范围:字符串,要符合标识符命名规范。

  • column_name

    可选。新表中要创建的字段名。如果没有指定字段名,那么新表的字段名和SELECT语句输入的字段名一致。

    取值范围:字符串,要符合标识符命名规范。

  • ENGINE

    B模式下支持,仅语法适配,且只支持设置InnoDB,并无实际效果。

  • WITH ( storage_parameter [= value] [, ... ] )

    为表或索引指定一个可选的存储参数。参数的详细说明如下所示。

    支持透明加密相关存储参数:enable_tde、encrypt_algo、dek_cipher、key_type、cmk_id。

    详细参数说明请参见CREATE TABLE章节。

    • FILLFACTOR

      表的填充因子(fillfactor),一个介于10和100之间的百分数。在Ustore存储引擎下,该值的默认值为92,在Astore存储引擎下默认值为100(完全填充)。如果指定了较小的填充因子,INSERT操作仅按照填充因子指定的百分率填充表页,每个页上的剩余空间将用于在该页上更新行。较小的填充因子对于UPDATE操作,可以在同一页上有机会放置同一条记录的新版本,相较于将新版本的数据放置在其他页上更为有效。对于一个从不更新的表,建议将填充因子设为100,对于频繁更新的表,建议选择较小的填充因子。该参数只对行存表有效。

      取值范围:10~100

    • ORIENTATION

      取值范围:ROW,表的数据将以行式存储。

      默认值:ROW

    • autovacuum_enabled

      表示是否开启表的自动清理功能。

      取值范围:on/off

      默认值:on

    • autovacuum_vacuum_threshold

      开启自动清理功能时,指定在该表中触发VACUUM所需的更新或删除的最小元组数(仅对Astore表生效)。

      取值范围:0~2147483647

      默认值:未指定时与GUC参数autovacuum_vacuum_threshold一致。

    • autovacuum_analyze_threshold

      开启自动清理功能时,指定在该表中触发ANALYZE所需的插入、更新或删除的最小元组数。

      取值范围:0~2147483647

      默认值:未指定时与GUC参数autovacuum_analyze_threshold一致。

    • autovacuum_vacuum_scale_factor

      开启自动清理功能时,指定在该表中触发VACUUM所需的插入、更新或删除元组的比例(仅对Astore表生效)。

      取值范围:0.0~100.0

      默认值:未指定时与GUC参数autovacuum_vacuum_scale_factor一致。

    • autovacuum_analyze_scale_factor

      开启自动清理功能时,指定在该表中触发ANALYZE所需的插入、更新或删除元组的比例。

      取值范围:0.0~100.0

      默认值:未指定时与GUC参数autovacuum_analyze_scale_factor一致。

    • autovacuum_freeze_min_age

      开启自动清理功能时,指定在该表参数指定了一个行版本的最小范围,超过这个范围的行会被冻结。

      取值范围:0~576460752303423487

      默认值:未指定时与GUC参数vacuum_freeze_min_age一致。

    • autovacuum_freeze_max_age

      开启自动清理功能时,表pg_class.relfrozenxid字段在超过指定个事务后,就会强制执行VACUUM操作,即使自动清理被禁用,系统也会启动AUTOVACUUM进程。清理操作还允许从pg_clog/子目录中删除旧文件(仅对Astore表生效)。

      取值范围:100000~576460752303423487

      默认值:未指定时与GUC参数autovacuum_freeze_max_age一致。

    • autovacuum_freeze_table_age

      开启自动清理功能时,该表被标记为不需要自动清理时,将保持不变的时间(仅对Astore表生效)。

      取值范围:0~576460752303423487

      默认值:未指定时与GUC参数vacuum_freeze_table_age一致。

    • vacuum_truncate

      表示是否开启表的VACUUM TRUNCATE功能,在VACUUM时截断末尾空页。

      取值范围:on/off

      默认值:on

      • 该参数仅对Astore生效,对hashbucket表和segment表不生效。
      • 从不支持该参数版本升级至支持该参数版本时,不支持在升级观察期创建、修改该参数。
      • 支持通过toast.vacuum_truncate参数单独控制TOAST表是否截断末尾空页。
    • hasrowid

      指定表是否包含ROWID、ROWNO系统列。设置hasrowid = on时,该表会增加rowid系统列和rowno系统列,且会在rowno系统列上关联创建序列和索引。

      取值范围:on、off

      默认值:off

      • 此参数只支持A兼容模式数据库(即sql_compatibility = 'A')。
      • 此参数不支持本地临时表、Unlogged表、全局临时表。
      • 此参数不支持和hasuids、oids参数同时指定。
      • 开启此参数时,不支持定义普通列名为rowno、rowid。
    • partition_statistics_min_rows

      对分区表执行ANALYZE命令时,用于确定分区级最小采样行数的参数值。

      取值范围:[0, 10000]

      默认值:50

      • 此参数仅对分区表有效。
      • 此参数仅在ANALYZE命令的PARTITION_MODE = ALL时,且GUC参数enable_partition_statistics_min_rows启用时生效,PARTITION_MODE说明请参见ANALYZE | ANALYSE章节中的PARTITION_MODE。
      • 上限约束:最终的分区表整表采样行数不超过GUC参数maintenance_work_mem可容纳的行数。
  • ON COMMIT { PRESERVE ROWS | DELETE ROWS }

    用于表示在事务中执行创建临时表操作且当事务提交时,对该临时表进行的其他操作。当前仅支持PRESERVE ROWS和DELETE ROWS选项。

    • PRESERVE ROWS(缺省值):提交时不对临时表执行任何操作,临时表及其表数据保持不变。
    • DELETE ROWS:提交时删除临时表中数据。
  • ILM ADD POLICY ROW STORE COMPRESS { ADVANCED [ MEDIUM | HIGH ] ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )] | TURBO [ LOW | MEDIUM | HIGH ] UNIT AFTER n { hour | day | month | year } OF NO MODIFICATION | NONE}
    • 创建新表时,可以调用ILM ADD POLICY ROW STORE COMPRESS ADVANCED [ MEDIUM | HIGH ] ROW为行存添加高级压缩中/高级别策略。
      • AFTER n { day | month | year } OF NO MODIFICATION:表示n天/月/年没有修改的行,高级压缩不支持hour。
      • ON ( EXPR ):行级表达式,用于判断行的冷热。
      • 在ILM策略的ON ( EXPR )行级表达式支持的函数中,部分函数的输出可能会受兼容性参数影响。例如,upper函数在B兼容模式下设置b_format_version='5.7'和b_format_dev_version='s2'后,将无法转换为大写。
      • 压缩级别是可选项,不指定时默认使用MEDIUM级别。
    • 创建新表时,可以调用ILM ADD POLICY ROW STORE COMPRESS TURBO HIGH UNIT为行存添加跨页透明压缩策略。

      AFTER n { hour | day | month | year } OF NO MODIFICATION:表示n小时/天/月/年没有修改的行。

      压缩级别不指定时默认为MEDIUM。

    • 创建新表时,可以调用ILM ADD POLICY ROW STORE COMPRESS NONE显式指定行存表不添加跨页透明压缩策略。
  • TABLESPACE tablespace_name

    创建新表时指定此关键字,表示新表将要在指定表空间tablespace_name内创建。如果没有声明,将使用默认表空间。

  • AS query

    一个SELECT VALUES命令或者一个运行预备好的SELECT或VALUES查询的EXECUTE命令。

    在需要进行数据类型转换时,特别是使用CREATE TABLE AS SELECT语句时,建议采用显式类型转换,避免因隐式类型转换可能导致的意外结果。

    M兼容模式下,CREATE TABLE AS SELECT和SELECT语句的datetime类型隐式转换行为不一致。

    -- M兼容模式下,设置GUC参数。
    m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
    m_db=# SET sql_mode='';
    -- 创建t1表,插入一条数据。
    m_db=# CREATE TABLE t1(c3 datetime);
    m_db=# INSERT INTO t1(c3) VALUES ('0000-00-00 00:00:00'::datetime);
    
    -- <>运算符两边类型不同,存在隐式类型转换,CREATE TABLE AS SELECT行为和SELECT语句不一致。
    m_db=# CREATE TABLE tab1 AS SELECT * FROM t1 WHERE c3 <> 0.3::double; -- 插入0条数据。
    INSERT 0 0
    m_db=# SELECT * FROM t1 WHERE c3 <> 0.3::double; -- 查到1条数据。
             c3          
    ---------------------
     0000-00-00 00:00:00
    (1 row)
    
    -- <>运算符两边类型相同,CREATE TABLE AS SELECT行为和SELECT语句一致。
    m_db=# CREATE TABLE tab2 AS SELECT * FROM t1 WHERE c3 <> 0.3::datetime; -- 插入0条数据。
    INSERT 0 0
    m_db=# SELECT * FROM t1 WHERE c3 <> 0.3::datetime; -- 查到0条数据。
     c3 
    ----
    (0 rows)
  • [ WITH [ NO ] DATA ]

    创建表时,是否也插入查询到的数据。默认插入,当指定NO时,则表示不插入数据。

示例

  • 不指定字段名时新表字段和SELECT查询结果一致。
    -- 创建test1表并向表中插入2条记录。
    gaussdb=# CREATE TABLE test1(col1 int PRIMARY KEY,col2 varchar(10));
    gaussdb=# INSERT INTO test1 VALUES (1,'col1'),(101,'col101');
    -- 查询表中col1<100的数据。
    gaussdb=# SELECT * FROM test1 WHERE col1 < 100;
     col1 | col2 
    ------+------
        1 | col1
    (1 row)
    
    -- 创建test2表并向表中插入上面查询的数据。
    gaussdb=# CREATE TABLE test2 AS SELECT * FROM test1 WHERE col1 < 100;
    
    -- 查询test2表表结构。
    gaussdb=# \d test2;
     Column |         Type          | Modifiers 
    --------+-----------------------+-----------
     col1   | integer               | 
     col2   | character varying(10) |
  • 为新表指定字段名。
    -- 使用test1复制一个新表test3并指定字段名。
    gaussdb=# CREATE TABLE test3(c1,c2) AS SELECT * FROM test1;
    
    -- 查询test3表结构。
    gaussdb=# \d test3
                Table "public.test3"
     Column |         Type          | Modifiers 
    --------+-----------------------+-----------
     c1     | integer               | 
     c2     | character varying(10) | 
    
    -- 删除。
    gaussdb=# DROP TABLE test1,test2,test3;
  • 为新表指定压缩策略。
    gaussdb=# CREATE TABLE old_table (a int);
    
    --开启数据库ILM特性。
    gaussdb=# ALTER DATABASE SET ILM = on;
    
    gaussdb=# CREATE TABLE ilm_table 
        ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
        ROW AFTER 3 MONTHS OF NO MODIFICATION 
        AS (SELECT * FROM old_table);
    
    --删除。
    gaussdb=# DROP TABLE old_table,ilm_table;

相关链接

CREATE TABLE,SELECT

相关文档