CREATE TABLE

Number of column-store and HDFS partitioned tables that can be created

For better performance, keep this value at or below 1,000.

Data types supported by column-store tables

For details, see Data Types Supported by Column-Store Tables.

Primary key and unique constraint requirements

The primary key and unique constraints in a table must contain distribution columns.

Modification of distribution columns

The data type of the distribution column in an existing table cannot be modified.

Row-store REPLICATION tables

A system column cannot be designated as a primary key in a row-store REPLICATION distributed table.

Supported range of column-store tables

Column-store tables support the PARTIAL CLUSTER KEY and table-level primary key and unique constraints, but do not support table-level foreign key constraints.

Columns of column-store tables

Only the NULL, NOT NULL, and DEFAULT constant values are supported.

Hot and cold tables

• Hot and cold tables support only column-store partitioned tables and depend on the available OBS.
• Hot and cold tables support only the default tablespace default_obs_tbs. If you need to add an OBS tablespace, contact technical support.

Delta tables supported by column-store tables

Whether column-store tables support a delta table is specified by the enable_delta parameter. The threshold for storing data into a delta table is specified by the deltarow_threshold parameter. Column-store tables with delta tables are not recommended. If delta tables are used, disk bloat and performance deterioration may occur due to insufficient merge time. Delta tables in column-store tables are no longer supported in versions later than 9.1.0.200 due to the preceding problems. HStore tables are used instead. For details about HStore tables, see "Hybrid Data Warehouse" in Data Warehouse Service (DWS) Developer Guide.

HStore tables

• When creating an HStore table, ensure that the database GUC parameter settings meet the following requirements:
  • autovacuum is set to on.
  • The value of autovacuum_max_workers_hstore is greater than 0.
  • The value of autovacuum_max_workers is greater than that of autovacuum_max_workers_hstore.

V3 tables

• Once a table is created, it cannot be switched from a non-V3 table to a V3 table using the ALTER TABLE syntax. In other words, a non-V3 table (colversion is 2.0) cannot be converted to a V3 table (colversion is 3.0)
• Delta tables and column-store level-2 partitions cannot be set for V3 tables (colversion is 3.0, storage-compute decoupling).
• V3 tables cannot be configured as HStore tables, cold and hot tables, or time sequence tables.
• Global temporary tables and temporary tables cannot be created for V3 tables. Any created temporary tables are automatically converted to temporary tables with colversion 2.0.

Other

If an error occurs during table creation, the system may not be able to clear the created, empty disk file automatically after the system is recovered. This problem rarely occurs and does not affect the normal running of the database system.

UNLOGGED

If UNLOGGED is specified, the created table is an unlogged table. Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables. However, an unlogged table is automatically truncated after a crash or unclean shutdown, incurring data loss risks. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are not automatically logged as well.

Usage scenario: Unlogged tables do not ensure safe data. You can back up data before using unlogged tables; for example, you 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, you should re-create the indexes with errors.

Example:

CREATE UNLOGGED TABLE table_unlog (
    col1 VARCHAR(50) ,
    col2 TEXT
);

GLOBAL | LOCAL | VOLATILE

Specify the keywords GLOBAL, LOCAL, and VOLATILE before TEMP or TEMPORARY to create temporary tables with different attributes. Global temporary tables are supported only by 8.2.1.220 and later cluster versions.

• If LOCAL is specified, a local temporary table is created.
• If keyword GLOBAL is specified, the attributes of the temporary table depend on the GUC parameter enable_global_temp_table. The default value of this parameter is ON.

  If enable_global_temp_table is set to on, a global temporary table GLOBAL is created.

  If enable_global_temp_table is set to off, a local temporary table LOCAL is created. You can also specify keyword LOCAL to reach the same effect.

• If VOLATILE is specified, a temporary volatile table is created.

  If default_temptable_type is set to local, temporary tables created without keywords are local temporary tables. If default_temptable_type is set to volatile, temporary tables created without keywords are volatile temporary tables.

Example:

CREATE LOCAL TEMP TABLE temp_test (
    col1 INT,
    col2 DECIMAL(10,2)
);

TEMPORARY | TEMP

If TEMP or TEMPORARY is specified, the created table is a temporary table.

Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction. Therefore, apart from CN and other CN errors connected by the current session, you can still create and use temporary table in the current session.

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 not advised to perform operations on temporary tables in DDL statements. TEMP is equivalent to TEMPORARY.

