CREATE TABLE

Description

Creates an initially empty table in the current database. The table will be owned by the creator.

Precautions

• If an error occurs during table creation, after it is fixed, the system may fail to delete the empty disk files (whose size is 0) created before the last automatic clearance. This problem seldom occurs and does not affect system running of the database.
• When JDBC is used, the DEFAULT value can be set through PrepareStatement.
• A user granted the CREATE ANY TABLE permission can create tables in the public and user schemas.
• Prefix keys are not supported in primary key indexes.

Syntax

CREATE [ [LOCAL | GLOBAL] TEMPORARY | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    {({ column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset_name ] [ compress_mode ] [ 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][...]]
        | LIKE source_table }
        [, ... ])}
    [ table_option ][[,...]|[...]]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
    [ TABLESPACE tablespace_name ];

CREATE [ [LOCAL | GLOBAL] TEMPORARY ] TABLE [IF NOT EXISTS] table_name LIKE source_table;

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.
  • Unlogged tables have no data on the standby node, and an error is reported when an unlogged table is queried on the standby node.

  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.

• GLOBAL | LOCAL

  When creating a temporary table, you can specify the GLOBAL or LOCAL keyword before TEMP or TEMPORARY. If the keyword GLOBAL is specified, a global temporary table will be created for an M-compatible database. Otherwise, a local temporary table will be created.

• TEMPORARY

  If this keyword is specified, the created table is a temporary table.

  Temporary tables are classified into global temporary tables and local temporary tables. If the keyword GLOBAL is specified when a temporary table is created, the table is a global temporary table. Otherwise, the table is a local temporary table.

  The metadata of the global temporary table is visible to all sessions. After the sessions end, the metadata still exists. The user data, indexes, and statistics of a session are isolated from those of another session. Each session can only view and modify the data committed by itself. There are two schemas for global temporary tables: ON COMMIT PRESERVE ROWS and ON COMMIT PRESERVE ROWS. In the session-based schema ON COMMIT PRESERVE ROWS, user data is automatically cleared when a session ends. In the transaction-based schema ON COMMIT DELETE ROWS, user data is automatically cleared when a commit or rollback operation is performed. If the ON COMMIT option is not specified during table creation, the session-based schema is used by default. Different from local temporary tables, you can specify a schema that does not start with pg_temp_ when creating a global 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 temporary tables in the current session as long as the connected database node in the session 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.
  • If global temporary tables or indexes are being used by other sessions, do not perform ALTER or DROP on the tables or indexes.
  • The DDL of a global temporary table affects only the user data and indexes of the current session. For example, TRUNCATE, REINDEX, and ANALYZE are valid only for the current session.
  • You can set the GUC parameter max_active_global_temporary_table to determine whether to enable the global temporary table function. If max_active_global_temporary_table is set to 0, the global temporary table function is disabled.
  • 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.
  • Global temporary tables do not respond to autovacuum. In persistent connection scenarios, it is advisable to use global temporary tables with the ON COMMIT DELETE ROWS clause or conduct regular vacuum operations manually. Failure to do so may result in Clogs not being recycled.
  • Global temporary tables do not support the following scenarios:
    • Global temporary sequences cannot be created. Global temporary tables of each session use shared sequences, which can only ensure uniqueness but not continuity.
    • Global temporary views cannot be created.
    • Partitioned tables cannot be created.
    • Hash bucket tables cannot be created.
    • Extended statistics are not supported.
• 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.

• compress_mode

  Specifies whether to compress a table column. The option specifies the compression algorithm preferentially used by table columns. Row-store tables do not support compression. This is unavailable in M-compatible databases.

  Value range: DICTIONARY

