CREATE TABLE SELECT

Description

CREATE TABLE SELECT creates a table and fills it with data obtained using SELECT. The field names and fields types of the table are custom columns and SELECT output fields.

• Fields that only occur in custom columns are displayed at the top. Otherwise, fields are displayed in sequence of the SELECT output columns.
• Columns with the same name but different letter cases are identified as the same column. If the same column exists, the data type of the customized column is retained, and the case of the SELECT column name is retained.
• If a SELECT column and a custom column have the same field, COMMENT of the SELECT column is always retained. If a SELECT column is a direct table column or a direct table column after the view is expanded, COMMENT of the column in the old table is retained. Otherwise, COMMENT is set to NULL.
• If a SELECT column is a direct table column or a direct table column after the view is expanded, and the field is not customized, retain NULL, NOT NULL, DEFAULT VALUE, ON UPDATE, CHARSET, and COLLATE of the old table column.

CREATE TABLE SELECT supports the FOR UPDATE option. The lock mechanism is the same as that of INSERT SELECT FOR UPDATE.

Precautions

• Partitioned tables cannot be created.
• REPLACE and IGNORE are not supported.
• If the SELECT column is not a direct table column, NULL is allowed by default, and there is no default value. For example, the column a of the new table created by CREATE TABLE t1 SELECT unix_timestamp('2008-01-02 09:08:07.3465') AS a; can be NULL and does not have the default value.
• To use all functions, you need to set the GUC parameter m_format_behavior_compat_options to enable_precision_decimal. Otherwise, a behavior error will be reported for types related to data type precision due to version compatibility issues. An error is reported in the UNION scenario or when a SELECT column contains a non-direct table column (such as expressions, functions, and constants).
• 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.

Syntax

CREATE [ TEMPORARY | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    {({ column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset_name ] [ COLLATE collation_name][AUTO_INCREMENT][COMMENT [=] 'string'] [ column_constraint [ ... ] ]
        | table_constraint
        | {INDEX | KEY} [ [schema_name.] index_name ] [ USING method ] ({column_name [(length)] | (expr) [ASC | DESC]}[,...]) [[COMMENT 'string' | USING method][...]]
}
        [, ... ])}
    [ table_option ][[,...]|[...]]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [AS] query
;

Parameters

• UNLOGGED

  If this keyword is specified, the created table is an unlogged table.

  Data written to unlogged tables is not written to WALs. When the OS is restarted, the database is restarted, a switchover occurs, the power supply is cut off, or the system is shut down abnormally, the write operation on the unlogged table is automatically truncated, which may cause data loss. Contents of an unlogged table are also not replicated to standby nodes. Any indexes created on an unlogged table are not automatically logged 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 an abnormal shutdown or other unexpected operations, users should rebuild indexes with errors.

• TEMPORARY

  If TEMPORARY is specified, the created table is a local temporary table.

  A local temporary table is visible only in the current session and is automatically dropped at the end of the session. Therefore, you can create and use the temporary tables in the current session as long as the database node that the session connects to is normal. 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.

  

  • Local temporary tables are visible to the current session through the schema starting with pg_temp. Users should not delete schemas starting with pg_temp or pg_toast_temp.
  • If TEMPORARY is not specified when creating the table and the schema of the table 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 primary/standby switchover.
• IF NOT EXISTS

  Sends a notice, but does not throw an error, if a table with the same name exists.

• table_name

  Specifies the name of the table to be created.

• column_name

  Specifies the name of a column to be created in the target table.

• constraint_name

  Specifies the name of the constraint specified during table creation.

• index_name

  Index name.

  

  • For a foreign key constraint, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
  • If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be used as the index name.
• USING method

  Specifies the method of creating an index.

  For details about the value range, see "USING method" in Parameters.

  

  Currently, only the B-tree index method is supported. However, btree indexes cannot be created for Ustore tables.

• ASC | DESC

  ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

• expression

  Specifies an expression index constraint based on one or more columns of the table. It must be written in parentheses.

• data_type

  Specifies the data type of a column.

• {CHARACTER SET | CHAR SET | CHARSET} charset_name

  Specifies the character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