For other restrictions of this parameter, see TEMPORARY | TEMP Parameter Description.

IF NOT EXISTS

If IF NOT EXISTS is specified, a table will be created if there is no table using the specified name. If there is already a table using the specified name, no error will be reported. A message will be displayed indicating that the table already exists, and the database will skip table creation.

Example:

Use IF NOT EXISTS to prevent duplicate table creation.

CREATE TABLE IF NOT EXISTS test_t1(col1 INT,col2 VARCHAR(30));

table_name

Specifies the name of the table to be created.

• The table name must start with a letter or underscore (_) and can contain up to 63 characters. Only letters, digits, underscores (_), dollar signs ($), and number signs (#) are allowed.
• Chinese table names are supported. The number of supported characters depends on the database's character set encoding. For details, see DWS Database Object Naming Rules.
• You are not advised to use a keyword as a table name. For details, see Keyword.
• A table name enclosed in double quotation marks can contain spaces and special characters. However, you are not advised to use these characters in a table name because they may make it difficult to reference and use. In addition, they may be processed differently under different database compatibility modes.

column_name

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

The column name can contain a maximum of 63 characters, including letters, digits, underscores (_), dollar signs ($), and number signs (#). It must start with a letter or underscore (_).

data_type

Specifies the data type of the column.

In a database compatible with Teradata or MySQL syntax, if the data type of a column is set to DATE, the DATE type is returned. Otherwise, the TIMESTAMP type is returned.

compress_mode

Specifies the compress option of the table, only available for row-store table. The option specifies the algorithm preferentially used by table columns.

DELTA, PREFIX, DICTIONARY, NUMSTR, or NOCOMPRESS

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.

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.

For details about LIKE constraints, see LIKE Parameter Description.

WITH

Specifies an optional storage parameter for a table or an index.

WITH contains many sub-parameters, which are classified as follows by scenario. You only need to pay attention to the parameters in specific scenarios.

• For details about storage class parameters, see Table 4.
• For details about parameters related to the partitioned table, see Table 5.
• For details about the parameters related to the Delta table, see Table 6.
• Parameters related to the Hstore table. For details, see Table 7.
• For details about performance-related parameters, see Table 8.

ON COMMIT { PRESERVE ROWS | DELETE ROWS }

ON COMMIT determines what to do when you commit a temporary table creation operation. Global temporary tables support only the PRESERVE ROWS option.

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

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

DISTRIBUTE BY

Specifies how the table is distributed or replicated between DNs.

• REPLICATION: Each row in the table exists on all DNs, that is, each DN has complete table data.
• ROUNDROBIN: Each row in the table is sent to each DN in turn. Therefore, data is evenly distributed on each DN. This value is supported only in 8.1.2 or later.
• HASH (column_name): Each row of the table will be placed into all the DNs based on the hash value of the specified column.

For details about other restrictions, see DISTRIBUTE BY Parameter Description.

Example:

Create a hash-distributed table.

CREATE TABLE test_t2(col1 INT,col2 VARCHAR(30))DISTRIBUTE BY HASH(col1);

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

TO GROUP specifies the Node Group in which the table is created. Currently, it cannot be used for HDFS tables. TO NODE is used for internal scale-out tools.

In logical cluster mode, if TO GROUP is not specified, the table is created in the node group associated with the logical cluster user by default. If the user, such as the administrator or a common user, does not manage the logical cluster, by default the table is created in the first logical cluster, which is the logical cluster with the smallest OID in pgxc_group.

If the node group specified by TO GROUP is a replication table node group, the table is created on all CNs and DNs, but the replication table data is distributed only on the DNs in the replication table node group.

The Storage-compute decoupling 3.0 supports read-only logical clusters. If a user is not bound to any read-only logical clusters but sets TO GROUP to a logical cluster in a table creation statement, an error will be reported during table creation. If a user bound to a read-only logical cluster creates a table, the table will be created in the logical cluster specified by the GUC parameter default_storage_nodegroup. If default_storage_nodegroup is set to installation, tables will be created in the first logical cluster.

COMMENT [=] 'text'

The COMMENT clause can specify table comments during table creation.

Example:

Add a comment for a table.

CREATE TABLE table_user(user_id INT,username VARCHAR(50)) COMMENT 'user Information';

COMMENT 'text'

The COMMENT clause can specify a comment for a column.

Example:

Add a comment for a specified column.

CREATE TABLE table_users(user_id INT COMMENT 'employee id',username VARCHAR(50));

CONSTRAINT constraint_name

Specifies a name for 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 any particular columns but can apply to more than one column.

Example:

Create the table_c_products table and define the primary key constraint. The primary key of the table consists of C_CUSTKEY and C_NAME.

CREATE TABLE table_c_products
(    
    C_CUSTKEY BIGINT,
    C_NAME VARCHAR(25) ,
    C_ADDRESS VARCHAR(40),
    C_NATIONKEY INT,
    C_PHONE CHAR(15) ,
    C_ACCTBAL DECIMAL(15,2), 
    CONSTRAINT C_CUSTKEY_KEY PRIMARY KEY(C_CUSTKEY,C_NAME)
)
DISTRIBUTE BY HASH(C_CUSTKEY,C_NAME);

NOT NULL

Indicates that the column is not allowed to contain NULL values.

-

NULL

The column is allowed to contain NULL values. This is the default setting.

This clause is only provided for compatibility with non-standard SQL databases. You are advised not to use this clause.

-

CHECK ( expression )

Specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. If any row of an insert or update operation produces a FALSE result, an error exception is raised and the insert or update does not alter the database.

A check constraint specified as a column constraint should reference only the column's values, while an expression appearing 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 for 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 on_update_expr

The ON UPDATE clause specifies a timestamp function for a column. Ensure that the data type of the column for which the ON UPDATE clause specifies a timestamp function is timestamp or timestamptz.

When an SQL statement containing the UPDATE operation is executed, this column is automatically updated to the time specified by the timestamp function.

on_update_expr supports only CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, LOCALTIME, and LOCALTIMESTAMP.

UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] index_parameters

UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] ( column_name [, ... ] ) index_parameters

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

