CREATE TABLE AS

Description

Creates a table from the results of a query.

It creates a table and fills it with data obtained using SELECT. The table columns have the names and data types associated with the output columns of SELECT (except that you can override the SELECT output column names by giving an explicit list of new column names).

CREATE TABLE AS queries a source table once and writes the data in a new table. The result in the query view changes with the source table. In contrast, the view re-computes and defines its SELECT statement at each query.

Precautions

• This statement cannot be used to create a partitioned table.
• If an error occurs during table creation, after it is fixed, the system may fail to delete the disk files that are created before the last automatic clearance and whose size is not 0. This problem seldom occurs and does not affect system running of the database.
• If you add a row-level expression when adding or changing an ILM policy for a data object, note that the row-level expression supports only the functions listed in the whitelist. For details about the whitelist function list, see Row Expression Function Whitelist.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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 } ]
    [ COMPRESS | NOCOMPRESS ]
    [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]]
    [ TABLESPACE tablespace_name ]
    [ DISTRIBUTE BY { REPLICATION | { [HASH ] ( column_name ) } } ]
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
    AS query
    [ WITH [ NO ] DATA ];

For details about each field, see Syntax.

Parameters

• UNLOGGED

  Specifies that the table is created as an unlogged table. Data written to unlogged tables is not written to the WALs, which makes them considerably faster than ordinary tables. However, they are not crash-safe. An unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.

  • Usage scenario: Unlogged tables do not ensure data security. Users can back up data before using unlogged tables; for example, users should back up the data before a system upgrade.
  • Troubleshooting: If data is missing in the indexes of unlogged tables due to some unexpected operations such as an unclean shutdown, users should rebuild the indexes with errors.
• GLOBAL | LOCAL

  When creating a temporary table, you can specify the GLOBAL or LOCAL keyword before TEMP or TEMPORARY. Currently, the two keywords are used to be compatible with the SQL standard. A local temporary table will be created by the GaussDB regardless of whether GLOBAL or LOCAL is specified.

• TEMPORARY | TEMP

  If TEMP or TEMPORARY is specified, the created table is a temporary table. A temporary table is automatically dropped at the end of the current session. Therefore, you can create and use temporary tables in the current session when CNs, except for the one connected to the current session, are faulty. Temporary tables are created only in the current session. If a DDL statement involves operations on temporary tables, a DDL error will be generated. Therefore, you are advised not to perform operations on temporary tables in DDL statements. TEMP is equivalent to TEMPORARY.

  

  • Temporary tables are visible to the current session through a schema starting with pg_temp. Therefore, do not delete schemas starting with pg_temp or pg_toast_temp.
  • If TEMPORARY or TEMP is not specified when you create a table but its schema is set to that starting with pg_temp in the current session, the table will be created as a temporary table.
  • A temporary table is visible only to the current session. Therefore, it cannot be used together with \parallel on.
  • Temporary tables do not support DN faults or primary/standby switchovers.
• IF NOT EXISTS

  When IF NOT EXISTS is specified, the system checks whether a relationship with the same name already exists in the current schema before creating a table. It is not created and a NOTICE is returned if a relationship with the same name already exists. When IF NOT EXISTS is not specified and a relationship with the same name exists in the schema, an ERROR is returned.

• table_name

  Specifies the name of the table to be created.

  Value range: a string. It must comply with the naming convention.

• column_name

  Optional. Specifies the name of a column to be created in the new table. If no column name is specified, the columns in the new table are the same as those entered in the SELECT statement.

  Value range: a string. It must comply with the naming convention.

• ENGINE

  Supported in MySQL-compatible mode and used only for syntax adaptation. Only InnoDB can be set and no actual effect is achieved.

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

  Specifies an optional storage parameter for a table or an index. See details of parameters below.

  • FILLFACTOR

    The fill factor of a table is a percentage from 10 to 100. If Ustore is used, the default value is 92. 100 (complete filling) is the default value. When a smaller fill factor is specified, INSERT operations fill table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page, which is more efficient than placing it on a different page. For a table whose entries are never updated, setting the fill factor to 100 (complete filling) is the best choice, but in heavily updated tables a smaller fill factor would be appropriate. The parameter is only valid for row–store tables.

    Value range: 10–100

  • ORIENTATION

    Value range:

    COLUMN: The data will be stored in columns.

    ROW (default value): The data will be stored in rows.

  • COMPRESSION

    Specifies the compression level of table data. It determines the compression ratio and time. Generally, the higher the level of compression, the higher the ratio, the longer the time; and the lower the level of compression, the lower the ratio, the shorter the time. The actual compression ratio depends on the distribution mode of table data loaded.

    Value range:

    Row-store tables do not support compression.

  • hashbucket

    Creates a hash table that uses buckets. This parameter supports only row-store tables, including row-store range tables.

    Value range: on and off

    Default value: off

    

    In current version, DDL operations on hash bucket tables are affected. Therefore, you are advised not to frequently perform DDL operations on hash bucket tables.