• COLLATE collation_name

  Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the select * from pg_collation; command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result. For details about the supported collations, see Table-level Character Sets and Collations.

  

  • Only the character type supports the specified character set. If the BINARY character set or collation is specified, the character type is converted to the corresponding binary type. If the type mapping does not exist, an error is reported. Currently, only the mapping from the TEXT type to the BLOB type is available.
  • If the character set or collation of a column is not explicitly specified and the default character set or collation of the table is specified, the character set or collation of the column is inherited from the table.
  • Except the database using SQL_ASCII, databases using other character sets support mixed use of multiple character sets.
• AUTO_INCREMENT [ = ] value

  This clause specifies an initial value for an auto-increment column. The value must be a positive number and cannot exceed 2¹²⁷ – 1.

• COMMENT [ = ] 'string'
  • The COMMENT [ = ] 'string' clause is used to add comments to a table.
  • The COMMENT 'string' in column_constraint indicates that comments are added to a column.
  • The COMMENT 'string' in table_constraint indicates that comments are added to the indexes corresponding to the primary key and unique key.
  

  • A table-level comment can contain a maximum of 2048 characters, and a column-level or index-level comment can contain a maximum of 1024 characters.
  • Comments in table_constraint support only primary keys and unique keys.
• WITH ( { storage_parameter = value } [, ... ] )

  Specifies an optional storage parameter for a table or an index. The WITH clause used for tables can also contain OIDS=FALSE to specify that rows of the new table should not contain OIDs.

  The description of parameters is as follows:

  • FILLFACTOR

    The fill factor of a table is a percentage from 10 to 100. If the Ustore is used, the default value is 92. If the Astore is used, the default value is 100 (completely filled). 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.

    Value range: 10–100

  • ORIENTATION

    Specifies the storage mode of table data. This parameter cannot be modified once it is set. Only row-store mode is supported in M-compatible mode.

    Value range:

    • ROW indicates that table data is stored in rows.

      Row store applies to OLTP service and scenarios with a large number of point queries or addition/deletion operations.

    Default value:

    If an ordinary tablespace is specified, the default is ROW.

  • STORAGE_TYPE

    Specifies the storage engine type. This parameter cannot be modified once it is set.

    Value range:

    • USTORE indicates that tables support the in-place update storage engine. Note that the track_counts and track_activities parameters must be enabled when the Ustore table is used. Otherwise, space bloating may occur.
    • ASTORE indicates that tables support the append-only storage engine.

    Default value:

    If ORIENTATION and STORAGE_TYPE are not specified, Ustore is used by default, indicating that the table supports the in-place update-based storage engine.

  • INIT_TD

    Specifies the number of TDs to be initialized when a Ustore table is created. This parameter can be modified by running the ALTER TABLE command. Note that this parameter affects the maximum size of a single tuple stored on the data page. The conversion method is MAX_TUPLE_SIZE = BLCKSZ – INIT_TD x TD_SIZE. For example, if you change the number of INIT_TD from 4 to 8, the maximum size of a single tuple decreases by 4 x INIT_TD.

    Value ranges: 2–128. The default value is 4.

  • 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. Row-store tables do not support compression.

    Value range: The row-store table does not support compression. The default value is NO.

  • COMPRESSLEVEL

    Specifies the table data compression ratio and duration at the same compression level. This divides a compression level into sublevels, providing more choices for compression ratio and duration. As the value becomes greater, the compression ratio becomes higher and duration longer at the same compression level.

    Value range: 0 to 3. The default value is 0.

  • COMPRESSTYPE

    Specifies the row-store table compression algorithm. The value 1 indicates the pglz algorithm. The value 2 indicates the zstd algorithm. The value 3 indicates TurboDB (Huawei-developed improved compression algorithm). By default, no compression algorithm is used.

    Value range: 0 to 23. The default value is 0.

  • COMPRESS_LEVEL

    Specifies the row-store table compression algorithm level. This parameter is valid only when COMPRESSTYPE is set to 2. A higher compression level indicates a better table compression effect and a slower table access speed.

    Value range: –31 to 31. The default value is 0.

  • COMPRESS_CHUNK_SIZE

    Specifies the size of a row-store table compression chunk. A smaller chunk size indicates a better compression effect, and a larger data dispersion degree indicates a slower table access speed.

    Value range: subject to the page size. When the page size is 8 KB, the value can be 512, 1024, 2048, or 4096.

    Default value: 4096

  • COMPRESS_PREALLOC_CHUNKS

    Specifies the number of pre-allocated row-store table compression chunks. A larger number of pre-allocated chunks indicates a lower table compression ratio, and a smaller data dispersion degree indicates a better access performance.

    Value range: 0 to 7. The default value is 0.

  • COMPRESS_BYTE_CONVERT

    Sets the preprocessing of row-store table compression byte conversion. In some scenarios, the compression effect can be improved, but the performance deteriorates. This parameter specifies whether to perform row-column conversion during preprocessing. This parameter can be configured only when COMPRESSTYPE is set to 2.

    Value range: Boolean value. By default, this function is disabled.

  • COMPRESS_DIFF_CONVERT

    Sets the preprocessing of row-store table compression differentiation. This parameter can be set to true only when COMPRESSTYPE is set to 2 and COMPRESS_BYTE_CONVERT is set to true. In some scenarios, the compression effect can be improved, but the performance deteriorates.

    Value range: Boolean value. By default, this function is disabled.

  • segment

    The data is stored in segment-page mode. This parameter supports only row-store tables. Protection against unauthorized deletion and damage of physical files 1 to 5 is not supported.

    Value range: on and off

    Default value: off

  • enable_tde

    Creates a TDE table. The prerequisite is that the GUC parameter enable_tde is enabled, the KMS service is enabled, and the GUC parameter tde_cmk_id is correctly configured. This parameter applies only to row-store tables, segment-page tables, and unlogged tables. Temporary tables are not supported.

    Value range: on and off If this parameter is set to on, transparent data encryption is enabled. If this parameter is set to off, transparent data encryption is disabled but the encryption function will be enabled later. When a table is created, a data encryption key will be applied from KMS.

    Default value: off

  • parallel_workers

    Number of bgworker threads started when an index is created. For example, value 2 indicates that two bgworker threads are started to create indexes concurrently.

    Value range: [0,32], int type. The value 0 indicates that concurrent index creation is disabled.

    Default value: If this parameter is not set, the concurrent index creation function is disabled.

  • encrypt_algo

    Specifies the transparent data encryption algorithm. Before enabling this function, ensure that enable_tde must be set for a table. The encryption algorithm can be specified only when a table is created. Different tables support different encryption algorithms. After the table is created, the encryption algorithm cannot be changed.

    Value range: AES_128_CTR and SM4_CTR (character string).

    If enable_tde is not set, the default value is null. If enable_tde is set to on or off and encrypt_algo is not set, the value is AES_128_CTR.

  • dek_cipher

    Ciphertext of the key used for transparent data encryption. When enable_tde is enabled, the system automatically applies for ciphertext creation. You cannot specify the ciphertext. The key rotation function can be used to update the key.

    Value range: character strings.

    If encryption is disabled, the default value is null by default.

  • key_type

    TDE key type. When enable_tde is enabled, the system automatically applies for ciphertext creation. You cannot specify the ciphertext.

    Value range: character strings.

    If encryption is disabled, the default value is null by default.

  • cmk_id

    Master key ID of the database instance used for transparent data encryption. When enable_tde is enabled, the value is obtained from the GUC parameter tde_cmk_id and cannot be specified or modified by users.

    Value range: character strings.

    If encryption is disabled, the default value is null by default.

  • hasuids

    If this parameter is set to on, a unique table-level ID is allocated to a tuple when the tuple is updated.

    Value range: on and off

    Default value: off

  • collate

    Records the default collation of tables. Generally, this parameter is used only for internal storage, import, and export. You are advised not to set or modify this parameter.

    Value range: OIDs in the supported collation

    Default value: 0

  • stat_state

    Determines whether table statistics are locked. If locked, the table statistics cannot be updated.

    Value range: locked and unlock

    Default value: unlock

• CONSTRAINT constraint_name

  Specifies the name of a table constraint. The optional constraint clauses specify constraints that new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

  A table constraint is not bound to a particular column and can be applied to more than one column.

• NOT NULL

  Specifies that the column value cannot be NULL.

• NULL

  Specifies that the column value can be NULL, which is the default value.

  This clause is only provided for compatibility with non-standard SQL databases. It is not recommended.

• CHECK ( expression )

  Specifies an expression producing a Boolean result where the INSERT or UPDATE operation of new or updated rows can succeed only when the expression result is true or unknown; otherwise, an error is thrown and the database is not altered.

  

  <>NULL and !=NULL do not take effect in expressions and need to be rewritten as is NOT NULL.

• DEFAULT default_expr
  • The DEFAULT clause is used to specify a default expression for a column. The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is NULL.
  • When the default expression is not enclosed in parentheses, the following content can be specified: constants, numeric constants with positive and negative signs, and update_expr.
  • When the default expression is enclosed in parentheses, the following content can be specified: constants, numeric constants with positive and negative signs, update_expr, CURRENT_TIME/CURTIME functions, and CURRENT_DATE/CURDATE functions. (CURRENT_TIME/CURRENT_DATE can be called without parentheses.)
  • The value of update_expr can be used as the default value only for columns of the TIMESTAMP or DATETIME type, and the precision of the columns must be the same as that of update_expr.