The [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] field is used to specify how to process null values in the index column of the Unique index.

Default value: This parameter is left empty by default. NULL values can be inserted repeatedly.

When the inserted data is compared with the original data in the table, the NULL value can be processed in any of the following ways:

• NULLS DISTINCT: NULL values are unequal and can be inserted repeatedly.
• NULLS NOT DISTINCT: NULL values are equal. If all index columns are NULL, NULL values cannot be inserted repeatedly. If some index columns are NULL, data can be inserted only when non-null values are different.
• NULLS IGNORE: NULL values are skipped during the equivalent comparison. If all index columns are NULL, NULL values can be inserted repeatedly. If some index columns are NULL, data can be inserted only when non-null values are different.

The following table Table 10 lists the behaviors of the three processing modes.

PRIMARY KEY index_parameters

PRIMARY KEY ( column_name [, ... ] ) index_parameters

Specifies the primary key constraint 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.

Example:

Create the products table, set the data type of product_no to integer and set it as the primary key.

CREATE TABLE products 
(product_no integer PRIMARY KEY, 
name text, 
price numeric
)
DISTRIBUTE BY HASH(product_no);

REFERENCES reftable [ ( refcolumn ) ]

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. The referenced column should be the only column or primary key in the referenced table.

DWS does not check foreign key constraints. When using foreign key constraints, you need to use the check_foreign_key_constraint function to check whether the data in the foreign key table meets the foreign key constraints.

-

DEFERRABLE | NOT DEFERRABLE

Controls 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 and PRIMARY KEY constraints of row-store tables accept this clause. All the other constraints are not deferrable.

PARTIAL CLUSTER KEY

Specifies a partial cluster key for storage. When importing data to a column-store table, you can perform local data sorting by specified columns (single or multiple).

Example:

Create the products_new table and define the partial cluster key on the product_no column.

CREATE TABLE products_new 
(
    product_no integer, 
    name text, 
    price numeric,
    PARTIAL CLUSTER KEY(product_no)
) WITH (ORIENTATION = COLUMN);

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

ORIENTATION

Specifies the storage mode (row-store, column-store) for table data. This parameter cannot be modified once it is set.

• Row-store tables are suitable for OLTP workloads. There are many interactive transactions in such tables. An interaction involves multiple columns in a table.
• Column-store tables are suitable for data warehouse workloads. There are a large amount of aggregation calculation and few column operations on these tables.

• ROW indicates that table data is stored in rows.
• COLUMN indicates that the data is stored in columns.

Default value: ROW (row-store)

COMPRESSION

Specifies the compression level of the 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 characteristics of loading table data.

This parameter is available only for column-store tables. Value: LOW (default value), MIDDLE, or HIGH.

For details about the compression algorithms provided by DWS, see Table 9.

COMPRESSLEVEL