• {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.
• LIKE source_table

  Specifies a table, known as the source table, for the target table to inherit the following information:

  • Column names and their data types
  • NOT NULL constraint, CHECK constraint, PRIMARY KEY constraint, UNIQUE KEY constraint, indexes, default values, and ON UPDATE attribute
  • Comments on the table, columns, indexes, and constraints
  • Partition information
  • Storage parameters of the table and columns

  The new table and the original table are decoupled after creation is complete. This means that any subsequent modifications to the source table will not be propagated to the target table, and data from the target table will not be included in scans of the source table.

  The copied columns and constraints are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause, an error is reported.

  

  If a table creation statement contains the LIKE, WITH, and PARTITION BY clauses, the WITH and partition information specified in the SQL statement will be used during table creation, instead of the source table information. If both CHARSET and COMMENT are specified, the information in the source table is used.

• 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.

  • 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

    Specifies that the table is an encryption table. The database automatically encrypts the data in the encryption table before storing it. Before using this parameter, ensure that TDE function has been enabled using the GUC parameter enable_tde and the information for accessing the key service has been set using the GUC parameter tde_key_info. For details about how to use this parameter, see section "Transparent Data Encryption" in Feature Guide. This parameter supports only row-store tables, segment-page tables, temporary tables, and unlogged tables.

    Value range: on and off When enable_tde is set to on, key_type, tde_cmk_id, and dek_cipher are automatically generated by the database and cannot be manually specified or modified.

    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 encryption algorithm of the encryption table. This parameter must be used together with enable_tde.

    Value range: AES_128_CTR and SM4_CTR (character string).

    Default value: the value assigned by table_algorithm in the tde_encrypt_config parameter.

  • dek_cipher

    Specifies the DEK ciphertext. After the enable_tde parameter is set for a table, the database automatically generates a data key.

    Value range: character strings.

    Default value: empty.

  • key_type

    Specifies the master key type. After the enable_tde parameter is set for a table, the database automatically obtains the master key type from the GUC parameter tde_key_info.

    Value range: character strings.

    Default value: empty.

  • cmk_id

    Specifies the master key ID. After the enable_tde parameter is set for a table, the database automatically obtains the master key ID from the GUC parameter tde_key_info.

    Value range: character strings.

    Default value: empty.

  • 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

• ON COMMIT { PRESERVE ROWS | DELETE ROWS }

  ON COMMIT determines what to do when you commit a temporary table creation operation. The options are PRESERVE ROWS and DELETE ROWS.

  • 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.
• TABLESPACE tablespace_name

  Specifies the tablespace where the new table is created. If not specified, the default tablespace is used.

  Note: You need to create or delete tablespaces in a non-M-compatible database.

• CONSTRAINT constraint_name

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

  There are two ways to define constraints:

  • A column constraint is defined as part of a column definition, and it is bound to a particular column.
  • 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 are invalid to expressions and need to be rewritten as is NOT NULL.
  • The comparison results of floating-point numbers may be inconsistent between the tables with the CHECK constraint created when the precision transfer function is disabled (m_format_behavior_compat_options is not set to enable_precision_decimal) and the tables with the CHECK constraint created after the precision transfer function is enabled. If you want to use a table with the CHECK constraint created when the precision transfer function is not enabled while the function is now enabled, you are advised to use the ALTER TABLE syntax to redefine the CHECK constraint after enabling the precision transfer function.

    m_db=# SET m_format_behavior_compat_options='';
    SET
    m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), CHECK(a/b=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    ERROR:  New row in relation "mm1" violates check constraint "mm1_check".
    DETAIL:  N/A
    m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
    SET
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# CREATE TABLE mm2(a float(10, 4), b floaT(5, 3), CHECK(a/b=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm2 VALUES(1.2, 0.7);
    ERROR:  New row in relation "mm2" violates check constraint "mm2_check".
    DETAIL:  N/A
    
    m_db=# DROP TABLE mm1, mm2;
    CREATE TABLE

• 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.
  • This attribute cannot be specified for the partition key in a partitioned table.
• [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 stored generated column can be used as a part of a partition key. In this case, ALTER TABLE cannot be used to modify a stored generated column.
  • A virtual generated column cannot be used as part of a partition key.
  • A virtual generated column cannot be referenced by a foreign key constraint.
  • Indexes cannot be created on virtual generated columns.
  • 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.
  • When a stored generated column does not meet its CHECK constraint and m_format_dev_version is set to s2, the ALTER TABLE statement fails to be executed to change the stored generated column and an error is reported.
  • The permission control for generated columns is the same as that for ordinary columns.
  • The comparison results of floating-point numbers may be inconsistent between the tables with columns generated when the precision transfer function is disabled (m_format_behavior_compat_options is not set to enable_precision_decimal) and the tables with columns generated after the precision transfer function is enabled. If you want to use a table with columns generated when the precision transfer function is not enabled while the function is now enabled, you are advised to use the ALTER TABLE syntax to redefine the generated columns after enabling the precision transfer function.

    m_db=# SET m_format_behavior_compat_options='';
    SET
    m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# SELECT * FROM mm1;
       a    |   b   | c 
    --------+-------+---
     1.2000 | 0.700 | 0
    (1 row)
    
    m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
    SET
    m_db=# CREATE TABLE mm2(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# INSERT INTO mm2 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# SELECT * FROM mm1;
       a    |   b   | c 
    --------+-------+---
     1.2000 | 0.700 | 0
     1.2000 | 0.700 | 1
    (2 rows)
    
    m_db=# SELECT * FROM mm2;
       a    |   b   | c 
    --------+-------+---
     1.2000 | 0.700 | 0
    (1 row)
    
    m_db=# DROP TABLE mm1, mm2;
    DROP TABLE

• 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, for example, ALTER TABLE EXCHANGE PARTITION.
  • 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. Do not make other sequences depend on or associate with this auto-increment column.
  • 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] index_parameters

  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 ] }[, ... ] ) index_parameters

  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 index_parameters

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

  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.