• ON UPDATE now_expr

  The ON UPDATE clause is an attribute constraint of a column.

  When an UPDATE operation is performed on a tuple in a table, if new values of updated columns are different from old values in the table, column values with this attribute but not in updated columns are automatically updated to the current timestamp. If new values of updated columns are the same as old values in the table, column values with this attribute but not in updated columns remain unchanged. If columns with this attribute are in updated columns, column values are updated according to the specified update value.

  

  • In terms of syntax, update_expr supports four keywords: CURRENT_TIMESTAMP, LOCALTIMESTAMP, LOCALTIME, and NOW(). You can also specify or not specify the precision of a keyword with parentheses. For example, ON UPDATE CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP(5), ON UPDATE LOCALTIMESTAMP(), and ON UPDATE LOCALTIMESTAMP(6). If the keyword does not contain parentheses or contains empty parentheses, the precision is 0. The NOW keyword cannot contain parentheses. The four types of keywords are synonyms of each other and have the same attribute effect.
  • This attribute can be specified on columns of the TIMESTAMP and DATETIME types.
  • The precision specified by this attribute must be the same as that specified by the type in the corresponding column. Otherwise, an error is reported. For example, CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6));. If the precision is inconsistent, ERROR: Invalid ON UPDATE clause for "col1" is reported.
  • The same column cannot be specified for this attribute and the generated column constraint at the same time.
• [GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL]

  Creates a column as a generated column. You can specify the STORED and VIRTUAL keywords to indicate the column value storage mode of the generated column.

  • STORED: creates a stored generated column. The column value needs to be stored. When a tuple is inserted or updated, the column value is calculated and stored by a generated column expression.
  • VIRTUAL: creates a virtual generated column. The column value does not need to be stored. If a query statement involves a virtual generated column, the column value is calculated by a generated column expression.
  

  • The keywords STORED and VIRTUAL can be omitted. The default value is STORED. When m_format_dev_version is set to s2, the default value is VIRTUAL.
  • The generated expression cannot reference data other than the current row in any way. A generated expression can reference generated columns that are defined earlier than the current generated column, but cannot reference system columns. Generated column expressions cannot reference system variables, user-defined variables, or variables in stored procedures. Generated column expressions cannot reference auto-increment columns, and generated columns do not support the definition of auto-increment column attributes. Generated expressions cannot return result sets or use subqueries, aggregate functions, window functions, or user-defined functions. Functions called by generated expressions can only be immutable functions.
  • Default values cannot be specified for generated columns.
  • A virtual generated column cannot be referenced by a foreign key constraint.
  • A stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.
  • When m_format_dev_version is set to s2, the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.
  • The method of modifying and deleting generated columns is the same as that of ordinary columns. If you delete a base column that a generated column depends on, the generated column is automatically deleted. When m_format_dev_version is set to s2, you need to delete the corresponding generated column before deleting the base column on which the generated column depends.
  • Data cannot be written directly to a generated column. In the INSERT or UPDATE statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified. When inserting generated columns to a view that can be updated, do not specify values for generated columns. However, you can specify the keyword DEFAULT.
  • The permission control for generated columns is the same as that for ordinary columns.
• AUTO_INCREMENT

  Specifies an auto-increment column.

  You are advised to set an index for the auto-increment column and use the auto-increment column as the first column of the index. Otherwise, a warning is generated during table creation.

  If the value of this column is not specified (or the value of this column is set to 0, NULL, or DEFAULT), the value of this column is automatically increased by the auto-increment counter.

  If a value greater than the current auto-increment counter is inserted into or updated to this column, the auto-increment counter is updated to this value after the command is executed successfully.

  The initial auto-increment value is set by the AUTO_INCREMENT [ = ] value clause. If it is not set, the default value 1 is used.

  

  • The data type of the auto-increment column can only be integer, 4-byte or 8-byte floating point, or Boolean. An error occurs if the auto-increment continues after an auto-increment value reaches the maximum value of a column data type.
  • Each table can have only one auto-increment column.
  • You are advised to set an index for the auto-increment column and use the auto-increment column as the first column of the index. Otherwise, a warning is generated during table creation, and errors may occur when some operations are performed in a table that contains auto-increment columns.
  • The DEFAULT value cannot be specified for an auto-increment column.
  • The expression of the CHECK constraint cannot contain auto-increment columns, and the expression for generating columns cannot contain auto-increment columns.
  • You can specify that the auto-increment column can be NULL. If it is not specified, the auto-increment column contains the NOT NULL constraint by default.
  • When a table containing an auto-increment column is created, a sequence that depends on the column is created as an auto-increment counter. You are not allowed to modify or delete the sequence using sequence-related functions. You can view the value of the sequence.
  • Sequences are not created for auto-increment columns in local temporary tables.
  • The auto-increment and refresh operations of the auto-increment counter are not rolled back.
    • Before data is inserted into a table, 0 or NULL triggers auto-increment. After data is inserted into or updated to a table, the auto-increment counter is updated. If an error is reported after auto-increment, and data is not inserted into or updated to the table, the auto-increment counter does not roll back. Subsequent insert statements trigger auto-increment based on the auto-increment counter. As a result, the values of the auto-increment columns in the table are discontinuous.
    • If you insert or import reserved auto-increment cache values in batches, the values of the auto-increment columns may also be discontinuous. For details, see the description of the auto_increment_cache parameter.
• [DEFAULT] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset

  Specifies the default character set of a table. If this parameter is specified separately, the default collation of the table is set to the default collation of the specified character set.

• [DEFAULT] COLLATE [ = ] default_collation

  Specifies the default collation of a table. If this parameter is specified separately, the default character set of the table is set to the character set corresponding to the specified collation.

  For details about the collation, see Character Sets and Collations.

  

  If the character set or collation of a table is not explicitly specified and the default character set or collation of the schema is specified, the character set or collation of the table is inherited from the schema.

• Column-level unique constraint: UNIQUE [KEY]

  Specifies that the designated column or columns must have unique values across the entire table.

  UNIQUE KEY has the same semantics as UNIQUE.

• Table-level UNIQUE constraint: UNIQUE [INDEX | KEY][ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] )

  Specifies that a group of one or more columns of a table can contain only unique values.

  For the purpose of a UNIQUE constraint, NULL is not considered equal.

  column_name (length) indicates the prefix key. For details, see •column_name ( length ).

  index_name indicates the index name.

  

  If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be used as the index name.

• Column-level primary key constraint: [PRIMARY] KEY

  Table-level PRIMARY KEY constraint: PRIMARY KEY [index_name] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] )

  Specifies that one or more columns in the table require unique and non-NULL values.

  Only one primary key can be specified for a table.

• Column-level constraint: REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]

  Table-level constraint: FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]

  

  When the version compatibility parameter m_format_dev_version in an M-compatible database is set to 's1' or later, no error is report and no prompt is displayed for the syntax, but the table-level column constraint REFERENCES does not take effect.

  The foreign key constraints require that the group consisting of one or more columns in the new table should contain and match only the referenced column values in the referenced table. If refcolumn is omitted, the primary key of reftable is used. The referenced column should be the only column or primary key in the referenced table. A foreign key constraint cannot be defined between a temporary table and a permanent table.

  There are three types of matching between a reference column and a referenced column:

  • MATCH FULL: A column with multiple foreign keys cannot be NULL unless all foreign key columns are NULL.
  • MATCH SIMPLE (default): Any unexpected foreign key column can be NULL.

  In addition, when certain operations are performed on the data in the referenced table, the operations are performed on the corresponding columns in the new table. The ON DELETE clause specifies the operations to be executed after a referenced row in the referenced table is deleted. The ON UPDATE clause specifies the operation to be performed when the referenced column data in the referenced table is updated. Possible responses to the ON DELETE and ON UPDATE clauses are as follows:

  • NO ACTION (default): When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created.
  • RESTRICT: When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created.
  • CASCADE: deletes any row that references the deleted row from the new table, or update the column value of the referenced row in the new table to the new value of the referenced column.
  • SET NULL: sets the referenced columns to NULL.
  • SET DEFAULT: sets the referenced columns to their default values.
  

  • The GUC parameter foreign_key_checks controls the integrity check of foreign key constraints. foreign_key_checks can be set to on (default) or off, indicating that the integrity check of foreign key constraints is enabled or disabled, respectively.
• AS query

  If a SELECT statement starts with CTE, the part following AS must be enclosed in parentheses, for example, AS (query).

Examples

-- Drop the table.
m_db=# DROP TABLE IF EXISTS test1, test2, test3, test4;
DROP TABLE
m_db=# CREATE TABLE test1(col1 int, col2 int);
CREATE TABLE

-- There is no custom column. Only the test1 column is copied for table creation.
m_db=# CREATE TABLE test2 AS SELECT * FROM test1;
INSERT 0 0
m_db=# SHOW CREATE TABLE test2;
 Table |                               Create Table
-------+---------------------------------------------------------------------------
 test2 | SET search_path = public;                                                +
       | CREATE TABLE test2 (                                                     +
       |     col1 integer,                                                        +
       |     col2 integer                                                         +
       | )                                                                        +
       | CHARACTER SET = "UTF8" COLLATE = "utf8mb4_general_ci"                    +
       | WITH (orientation=row, compression=no, storage_type=USTORE, segment=off);
(1 row)

-- Custom columns are contained. The new table fields contain custom columns and SELECT result columns.
m_db=# CREATE TABLE test3(col0 int, col2 varchar(55) not null) AS SELECT * FROM test1;
INSERT 0 0
m_db=# SHOW CREATE TABLE test3;
 Table |                                 Create Table
-------+-------------------------------------------------------------------------------
 test3 | SET search_path = public;                                                    +
       | CREATE TABLE test3 (                                                         +
       |     col0 integer,                                                            +
       |     col1 integer,                                                            +
       |     col2 varchar(55) CHARACTER SET `UTF8` COLLATE utf8mb4_general_ci NOT NULL+
       | )                                                                            +
       | CHARACTER SET = "UTF8" COLLATE = "utf8mb4_general_ci"                        +
       | WITH (orientation=row, compression=no, storage_type=USTORE, segment=off);
(1 row)

-- Example of table creation options
m_db=# CREATE TABLE test4(col0 int, col2 varchar(55)) CHARACTER SET gb18030 COLLATE gb18030_bin WITH(storage_type=astore) AS SELECT * FROM test1;
INSERT 0 0
m_db=# SHOW CREATE TABLE test4;
 Table |                           Create Table
-------+------------------------------------------------------------------
 test4 | SET search_path = public;                                       +
       | CREATE TABLE test4 (                                            +
       |     col0 integer,                                               +
       |     col1 integer,                                               +
       |     col2 varchar(55) CHARACTER SET `GB18030` COLLATE gb18030_bin+
       | )                                                               +
       | CHARACTER SET = "GB18030" COLLATE = "gb18030_bin"               +
       | WITH (orientation=row, storage_type=astore, compression=no);
(1 row)

-- Drop the table.
m_db=# DROP TABLE IF EXISTS test1, test2, test3, test4;
DROP TABLE