• ON COMMIT { PRESERVE ROWS | DELETE ROWS }

  ON COMMIT determines what to do when you commit a temporary table creation operation. Currently, only PRESERVE ROWS and DELETE ROWS are supported.

  • PRESERVE ROWS (default): No special action is taken at the ends of transactions. The temporary table and its table data are unchanged.
  • DELETE ROWS: All rows in the temporary table will be deleted at the end of each transaction block.
• COMPRESS / NOCOMPRESS

  Specifies keyword COMPRESS during the creation of a table, so that the compression feature is triggered in case of bulk insert operations. If this feature is enabled, a scan is performed for all tuple data within the page to generate a dictionary and then the tuple data is compressed and stored. If NOCOMPRESS is specified, the table is not compressed. Row-store tables do not support compression.

  Default value: NOCOMPRESS, that is, tuple data is not compressed before storage.

• [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]]

  When creating a table, you can call ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW to add an advanced compression policy for row store.

  • AFTER n { day | month | year } OF NO MODIFICATION: indicates the rows that are not modified in n days, months, or years.
  • ON (EXPR): indicates the row-level expression, which is used to determine whether a row is hot or cold.
• TABLESPACE tablespace_name

  Specifies that the new table will be created in the tablespace_name tablespace. If not specified, the default tablespace is used.

• DISTRIBUTE BY

  For details, see DISTRIBUTE BY.

• TO { GROUP groupname | NODE ( nodename [, ... ] ) }

  TO GROUP specifies the node group to which the table to be created belongs. TO NODE is used for internal scale-out tools.

• AS query

  Specifies a SELECT or VALUES command, or an EXECUTE command that runs a prepared SELECT or VALUES query.

• [ WITH [ NO ] DATA ]

  Specifies whether the data produced by the query should be copied to the new table. By default, the data will be copied. If the value NO is used, only the table structure will be copied.

Examples

• If no column name is specified, the columns in the new table are the same as those queried in the SELECT statement.

  -- Create the test1 table and insert two records into the table.
  gaussdb=# CREATE TABLE test1(col1 int PRIMARY KEY,col2 varchar(10));
  gaussdb=# INSERT INTO test1 VALUES (1,'col1'),(101,'col101');
  -- Query the data whose col1 is less than 100 in the table.
  gaussdb=# SELECT * FROM test1 WHERE col1 < 100;
   col1 | col2 
  ------+------
      1 | col1
  (1 row)
  
  -- Create the test2 table and insert the queried data into the table.
  gaussdb=# CREATE TABLE test2 AS SELECT * FROM test1 WHERE col1 < 100;
  
  -- Query the structure of the test2 table.
  gaussdb=# \d test2;
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   col1   | integer               | 
   col2   | character varying(10) |

• Specify column names for the new table.

  -- Use test1 to copy a new table test3 and specify column names.
  gaussdb=# CREATE TABLE test3(c1,c2) AS SELECT * FROM test1;
  
  -- Query the structure of the test3 table.
  gaussdb=# \d test3
              Table "public.test3"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   c1     | integer               | 
   c2     | character varying(10) | 
  
  -- Delete.
  gaussdb=# DROP TABLE test1,test2,test3;

• Create a table with the ILM policy enabled.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  -- Create an ORA-compatible database. gaussdb=# CREATE DATABASE ilmtabledb WITH dbcompatibility = 'ORA'; gaussdb=# \c ilmtabledb -- Enable the ILM feature of the database. ilmtabledb=# ALTER DATABASE SET ILM = on; -- Create a table ilm_table with the ILM policy enabled and insert the value of old_table. ilmtabledb=# CREATE TABLE old_table (a int); ilmtabledb=# CREATE TABLE ilm_table ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 3 MONTHS OF NO MODIFICATION AS (SELECT * FROM old_table); -- Delete. ilmtabledb=# DROP TABLE old_table,ilm_table; ilmtabledb=# \c postgres gaussdb=# DROP DATABASE ilmtabledb;

Helpful Links

CREATE TABLE and SELECT