• USING INDEX TABLESPACE tablespace_name

  Specifies a tablespace in which an index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, the index is created in default_tablespace. If default_tablespace is empty, the default tablespace of the database is used.

  

  This attribute is not supported when the version compatibility control parameter m_format_dev_version of an M-compatible database is set to 's1'.

Examples

• Temporary table

  -- Create a temporary table.
  m_db=# CREATE TEMPORARY TABLE test_t1(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                       -- Province
      country   VARCHAR(30) DEFAULT 'China'        -- Country
  );
  
  -- Insert data to the current session.
  m_db=# INSERT INTO test_t1 VALUES ('0000009','Jack','Guangzhou','China');
  
  -- Data in the temporary table is valid only in the current transaction. Therefore, the table does not contain data in another session.
  m_db=# SELECT * FROM test_t1;
   id | name | age 
  ----+------+-----
  (0 rows)

  -- Create a temporary table in a transaction and specify that this table is deleted when the transaction is committed.
  m_db=# START TRANSACTION;
  m_db=# CREATE TEMPORARY TABLE test_t2(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                       -- Province
      country   VARCHAR(30) DEFAULT 'China'        -- Country
  ) ON COMMIT DELETE ROWS;
  m_db=# INSERT test_t2 VALUES('aid','aname','aprovince','China');
  m_db=# COMMIT;
  m_db=# SELECT * FROM test_t2;
   id | name | province | country
  ----+------+----------+---------
  (0 rows)
  -- Drop the table.
  m_db=# DROP TABLE test_t1;
  m_db=# DROP TABLE test_t2;

• IF NOT EXIST keyword

  If this keyword is used, NOTICE is reported when the table does not exist. If this keyword is not used, ERROR is reported. In either case, the table fails to be created.

  m_db=# CREATE TABLE test_t3(id INT);
  -- Create a table named test_t3.
  m_db=# CREATE TABLE test_t3(id INT);
  ERROR:  relation "test_t3" already exists in schema "public"
  DETAIL:  creating new table with existing name in the same schema
  
  -- Use the IF NOT EXISTS keyword.
  m_db=# CREATE TABLE IF NOT EXISTS test_t3(id INT);
  NOTICE:  relation "test_t3" already exists, skipping
  CREATE TABLE
  
  -- Drop the table.
  m_db=# DROP TABLE test_t3;

Examples of Creating a Table and Adding Constraints to the Table

• NOT NULL constraints
  If no value is specified for a column with a NOT NULL constraint when data is added, an error is reported. You can add not null constraints to multiple columns in a table.

  -- Create a table and add a not null constraint to the id column.
  m_db=# CREATE TABLE test_t4(
      id       CHAR(7) NOT NULL,
      name     VARCHAR(20),
      province VARCHAR(60),                       -- Province
      country   VARCHAR(30) DEFAULT 'China'        -- Country
  );
  
  -- If the value of id is not specified or is NULL during data insertion, the NOT NULL constraint is triggered. As a result, the insertion fails.
  m_db=# INSERT INTO test_t4 (name,province) VALUES ('scott','Shanghai');
  ERROR:  null value in column "id" violates not-null constraint
  DETAIL:  Failing row contains (null, scott, Shanghai, China) 
  
  -- Drop the table.
  m_db=# DROP TABLE test_t4;

• Unique constraint
  The keyword UNIQUE is used to add a unique constraint to a column. When data is inserted, the constraint is triggered if the column is duplicate. Multiple NULL values are not duplicate values. When a unique constraint is added, a unique index is automatically added. You can add unique constraints to multiple columns in a table.

  -- Create a table and add a unique constraint.
  m_db=# CREATE TABLE test_t5(
      id       CHAR(7) UNIQUE,
      name     VARCHAR(20),
      province VARCHAR(60),                       -- Province
      country   VARCHAR(30) DEFAULT 'China'        -- Country
  );
  
  -- You can also use the following method to manually name unique constraints and add constraints to multiple columns:
  m_db=# CREATE TABLE test_t6(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                        -- Province
      country   VARCHAR(30) DEFAULT 'China',        -- Country
      CONSTRAINT unq_test_id UNIQUE (id,name)
  );

  -- When data with duplicate IDs is inserted, constraints are triggered. As a result, the insertion fails.
  m_db=# INSERT INTO test_t5(id) VALUES('0000010');
  INSERT 0 1
  m_db=# INSERT INTO test_t5(id) VALUES('0000010');
  ERROR:  duplicate key value violates unique constraint "test_t5_id_key"
  DETAIL:  Key (id)=(0000010) already exists.

  -- The constraint is not triggered when data whose id is NULL is inserted for multiple times.
  m_db=# INSERT INTO test_t5(id) VALUES (NULL);
  INSERT 0 1
  m_db=# INSERT INTO test_t5(id) VALUES (NULL);
  INSERT 0 1
  
  m_db=# SELECT * FROM test_t5;
     id    | name | province | country 
  ---------+------+----------+--------
   0000010 |      |          | China
           |      |          | China
           |      |          | China
  
  -- Drop the table.
  m_db=# DROP TABLE test_t5;
  m_db=# DROP TABLE test_t6;

• Primary key constraints

  The keyword PRIMARY KEY is used to add a primary key constraint to a column. The column must be unique and cannot be null. When a primary key constraint is added, a unique index is automatically created for the table, and a NOT NULL constraint is automatically added for the column.

  Only one primary key constraint can be defined in each table.

  -- Create a table and add a primary key constraint.
  m_db=# CREATE TABLE test_t6(
      id       CHAR(7) PRIMARY KEY,
      name     VARCHAR(20),
      province VARCHAR(60),                       -- Province
      country   VARCHAR(30) DEFAULT 'China'        -- Country
  );
  m_db=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','july','Beijing');
  
  -- You can also use the following method to manually name unique constraints and add constraints to multiple columns:
  m_db=# CREATE TABLE test_t7(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                       -- Province
      country   VARCHAR(30) DEFAULT 'China',        -- Country
      CONSTRAINT pk_test_t6_id PRIMARY KEY (id,name)
  );

  -- Insert data whose id is NULL to trigger the constraint.
  m_db=# INSERT INTO test_t6 (id,name,province) VALUES (NULL,'july','Beijing'); 
  ERROR:  null value in column "id" violates not-null constraint
  DETAIL:  Failing row contains (null, july, Beijing, China).
  
  -- Insert data with duplicate id values to trigger the constraint.
  m_db=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','ben','Shanghai');
  ERROR:  duplicate key value violates unique constraint "test_t6_pkey"
  DETAIL:  Key (id)=(0000001) already exists.

  -- Drop the table.
  m_db=# DROP TABLE test_t6;
  m_db=# DROP TABLE test_t7;