Specifies the compression level of the table data. It determines the compression ratio and time.

This divides a compression level into sublevels, providing you with more choices for compression rate and duration. As the value becomes greater, the compression rate becomes higher and duration longer at the same compression level. The parameter is only valid for column-store tables.

0~3

Default value: 0

MAX_BATCHROW

Specifies the maximum of a storage unit during data loading process. The parameter is only valid for column-store tables.

10000 to 60000

Default value: 60,000

PARTIAL_CLUSTER_ROWS

Specifies the number of records to be partial cluster stored during data loading process. The parameter is only valid for column-store tables.

600000 to 2147483647

Default value: 4,200,000

COLVERSION

Specifies the version of the column-store format. You can switch between different storage formats.

• 1.0: Each column in a column-store table is stored in a separate file. The file name is relfilenode.C1.0, relfilenode.C2.0, relfilenode.C3.0, or similar.
• 2.0: All columns of a column-store table are combined and stored in a file. The file is named relfilenode.C1.0.
• 3.0: Each column of a column-store table is stored in a file. The file is stored in the OBS file system and named C1_fileid.0.

Default value: The default value for the storage-compute coupled version is 2.0, while for the storage-compute decoupling version, it is 3.0.

The value of COLVERSION can only be set to 2.0 for OBS multi-temperature tables.

cache_policy (This parameter is supported only in 9.0.2 and later versions where the storage and compute nodes are decoupled.)

Specifies the cache mode of tables or partitioned tables (disks). If one of the following values is specified in the cache policy, hot cache is used. Otherwise, cold cache is used. Hot cache occupies more space than cold cache and uses more complex replacement policies.

• ALL: Hot cache is used for the entire table.
• NONE: Cold cache is used for the entire table.
• HPN: The first N partitions in a partitioned table use hot cache. The rest of the partitions use cold cache.
• HPL: P1, P2, .... In a partitioned table, the specified partitions use hot cache. The rest of the partitions use cold cache.

The default value is ALL.

enable_disaster_cstore

Specifies whether fine-grained DR will be enabled for column-store tables. This parameter is available only for common column-store tables whose COLVERSION is 2.0. It is unavailable for HStore tables, row-store tables, temporary tables, unlogged tables, foreign tables, time series tables, cold and hot tables, materialized views, and delta tables, and cannot be enabled together with enable_hstore and enable_delta. This parameter is supported by version 8.2.0.100 or later clusters.

-

fine_disaster_table_role

This parameter has been discarded in version 8.2.1 and is reserved for compatibility with earlier versions. This parameter is invalid in the current version.

Specifies whether the fine-grained DR table will be set as a primary or secondary table. This parameter can be true only when the enable_disaster_cstore parameter has been set to true.

• primary: Specifies the primary fine-grained DR table.
• standby: Specifies the standby fine-grained DR table.

on_commit_preserve_rows

It is similar to ON COMMIT { PRESERVE ROWS | DELETE ROWS }. The two parameters cannot be specified at the same time. This parameter is used only for global temporary tables.

The default value is true.

TTL

Schedules the partition deletion tasks in a partitioned table. By default, no partition deletion task is created.

1 hour ~ 100 years

PERIOD

Schedules the partition creation tasks in a partitioned table. If TTL has been configured, PERIOD cannot be greater than TTL.

1 hour ~ 100 years

Default value: 1 day

time_format

You can use auto-increment and decrement partitions for INT4, INT8, VARCHAR, and TEXT columns, which are commonly used for storing time-related data. The time_format option is applicable only when the partition key is INT4, INT8, VARCHAR, or TEXT and a period is specified. This is supported only by clusters of version 9.1.0.200 or later.

1. The value of time_format must comply with the PostgreSQL specifications, for example, yyyymmdd.
2. Restrictions on time_format differ by partition key type.

   VARCHAR/TEXT:

   • The precision can be accurate to seconds.
   • The value cannot contain letters, for example, month, am, and pm.
   • The time must be arranged in descending order, for example, year, month, day, hour, minute, and second.

   INT4/INT8:

   • The precision can be accurate to hours.
   • The value can contain only Y, M, D, and HH24.
   • The time must be arranged in descending order, for example, year, month, day, and hour.
3. ALTER restrictions:
   • The set operation is not supported.
   • When period is reset (indicating that automatic partitioning is disabled and a message is displayed), you can reset this option.

enable_delta

Specifies whether to enable delta tables in column-store tables. The parameter is only valid for column-store tables. If COLVERSION is set to 3.0, enable_delta cannot be turned on because this parameter is not supported by V3 tables.

Using column-store tables with delta tables is not recommended. This may cause disk bloat and performance deterioration due to delayed merge.

Default value: off

DELTAROW_THRESHOLD

Specifies the upper limit of to-be-imported rows for triggering the data import to a delta table when data is to be imported to a column-store table. This parameter takes effect only if the enable_delta table parameter is set to on. The parameter is only valid for column-store tables.

0 to 60000

Default value: 6000

enable_hstore

Specifies whether an HStore table will be created (based on column-store tables). The parameter is only valid for column-store tables. This parameter is supported by version 8.2.0.100 or later clusters. If COLVERSION is set to 3.0, enable_delta cannot be turned on because this parameter is not supported by V3 tables.

Default value: off

enable_hstore_opt

If the enable_hstore_opt table-level parameter is enabled, the enable_hstore table-level parameter is also enabled by default. This parameter is supported only in cluster 8.3.0 and later versions.

Default value: false

secondary_part_column

Specifies the name of a level-2 partition column in a column-store table. Only one column can be specified as the level-2 partition column. This parameter applies only to HStore column-store tables. This parameter is supported only by clusters of version 8.3.0 or later.

• The column specified as a level-2 partition column cannot be deleted or modified.
• The level-2 partition column can be specified only when a table is created. After a table is created, the level-2 partition column cannot be modified.
• You are not advised to specify a distribution column as a level-2 partition column.
• The level-2 partition column determines how the table is logically split into hash partitions on DNs, which enhances the query performance for that column.

secondary_part_num

Specifies the number of level-2 partitions in a column-store table. This parameter applies only to HStore column-store tables. This parameter is supported only by clusters of version 8.3.0 or later.

1~32

Default value: 8

bitmap_columns

The bitmap index is only applicable to the new HStore (hstore_opt) tables. To generate the bitmap index mapping, you need to enable the enable_hstore_opt table-level parameter and set bitmap_columns to Specified Columns. This parameter is supported only by clusters of version 8.3.0 or later.

-

FILLFACTOR

The fillfactor of a table is a percentage between 10 and 100. 100 (complete packing) is the default value. When a smaller fillfactor is specified, INSERT operations pack 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 records are never updated, setting the fillfactor to 100 (complete packing) is the appropriate choice, but in heavily updated tables smaller fillfactors are appropriate. The parameter has no meaning for column–store tables.

10~100

analyze_mode

Specifies the mode of table-level auto-analyze.

• frozen: disables all ANALYZE operations (dynamic sampling can still be triggered when no statistics are collected).
• backend: allows only ANALYZE triggered by AUTOVACUUM polling.
• runtime: allows only runtime ANALYZE triggered by the optimizer.
• all: Both backend and runtime AUTO-ANALYZE can be triggered.

Default value: all

incremental_analyze

Specifies whether to enable the incremental analyze mode for partitioned tables. This parameter is valid only for partitioned tables and cannot be set for replicated tables. This is supported only by clusters of version 9.1.0.100 or later.

Default value: false

enable_turbo_store

Default value: off

SKIP_FPI_HINT

Indicates whether to skip the hint bits operation when the full-page writes (FPW) log needs to be written during sequential scanning.

enable_column_autovacuum_garbage

Determines whether to enable CU rewriting logic for column-store tables using AUTOVACUUM. This parameter is supported only by clusters of version 8.2.1.100 or later.

There is a low probability that an error is reported when lightweight UPDATE and AUTOVACUUM are executed concurrently. You can set the table-level parameter to off to avoid this problem.

Default value: true

LOW

Delta compression + RLE compression

LZ4 compression

Delta compression (RLE is optional.)

MIDDLE

Delta compression + RLE compression + LZ4 compression

dict compression or LZ4 compression

Delta compression or LZ4 compression (RLE is optional)

HIGH

Delta compression + RLE compression + zlib compression

dict compression or zlib compression

Delta compression or zlib compression (RLE is optional)

NULLS DISTINCT

Can be inserted repeatedly.

Can be inserted repeatedly.

NULLS NOT DISTINCT

Cannot be inserted repeatedly.

Cannot be inserted if the non-null values are equal. Can be inserted if the non-null values are not equal.

NULLS IGNORE

Can be inserted repeatedly.

Cannot be inserted if the non-null values are equal. Can be inserted if the non-null values are not equal.