Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Reference/ SQL Syntax/ C/ CREATE TABLE

Updated on 2024-08-20 GMT+08:00

View PDF

CREATE TABLE

Description

Creates an initially empty table in the current database. The table will be owned by the creator. Row-store tables are created by default.

Precautions

If an error occurs during table creation, after it is fixed, the system may fail to delete the empty disk files 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 with the CREATE ANY TABLE permission can create tables in the public and user schemas. To create a table that contains serial columns, you must also obtain the CREATE ANY SEQUENCE permission to create sequences.
The XML type cannot be used as a primary key or foreign key.
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.
The number of table constraints cannot exceed 32,767.

Syntax

Create a table.

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    {({ column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | LIKE source_table [ like_option [...] ] }
        [, ... ])
    | LIKE source_table }
    [ table_option [ [ , ] ... ] ]
    [ 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 ];

table_option is as follows:

{ COMMENT [ = ] 'string' |
  AUTO_INCREMENT [ = ] value |
  [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset |
  [ DEFAULT ] COLLATE [ = ] default_collation |
  ENGINE [ = ] { InnoDB | 'InnoDB' | "InnoDB" } }

column_constraint is as follows:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) |
  DEFAULT default_expr |
  ON UPDATE update_expr |
  GENERATED ALWAYS AS ( generation_expr ) [STORED] |
  GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] |
  AUTO_INCREMENT |
  COMMENT 'string' |
  UNIQUE [KEY] index_parameters |
  PRIMARY KEY index_parameters |
  ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
      [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

compress_mode of a column is as follows:

{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }

table_constraint is as follows:

[ CONSTRAINT [ constraint_name ] ]
{ CHECK ( expression ) |
  UNIQUE [ index_name ] [ USING method ] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters |
  PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters 
  |
  FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
      [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
{ [ COMMENT 'string' ] [ ... ] }

like_option is as follows:

{ INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | UPDATE | IDENTITY | ALL }

index_parameters is as follows:

[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

update_expr is as follows:

{ CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

identity_options is as follows:

[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE | NOMINVALUE] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE] [ START [ WITH ] start ] [ CACHE cache | NOCACHE ] [ [ NO ] CYCLE | NOCYCLE] [ SCALE [ EXTEND | NO EXTEND] | NOSCALE ]

Parameters

UNLOGGED
If this keyword is specified, the created table is an unlogged table. Data written to unlogged tables is not written to the WALs, which makes them considerably faster than ordinary tables. However, an unlogged table is automatically truncated after conflicts, OS restart, database restart, primary/standby switchover, power-off, or abnormal shutdown, incurring data loss risks. 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 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. If the keyword GLOBAL is specified, GaussDB creates a global temporary table. Otherwise, GaussDB creates a local temporary table.
TEMPORARY | TEMP
If TEMP or TEMPORARY 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. Global temporary tables have two schemas: ON COMMIT PRESERVE ROWS and ON COMMIT PRESERVE ROWS. In session-based ON COMMIT PRESERVE ROWS schema, user data is automatically cleared when a session ends. In transaction-based ON COMMIT DELETE ROWS schema, user data is automatically cleared when the commit or rollback operation is performed. If the ON COMMIT option is not specified during table creation, the session level 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 automatically dropped at the end of the current 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. TEMP is equivalent to TEMPORARY.
- 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 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.
- If global temporary tables and indexes are being used by other sessions, do not perform ALTER or DROP (except the ALTER INDEX index_name REBUILD command).
- 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.
- The global temporary table does not respond to automatic clearance. In persistent connection scenarios, you are advised to use the global temporary table in the ON COMMIT DELETE ROWS clause or periodically and manually execute the VACUUM statement. Otherwise, Clogs may not be reclaimed.
- 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.

Some processing logic of materialized views determines whether a table is the log table of a materialized view or a table associated with a materialized view based on the table name prefix. Therefore, do not create a table whose name prefix is mlog_ or matviewmap_. Otherwise, some functions of the table are affected.
column_name
Specifies the name of a column to be created in the new table.
constraint_name
Specifies the name of the constraint specified during table creation.

The constraint_name is optional in B-compatible mode (sql_compatibility = 'B'). For other modes, constraint_name must be added.
index_name
Index name
- The index_name is supported only in B-compatible databases (that is, sql_compatibility set to 'B').
- For foreign key constraints, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
- For a unique key constraint, if both constraint_name and index_name are specified, index_name is used as the index name.
USING method
Specifies the name of the index method to be used.

For details about the value range, see USING method in Parameters.
- The USING method is supported only in B-compatible databases (that is, sql_compatibility set to 'B').
- In B-compatible mode, if USING method is not specified, the default index method is btree for Astore or UB-tree for Ustore.
- If the storage mode of a table is Ustore and the constraint in the SQL statement is specified as USING BTREE, the underlying layer automatically creates the constraint as USING UBTREE.
ASC | DESC
ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

The ASC|DESC is supported only in B-compatible databases (that is, sql_compatibility set to 'B').
expression
Specifies an expression index constraint based on one or more columns of the table. It must be written in parentheses.

Expression indexes in the UNIQUE constraint are supported only in B-compatible databases (that is, sql_compatibility set to 'B').
data_type
Specifies the data type of the column.
compress_mode
Specifies whether to compress a table column. The option specifies the algorithm preferentially used by table columns. Row-store tables do not support compression.

Value range: DELTA, PREFIX, DICTIONARY, NUMSTR, and NOCOMPRESS
CHARACTER SET | CHARSET charset
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.

This syntax is supported only in B-compatible databases (that is, sql_compatibility set to 'B').

COLLATE collation

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. In B-compatible databases (that is, sql_compatibility = 'B'), utf8mb4_bin, utf8mb4_general_ci, utf8mb4_unicode_ci, binary, gbk_chinese_ci, gbk_bin, gb18030_chinese_ci, and gb18030_bin are also supported.

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.
Except the binary character set and collation, only the character set that is the same as the database encoding can be specified.
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. If the default character set or collation of a table does not exist, the character set and collation of table columns inherit the character set and collation of the current database when b_format_behavior_compat_options contains 'default_collation'.

**Table 1** Character sets and collation supported in mode B (sql_compatibility = 'B')
Collation	Character Set	Description
utf8mb4_general_ci	utf8mb4 (utf8)	The general collation is used, which is case-insensitive.
utf8mb4_unicode_ci	utf8mb4 (utf8)	The general collation is used, which is case-insensitive.
utf8mb4_bin	utf8mb4 (utf8)	The binary collation is used, which is case-sensitive.
binary	binary	The binary collation is used.
gbk_chinese_ci	gbk	The Chinese collation is used.
gbk_bin	gbk	The binary collation is used, which is case-sensitive.
gb18030_chinese_ci	gb18030	The Chinese collation is used.
gb18030_bin	gb18030	The binary collation is used, which is case-sensitive.
utf8mb4_0900_ai_ci	utf8mb4	The unicode collation algorithm (UCA) rule is used, which is case-insensitive.
utf8_general_ci	utf8	The general collation is used, which is case-insensitive.
utf8_bin	utf8	The binary collation is used, which is case-sensitive.

LIKE source_table [ like_option ... ]
Specifies a table from which the new table automatically copies all column names, their data types, and their NOT NULL constraints.

The new table and the original table are decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original 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.
- The default expressions are copied from the original table to the new table only if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having default values NULL.
- If INCLUDING UPDATE is specified, the ON UPDATE CURRENT_TIMESTAMP attribute of the source table column is copied to the new table column. By default, the generated expression is not copied.
- If INCLUDING GENERATED is specified, the generated expression of the source table column is copied to the new table. By default, the generated expression is not copied.
- The CHECK constraints are copied from the original table to the new table only when INCLUDING CONSTRAINTS is specified. Other types of constraints are never copied to the new table. NOT NULL constraints are always copied to the new table. These rules also apply to column constraints and table constraints.
- Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified.
- STORAGE settings for the source column definitions are copied only if INCLUDING STORAGE is specified. The default behavior is to exclude STORAGE settings.
- If INCLUDING COMMENTS is specified, comments for the copied columns, constraints, and indexes are copied. The default behavior is to exclude comments.
- If INCLUDING PARTITION is specified, the partition definitions of the source table are copied to the new table, and the new table no longer uses the PARTITION BY clause. By default, the partition definition of the source table is not copied. If the source table has an index, you can use the INCLUDING PARTITION INCLUDING INDEXES syntax. If only INCLUDING INDEXES is used for a partitioned table, the target table will be defined as an ordinary table, but the index is a partitioned index. In this case, an error will be reported because ordinary tables do not support partitioned indexes.
- If INCLUDING RELOPTIONS is specified, the new table will copy the storage parameter (that is, WITH clause) of the source table. The default behavior is to exclude partition definition of the storage parameter of the original table.
- If INCLUDING IDENTITY is specified, the identity of the source table will be copied to the new table, and a SEQUENCE with the same SEQUENCE parameter as that of the source table will be created. By default, the identity of the source table is not copied.
- INCLUDING ALL contains INCLUDING DEFAULTS, INCLUDING UPDATE, INCLUDING GENERATED, INCLUDING CONSTRAINTS, INCLUDING INDEXES, INCLUDING STORAGE, INCLUDING COMMENTS, INCLUDING PARTITION, INCLUDING RELOPTIONS, and INCLUDING IDENTITY.
- If the original table contains a sequence with the SERIAL, BIGSERIAL, SMALLSERIAL, or LARGESERIAL data type, or a column in the original table is a sequence by default and the sequence is created for this table (created by using CREATE SEQUENCE ... OWNED BY), these sequences will not be copied to the new table, and another sequence specific to the new table will be created. This is different from earlier versions. To share a sequence between the source table and new table, create a shared sequence (do not use OWNED BY) and set a column in the source table to this sequence.
- You are advised not to set a column in the source table to the sequence specific to another table especially when the table is distributed in specific node groups, because doing so may result in CREATE TABLE ... LIKE execution failures. In addition, doing so may cause the sequence to become invalid in the source sequence because the sequence will also be deleted from the source table when it is deleted from the table that the sequence is specific to. To share a sequence among multiple tables, you are advised to create a shared sequence for them.
- EXCLUDING of a partitioned table must be used together with INCLUDING ALL, for example, INCLUDING ALL EXCLUDING DEFAULTS, except for DEFAULTS of the source partitioned table.
- The CREATE TABLE table_name LIKE source_table syntax is supported only when sql_compatibility is set to 'B' (B-compatible database), b_format_version is set to 5.7, and b_format_dev_version is set to s2.
- In a B-compatible database, if b_format_version is set to 5.7 and b_format_dev_version is set to s2, INCLUDING and EXCLUDING cannot be specified. In this case, it is equivalent to specify INCLUDING ALL by default.
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.

This clause takes effect only when sql_compatibility is set to 'B'.
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.
- This clause takes effect only when sql_compatibility is set to 'B'.
- 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.
ENGINE
Supported in B-compatible mode and used only for syntax adaptation. Only InnoDB can be set and no actual effect is achieved.

The ENGINE syntax cannot be used in the CREATE TABLE table_name LIKE source_table syntax.
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.

When using Numeric of arbitrary precision to define a column, specify precision p and scale s. When precision and scale are not specified, the input will be displayed.

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 can be set only once.
  
  Value range:
  - ROW indicates that table data is stored in rows.
    ROW 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 * 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 range: 2 to 128
  
  Default value: 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: For row-store tables, the valid values are YES and NO.
  
  Default value: 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
  
  Default value: 0
- segment
  The data is stored in segment-page mode. This parameter supports only row-store tables. Column-store tables are not supported. 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
  Indicates that the table is an encrypted table. The database automatically encrypts the data in the encryption table before storing it. Before using this parameter, ensure that TDE 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 applies only to 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
- encrypt_algo
  Specifies the encryption algorithm for an encrypted table. This parameter must be used together with enable_tde.
  
  Value range: a string. The value can be AES_128_CTR or SM4_CTR.
  
  Default value: null if enable_tde and AES_128_CTR if enable_tde is set.
- dek_cipher
  Specifies the DEK ciphertext. After a user sets the enable_tde parameter for a table, the database automatically generates a data key.
  
  Value range: a string
  
  Default value: null
- key_type
  Specifies the type of the master key. 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: a string
  
  Default value: null
- cmk_id
  Specifies the ID of the master key. 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: a string
  
  Default value: null
- 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.
- 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
  In B-compatible databases (sql_compatibility = 'B'), this parameter is used to record the default collation of tables. Generally, this parameter is used only for internal storage, import, and export. You are advised not to specify or modify this parameter.
  
  Value range: OIDs in the collation that is independently supported in B-compatible databases.
  
  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
- statistic_granularity
  Records the default partition_mode when the table analyzes statistics. For details about partition_mode, see ANALYZE|ANALYSE. This parameter is invalid for non-partitioned tables.
  
  Value range: See the value range of partition_mode.
  
  Default value: AUTO
- immediate_delete
  This parameter can be set only for vector database tables. After this parameter is set, index data is searched and deleted immediately when heap table data is deleted in the Astore scenario. If this parameter is set for an ordinary table, the Astore deletion and update performance deteriorates.
  
  Value range: false and true
  
  Default value: false
WITHOUT OIDS
It is equivalent to WITH(OIDS=FALSE).
ON COMMIT { PRESERVE ROWS | DELETE ROWS }
ON COMMIT determines what to do when you commit a temporary table creation operation. Currently, 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
If you specify COMPRESS in the CREATE TABLE statement, the compression feature is triggered in case of a bulk insert operation. 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. For example, CREATE TABLE t1 (a int) ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 3 DAY OF NO MODIFICATION ON (a != 0) indicates that the t1 table is created and the advanced compression policy is added: rows that are not modified in three days and a != 0 is specified.
- 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 the tablespace where the new table is created. If not specified, the default tablespace is used.
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 but can apply to more than one column.
NOT NULL
Forbids NULL values in columns.
NULL
Allows to contain NULL values. This is the default setting.

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 rows can succeed only when the expression result is TRUE or UNKNOWN; otherwise, an error is thrown and the database is not altered.

A CHECK constraint specified as a column constraint should reference only the column's value, while an expression in a table constraint can reference multiple columns.

<>NULL and !=NULL are invalid in an expression. Change them to IS NOT NULL.
DEFAULT default_expr
Assigns a default data value to a column. The value can be any variable-free expressions. (Subqueries and cross-references to other columns in the current table are not allowed.) The data type of the default expression must match the data type of the 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.
ON UPDATE update_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.
- This attribute can be specified only in B-compatible database 5.7 (that is, sql_compatibility is set to 'B', b_format_version is set to '5.7', and b_format_dev_version is set to 's1').
- In terms of syntax, update_expr supports three keywords: CURRENT_TIMESTAMP, LOCALTIMESTAMP, 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 three types of keywords are synonyms of each other and have the same attribute effect.
- This attribute can be specified only for columns of the following types: timestamp, datetime, date, time without time zone, smalldatetime, and abstime.
- The CREATE TABLE AS syntax does not inherit the column attributes.
- The CREATE TABLE LIKE syntax can use INCLUDING UPDATE or EXCLUDING UPDATE to inherit or exclude a constraint. The LIKE syntax is inherited from the LIKE syntax of PostgreSQL. Currently, the ILM policy information of the old table cannot be copied.
- The precision specified by this attribute can be different from the precision specified by the type in the corresponding column. After the column value is updated through this attribute, the minimum precision is displayed. For example, CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(3));. If the UPDATE syntax triggers the attribute to take effect, three decimal places in the value of col1 are displayed after the update.
- 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]
This clause creates a column as a generated column. The value of the generated column is calculated by generation_expr when data is written (inserted or updated). STORED indicates that the value of the generated column is stored as a common column.
- The STORED keyword can be omitted, which has the same semantics as not omitting STORED.
- The generation expression cannot refer to data other than the current row in any way. The generation expression cannot reference other generation columns or system columns. The generation expression cannot return a result set. No subquery, aggregate function, or window function can be used. The function called by the generation expression can only be an immutable function.
- Default values cannot be specified for generated columns.
- The generated column cannot be used as a part of the partition key.
- Do not specify the generated column and the CASCADE, SET NULL, and SET DEFAULT actions of the ON UPDATE constraint clause together. Do not specify the generated column and the SET NULL, and SET DEFAULT actions of the ON DELETE constraint clause together.
- The method of modifying and deleting generated columns is the same as that of common columns. Delete the common column that the generated column depends on. The generated column is automatically deleted. The type of the column on which the generated column depends cannot be changed.
- The generated column cannot be directly written. In the INSERT or UPDATE statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified.
- The permission control for generated columns is the same as that for common columns.
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]
Creates a column as an IDENTITY column. An implicit sequence is automatically created based on identity_options and attached to a specified column. When data is inserted, the value obtained from the sequence is automatically assigned to the column.
- GENERATED [ ALWAYS ] AS IDENTITY: Only identity values provided by the sequence generator can be inserted into this column. User-specified values cannot be inserted into this column.
- GENERATED BY DEFAULT AS IDENTITY: User-specified values are preferentially inserted into this column. If no value is specified, the identity values provided by the sequence generator are inserted.
- GENERATED BY DEFAULT ON NULL AS IDENTITY: User-specified values are preferentially inserted into this column. If a null value is specified or no value is specified, the identity values provided by the sequence generator are inserted.
The optional identity_options clause can be used to override sequence options.
- increment: specifies the implicit sequence step. If the value is a positive number, an ascending sequence is generated. If the value is a negative number, a descending sequence is generated. The default value is 1.
- MINVALUE minvalue | NO MINVALUE | NOMINVALUE: specifies the minimum value of an execution sequence. If minvalue is not specified or NO MINVALUE is specified, the default value of an ascending sequence is 1, and the default value of a descending sequence is –10²⁷ + 1. NOMINVALUE is equivalent to NO MINVALUE.
- MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE: specifies the maximum value of an execution sequence. If maxvalue is not specified or NO MAXVALUE is specified, the default value of an ascending sequence is 10²⁸ – 1, and the default value of a descending sequence is –1. NOMAXVALUE is equivalent to NO MAXVALUE.
- start specifies the start value of an implicit sequence. The default value for an ascending sequence is minvalue and that for a descending sequence is maxvalue.
- cache: specifies the number of sequences stored in the memory for quick access purposes. The default value is 1, indicating that only one value can be generated at a time, that is, no cache is generated.
- NOCACHE: No value of the sequence is stored in advance.
- CYCLE: recycles sequences after the number of sequences reaches maxvalue or minvalue. If NO CYCLE is declared, any call to nextval returns an error after the sequence reaches its maximum or minimum value. NOCYCLE is equivalent to NO CYCLE. The default value is NO CYCLE.
- SCALE: enables sequence scalability. If specified, a numeric offset is appended to the beginning of the sequence to prevent duplicate items in the generated value. If NOSCALE is declared, sequence scalability is disabled. The default value is NOSCALE.
- EXTEND: extends the numeric offset length (default value: 6), aligns the sequence generation value to x (default value: 6) + y (maximum number of digits). SCALE must be specified when EXTEND is specified. If NOEXTEND is declared, the numeric offset length is not extended. The default value is NOEXTEND.
- The IDENTITY column can only be of the smallint, integer, bigint, decimal, numeric, float, double precision, or real type.
- In A-compatible mode, when an IDENTITY column of the integer type is created, the IDENTITY column is of the numeric type by default.
- The method of changing an IDENTITY column is the same as that of changing a common column, but the type can be changed only to smallint, integer, bigint, decimal, numeric, float, double precision, or real.
- By default, the IDENTITY column has the NOT NULL constraint.
- A table can contain only one IDENTITY column.
- The method of deleting an IDENTITY column is the same as that of deleting a common column. When a column is deleted, the implicit sequence of the IDENTITY column is automatically deleted.
- The IDENTITY column cannot be specified together with the SET DEFAULT action.
- The type of the implicit sequence that is automatically created is LARGE SEQUENCE.
- You cannot use DROP LARGE SEQUENCE or ALTER LARGE SEQUENCE to modify the implicit sequence of an identity.
- After permission is granted to the table, data can be inserted properly. To modify the IDENTITY column, delete the IDENTITY attribute, or delete the IDENTITY column, you need to grant permission to the corresponding implicit sequence.
- The [ SCALE [ EXTEND | NOEXTED ] | NOSCALE ] clause is available only when an IDENTITY column is created in a centralized system in A-compatible mode.
- In a fully-encrypted database, the encrypted IDENTITY column cannot be specified during table creation.
AUTO_INCREMENT
Specifies an auto-increment column.

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 this column is inserted or updated to a value greater than the current auto-increment counter, 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 auto-increment column can be specified only when sql_compatibility is set to 'B'.
- 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.
- It is recommended that the auto-increment column be the first column of an index. Otherwise, an alarm is generated when a table is created, and errors may occur when some operations are performed on 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.
- 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 or updated to a table, the auto-increment counter is updated. If an error is reported after auto-increment and data is not inserted 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 in the auto-increment column may be discontinuous. For details, see the description of the auto_increment_cache parameter.
[DEFAULT] CHARACTER 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.

This syntax is supported only when sql_compatibility is set to 'B'.
[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.

This syntax is supported only when sql_compatibility is set to 'B'. For details about the collation, see Table 1.

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. If the default character set or collation of a schema does not exist, the character set and collation of the table inherit the character set and collation of the current database when b_format_behavior_compat_options contains 'default_collation'.
UNIQUE [KEY] index_parameters
Specifies that a group of one or more columns of a table can contain only unique values.

UNIQUE KEY can be used only when sql_compatibility is set to 'B', which has the same semantics as UNIQUE.
UNIQUE [ 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.
- The index_name is supported only in B-compatible databases (that is, sql_compatibility set to 'B').
- For a unique key constraint, if both constraint_name and index_name are specified, index_name is used as the index name.
PRIMARY KEY index_parameters
PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters

Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.

Only one primary key can be specified for a table.
REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

The foreign key constraint requires 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.
- MATCH PARTIAL: This option is not supported currently.
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. ON DELETE: specifies the operations to be executed after a referenced row in the referenced table is deleted. ON UPDATE: 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. If the constraint is deferrable and there are still any referenced rows, this error will occur when the constraint is checked.
- RESTRICT: When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. It is the same as NO ACTION except that the constraint is not deferrable.
- 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.
DEFERRABLE | NOT DEFERRABLE
Determines whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction using the SET CONSTRAINTS command. NOT DEFERRABLE is the default value. Currently, only UNIQUE constraints, primary key constraints, and foreign key constraints accept this clause. All the other constraints are not deferrable.
INITIALLY IMMEDIATE | INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default time to check the constraint.
- If the constraint is INITIALLY IMMEDIATE (default value), it is checked after each statement.
- If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.
The constraint check time can be altered using the SET CONSTRAINTS statement.
USING INDEX TABLESPACE tablespace_name
Allows selection of the tablespace in which the 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.
ENCRYPTION_TYPE = encryption_type_value
For the encryption type in the ENCRYPTED WITH constraint, the value of encryption_type_value is DETERMINISTIC or RANDOMIZED.

Examples

Temporary table

-- Create a temporary table.
gaussdb=# CREATE GLOBAL TEMP TABLE test_t1(
    id       CHAR(7),
    name     VARCHAR(20),
    province VARCHAR(60),                       -- Province
    country   VARCHAR(30) DEFAULT 'China'        -- Country
);

-- Insert data into the current session.
gaussdb=# INSERT INTO test_t1 VALUES ('0000009','Jack','Guangzhou','China');

-- The data in the temporary table is visible only in the current session. Therefore, the table does not contain data in another session.
gaussdb=# SELECT * FROM test_t1;
 id | name | province | country 
----+------+----------+---------
(0 rows)

-- Create a temporary table and specify that this table is deleted when the transaction is committed.
gaussdb=# 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;

-- Delete the table.
gaussdb=# DROP TABLE test_t1;
gaussdb=# DROP TABLE test_t2;

Specifying the character set and collation during table creation

-- Create a frontend database.
gaussdb=# CREATE DATABASE testdb1 DBCOMPATIBILITY = 'B' ENCODING = 'UTF8';
gaussdb=# \c testdb1


-- Create table t1. Set the default character set of t1 to utf8mb4 and the default collation to utf8mb4_bin. Set the character set and collation of the c1 column to the default values of the table. Set the character set of the c2 column to utf8mb4, and its collation to utf8mb4_unicode_ci.
testdb1=# CREATE TABLE t1(c1 text, c2 text charset utf8mb4 collate utf8mb4_unicode_ci) charset utf8mb4 collate utf8mb4_bin;

-- Delete.
testdb1=# DROP TABLE t1;
testdb1=# \c postgres
gaussdb=# DROP DATABASE testdb1;

IF NOT EXISTS 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.

gaussdb=# CREATE TABLE test_t3(id INT);

-- Create a table named test_t3.
gaussdb=# 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.
gaussdb=# CREATE TABLE IF NOT EXISTS test_t3(id INT);
NOTICE:  relation "test_t3" already exists, skipping
CREATE TABLE

-- Delete the table.
gaussdb=# DROP TABLE test_t3;

Specifying a tablespace during table creation

-- Create a tablespace.
gaussdb=# CREATE TABLESPACE ds_tbs1 RELATIVE LOCATION 'tablespace/tablespace_1';

-- Specify a tablespace when creating a table.
gaussdb=# CREATE TABLE test(id CHAR(7), name VARCHAR(20)) TABLESPACE ds_tbs1;

-- Delete the table and tablespace.
gaussdb=# DROP TABLE test;
gaussdb=# DROP TABLESPACE ds_tbs1;

Specifying the AUTO_INCREMENT column during table creation

-- Create a table and specify an auto-increment column. The column starts from 10.
gaussdb=# CREATE DATABASE test DBCOMPATIBILITY = 'B';
gaussdb=# \c test
test=# CREATE TABLE test_autoinc(col int primary key AUTO_INCREMENT, col1 int) AUTO_INCREMENT = 10;

-- You are advised to use the auto-increment column as the first column of the index to create an index.
test=# CREATE INDEX test_autoinc_ai ON test_autoinc(col);

-- Enter NULL to trigger auto-increment. The auto-increment value is 10.
test=# INSERT INTO test_autoinc(col, col1) VALUES(NULL,1);

-- Enter 100. The auto-increment is not triggered. After the insertion is successful, the auto-increment count is updated to 100.
test=# INSERT INTO test_autoinc(col, col1) VALUES(100,2);

-- Enter 0 to trigger auto-increment. The auto-increment value is 101.
test=# INSERT INTO test_autoinc(col, col1) VALUES(0,3);

test=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1;
 col | col1
-----+------
  10 |    1
 100 |    2
 101 |    3
(3 rows)

-- Delete.
test=# DROP TABLE test_autoinc;
-- Switch to the default database. Change the database name based on actual situation.
test=# \c postgres
gaussdb=# DROP DATABASE test;

Creating a table using CREATE TABLE ... LIKE

-- Create the source table t1.
gaussdb=# CREATE TABLE t1(col INT);
CREATE TABLE

gaussdb=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 col    | integer |

-- Create the target table t2.
gaussdb=# CREATE TABLE t2(LIKE t1);
CREATE TABLE

gaussdb=# \d t2
      Table "public.t2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 col    | integer | 

-- Delete.
gaussdb=# DROP TABLE t1,t2;

Examples of Creating a Table and Adding Constraints to the Table

Non-null constraints

If no value is specified for a column with a non-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.
gaussdb=# 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.
gaussdb=# 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) 

-- Delete the table.
gaussdb=# 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 to add unique constraints.
gaussdb=# CREATE TABLE test_t5(
    id       CHAR(7) UNIQUE USING INDEX TABLESPACE pg_default, -- You can specify a tablespace or use the default tablespace.
    name     VARCHAR(20),
    province VARCHAR(60),                       -- Province
    country   VARCHAR(30) DEFAULT 'China'        -- Country
);

-- You can also use the following method to manually name a unique constraint and add constraints for multiple columns:
gaussdb=# 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.
gaussdb=# INSERT INTO test_t5(id) VALUES('0000010');
INSERT 0 1
gaussdb=# 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.
gaussdb=# INSERT INTO test_t5(id) VALUES (NULL);
INSERT 0 1
gaussdb=# INSERT INTO test_t5(id) VALUES (NULL);
INSERT 0 1

gaussdb=# SELECT * FROM test_t5;
   id    | name | province | country 
---------+------+----------+--------
 0000010 |      |          | China
         |      |          | China
         |      |          | China

-- Delete the table.
gaussdb=# DROP TABLE test_t5;
gaussdb=# DROP TABLE test_t6;

Primary key constraints

The keyword PRIMARY KEY is used to add a unique constraint to a column. The column must be unique and cannot be empty. 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 to the table.
gaussdb=# CREATE TABLE test_t6(
    id       CHAR(7) PRIMARY KEY,
    name     VARCHAR(20),
    province VARCHAR(60),                       -- Province
    country   VARCHAR(30) DEFAULT 'China'        -- Country
);
gaussdb=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','july','Beijing');