• Check constraints

  The keyword CHECK adds a check constraint to a column. The check constraint must reference one or more columns in the table, and the result returned by the expression must be a Boolean value. Currently, expressions cannot contain subqueries. Both check and not null constraints can be defined for the same column.

  -- Create a table and add a check constraint.
  m_db=# CREATE TABLE test_t8 (
      id       CHAR(7),
      name     VARCHAR(20),
      age      INT CHECK(age > 0 AND age < 150)
  );
  
  -- You can also use the following SQL statements to name a check constraint and add the check constraint for one or more columns:
  m_db=# CREATE TABLE test_t9 (
      id       CHAR(7),
      name     VARCHAR(20),
      age      INT,
      CONSTRAINT chek_test_t8_age CHECK(age > 0 AND age < 150)
  );

  -- If a value that does not comply with the expression is inserted, the check constraint is triggered. As a result, the insertion fails.
  m_db=# INSERT INTO test_t8 (id,name,age) VALUES ('0000007','scott',200);
  ERROR:  new row for relation "test_t8" violates check constraint "test_t8_age_check"
  DETAIL:  N/A

  -- Drop the table.
  m_db=# DROP TABLE test_t8;
  m_db=# DROP TABLE test_t9;

• Foreign key constraint

  When two tables contain one or more public columns, you can use foreign key constraints to forcibly join the two tables.

  • FOREIGN KEY: columns that are related to the referenced table.
  • REFERENCES: columns that are related to the referenced table and the original table.

  Foreign key constraints have the following features:

  • A column defined as a foreign key constraint can contain only the values or NULL of the referenced columns in other tables.
  • You can define foreign key constraints for one or more columns.
  • A column that defines a foreign key constraint and the corresponding referenced column can exist in the same table, which is called self-reference.
  • Both foreign keys and not null constraints can be defined for the same column.
  • Columns to be applied in the main table must have primary key constraints or unique constraints.

  -- Create a department table.
  m_db=# CREATE TABLE dept(
      deptno INT PRIMARY KEY,
      loc VARCHAR(200)
  );
  
  -- Create an employee table and add a foreign key constraint.
  m_db=# CREATE TABLE emp(
      empno INT,
      name VARCHAR(50),
      deptno INT,
      CONSTRAINT fk_emp FOREIGN KEY (deptno) REFERENCES dept(deptno)
  );

  -- Insert data into the department table.
  m_db=# INSERT INTO dept VALUES (10,'Beijing');
  m_db=# INSERT INTO dept VALUES (20,'Beijing');
  m_db=# INSERT INTO dept VALUES (30,'Shanghai');
  
  -- Insert the deptno data that can be found in the department table into the employee table.
  m_db=# INSERT INTO emp VALUES (1,'Bob',10);
  
  -- Insert data whose deptno is NULL into the employee table.
  m_db=# INSERT INTO emp VALUES (2,'Scott',NULL);
  
  -- Insert the deptno data that cannot be found in the department table into the employee table.
  m_db=# INSERT INTO emp VALUES (1,'Jack',999);
  ERROR:  insert or update on table "emp" violates foreign key constraint "fk_emp"
  DETAIL:  Key (deptno)=(999) is not present in table "dept".

  -- View data.
  m_db=# SELECT * FROM emp;
   empno | name  | deptno 
  -------+-------+--------
       1 | Bob   |     10
       2 | Scott |       
  (2 rows)

  -- Drop the table.
  m_db=# DROP TABLE emp;
  m_db=# DROP TABLE dept;

Helpful Links

ALTER TABLE and DROP TABLE

Suggestions

• UNLOGGED
  • The unlogged table and its indexes do not use the WAL mechanism during data writing. Their write speed is much higher than that of ordinary tables. Therefore, they can be used for storing intermediate result sets of complex queries to improve query performance.
  • The unlogged table has no primary/standby mechanism. In case of system faults or abnormal breakpoints, data loss may occur. Therefore, the unlogged table cannot be used to store basic data.
• TEMPORARY
  • A non-global temporary table is visible only in the current session and is automatically dropped at the end of the session.
• LIKE
  • The new table automatically inherits all column names, data types, and not null constraints from this table. The new table is irrelevant to the original table after the creation.
• ORIENTATION ROW
  • Creates a row-store table. Row store applies to the OLTP service, which has many interactive transactions. An interaction involves many columns in the table. Using row store can improve the efficiency.