-- You can also use the following method to manually name a unique constraint and add constraints for multiple columns:
gaussdb=# 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.
gaussdb=# 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.
gaussdb=# 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.

-- Delete the table.
gaussdb=# DROP TABLE test_t6;
gaussdb=# 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 check constraints.
gaussdb=# CREATE TABLE test_t8 (
    id       CHAR(7),
    name     VARCHAR(20),
    age      INT CHECK(age > 0 AND age < 150)
);

-- Or run the following SQL statements to manually name check constraints and add check constraints for one or more columns:
gaussdb=# 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.
gaussdb=# 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

-- Delete the table.
gaussdb=# DROP TABLE test_t8;
gaussdb=# DROP TABLE test_t9;

Foreign key constraints

When two tables contain one or more public columns, you can use foreign key constraints to enforce the relationship between 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 of the referenced columns in other tables or NULL.
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 KEY 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.
gaussdb=# CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    loc VARCHAR(200)
);

-- Create an employee table and add foreign key constraints.
gaussdb=# 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.
gaussdb=# INSERT INTO dept VALUES (10,'Beijing');
gaussdb=# INSERT INTO dept VALUES (20,'Beijing');
gaussdb=# INSERT INTO dept VALUES (30,'Shanghai');

-- Insert the deptno data that can be found in the department table into the employee table.
gaussdb=# INSERT INTO emp VALUES (1,'Bob',10);

-- Insert data whose deptno is NULL into the employee table.
gaussdb=# INSERT INTO emp VALUES (2,'Scott',NULL);

-- Insert the deptno data that cannot be found in the department table into the employee table.
gaussdb=# 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.
gaussdb=# SELECT * FROM emp;
 empno | name  | deptno 
-------+-------+--------
     1 | Bob   |     10
     2 | Scott |       
(2 rows)

-- Delete the table.
gaussdb=# DROP TABLE emp;
gaussdb=# DROP TABLE dept;

Helpful Links

ALTER TABLE, DROP TABLE, and CREATE TABLESPACE

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 | TEMP
- A temporary table is automatically dropped at the end of a 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.
LIKE INCLUDING DEFAULTS
- The default expressions are copied from the original table to the new table only if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having default values NULL.
LIKE INCLUDING CONSTRAINTS
- The CHECK constraints are copied from the original table to the new table only when INCLUDING CONSTRAINTS is specified. Other types of constraints are never copied to the new table. NOT NULL constraints are always copied to the new table. These rules also apply to column constraints and table constraints.
LIKE INCLUDING INDEXES
- Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified.
LIKE INCLUDING STORAGE
- STORAGE settings for the copied column definitions are copied only if INCLUDING STORAGE is specified. The default behavior is to exclude STORAGE settings.
LIKE INCLUDING COMMENTS
- If INCLUDING COMMENTS is specified, comments for the copied columns, constraints, and indexes are copied. The default behavior is to exclude comments.
LIKE INCLUDING PARTITION
- If INCLUDING PARTITION is specified, the partition definitions of the source table are copied to the new table, and the new table no longer uses the PARTITION BY clause. By default, the partition definition of the source table is not copied.
List and hash partitioned tables do not support LIKE INCLUDING PARTITION.
LIKE INCLUDING RELOPTIONS
- If INCLUDING RELOPTIONS is specified, the new table will copy the storage parameter (that is, WITH clause) of the source table. The default behavior is to exclude partition definition of the storage parameter of the original table.
LIKE INCLUDING IDENTITY
- If INCLUDING IDENTITY is specified, a SEQUENCE with the same SEQUENCE parameter as the source table is created to implement the identity, and the IDENTITY type is the same as that of the source table. By default, the identity of the source table is not copied.
LIKE INCLUDING ALL
- INCLUDING ALL contains the contents of INCLUDING DEFAULTS, INCLUDING CONSTRAINTS, INCLUDING INDEXES, INCLUDING STORAGE, INCLUDING COMMENTS, INCLUDING PARTITION, INCLUDING RELOPTIONS, and INCLUDING IDENTITY.
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.

Parent topic: C

Previous topic: CREATE SYNONYM

Next topic: CREATE TABLE AS

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot