CREATE TABLE

CREATE TABLE — define a new table.

Syntax

Create a table:

CREATE TABLE [ IF NOT EXISTS ] [schema_name.]table_name
 ( column_name { data_type | domain_name } [ column_constraints ] [ , ... ]
    [, table_constraints ]
 )

Create a temporary tableA database object that resides in memory for only the current connection. You can define a temporary table with a CREATE TABLE statement and you can operate on it with DML. A temporary table can be dropped during a session. Not to be confused with pseudo system table.:

CREATE [ LOCAL ] { TEMPORARY | TEMP } TABLE [ IF NOT EXISTS ] table_name
 ( column_name { data_type | domain_name } [ column_constraints ] [, ... ]
    [, table_constraints ]
 ) [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

Create a table with partitioning:

CREATE TABLE [ IF NOT EXISTS ] [schema_name.]table_name
 ( column_name { data_type | domain_name } [ column_constraints ] [ , ... ]
    [, table_constraints ]
 )
 { 
  STORE IN storage_group |
  ( { PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
      [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE) 
      [ STORE IN storage_group ] ]... } |
    { PARTITION BY LIST (column_name) [STORE IN storage_group ]
      [ PARTITION partition_name VALUES IN ( { literal [,literal]...} | DEFAULT ) 
      [ STORE IN storage_group ] ]... }
  )
 }

Where column_constraints can be one or more of the following:

GENERATED ALWAYS AS IDENTITY  | GENERATED BY DEFAULT AS IDENTITY
  NOT NULL | NULL
  CHECK ( expression )
  DEFAULT default_expr 
{ COLLATE | COLLATION } collation_name
  PRIMARY KEY
  REFERENCES reference_table [ ( reference_column [ ,...] ) ]
  UNIQUE

Where table_constraints is:

[ CONSTRAINT [ constraint_name ] ] table_constraint [ , table_constraint ]...

and table_constraint is:

CHECK ( expression )
PRIMARY KEY ( column_name [, ...] ) [ index_qualifiers ]
FOREIGN KEY ( column_name [, ...] ) REFERENCES reference_table [ ( reference_column [, ... ] ) ]
UNIQUE ( column_name [, ...] )
KEY indexName ( column_name[, ...]) [ index_qualifiers ]
UNIQUE KEY indexName ( column_name[, ...]) [ index_qualifiers ]

Description

CREATE TABLE will create a new, initially empty table in the current database. The table will be owned by the user issuing the command.

If a schema_name is specified (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise, the table is created in the current schema, assigned by the USE SCHEMA command. Tables cannot be created in the SYSTEM schema. This schema is reserved for database metadata. Temporary tables exist in a special schema, so a schema_name cannot be specified when creating a temporary table.

The name of a persistent table must be distinct from the name of any other table, sequence, index or view in the current or specified schema. Temporary tables can be created using the same name as an existing persistent table. However, the persistent table is not visible to the current session while the temporary table exists, unless they are referenced with their schema-qualified names. This is due to the fact that any reference to that table by name, without schema name, references the temporary table.

The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table in various ways.

There are two ways to define constraints: column constraints and table constraints. A column constraint is defined as part of a column definition and only affects that one column. A table constraint definition is not tied to a particular column, and it can encompass more than one column.

A table cannot have more than 32000 columns. (In practice, the effective limit is lower because of record-length constraints).

Parameters

Table Partitioning

CREATE TABLE supports an optional PARTITION BY clause which allows control over where data in the table is stored. Without table partitioning, a row in a table is stored on every storage manager (SM). This is a very powerful tool for increasing performance by reducing disk and network traffic, especially in a Region distributed application. The notion is to keep data local to the client accessing it. CREATE TABLE supports two formats of PARTITION BY: BY RANGE and BY LIST.

In addition to associating a table with multiple storage groups, a table can be created and assigned to just one storage group by specifying the syntax STORE IN storage_group. In this case, we establish a partition for the table and all the data in the table is put into that partition regardless of any other associations established for that table (for example via the MAP PARTITION command or a subsequent PARTITION BY clause).

Partition by Range

PARTITION BY RANGE specifies that each row is mapped to a partition based on the first VALUES LESS THAN subclause that evaluates to true, where VALUES LESS THAN clauses are sorted from lowest to highest.

PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
( [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE) [ STORE IN storage_group ]... )

The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES LESS THAN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES LESS THAN clause, may specify a storage group to use for the given partition_name, if the MAP PARTITION command was not already used to associate the specific partition name with a storage group. The MAXVALUE keyword specifies a partition for values not evaluating to true for any of the VALUES LESS THAN clauses. If MAXVALUE is not specified, then values not evaluating to true for any of the VALUES LESS THAN clauses would cause an error. A partition_name may not be used in more than one VALUES LESS THAN clause. A literal may not be used in more than one VALUES LESS THAN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY RANGE table is done in the following order:

  1. If an optional storage group is specified after the VALUES LESS THAN clause, then that storage group is used.
  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY RANGE clause, use that storage group.
  3. If the partition name specified by PARTITION partition_name was previously associated with a storage group using MAP PARTITION, then use that storage group.
  4. No storage group is found and an exception is thrown.

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB. If the table has a PRIMARY KEY and/or UNIQUE index, the partitioning column must be added as a column for the index to ensure uniqueness across multiple partitions. The exception to this is a partitioning column that is defined as GENERATED ALWAYS AS IDENTITY. This column would not be required to be added to the PRIMARY KEY and/or UNIQUE index.

For more information, see Using Table Partitions and Storage Groups.

Partition by List

PARTITION BY LIST specifies that each row is mapped to a partition based on the first VALUES IN clause that contains the value stored in the partitioning column specified by column_name.

PARTITION BY LIST (column_name) [STORE IN storage_group ]
( [ PARTITION partition_name VALUES IN ( { literal [,literal]... } | DEFAULT ) [ STORE IN storage_group ] ]... )

Like PARTITION BY RANGE, The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES IN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES IN clause, may specify a storage group to use for the given partition_name, if the MAP PARTITION command was not already used to associate the specific partition name with a storage group name. If a value in the partitioning column specified by column_name does not match any of the VALUES IN lists, then an exception is thrown. The DEFAULT keyword specifies a partition for values not evaluating to true for any of the previous VALUES IN clauses. If DEFAULT is not specified, then values not evaluating to true for any of the VALUES IN clauses would cause an error. A partition_name may not be used in more than one VALUES IN clause. A literal may not be used in more than one VALUES IN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY LIST table is done in the following order:

  1. If an optional storage group is specified after the VALUES IN clause, then that storage group is used.
  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY LIST clause, use that storage group.
  3. If the partition specified by PARTITION partition_name was previously mapped to a storage group using MAP PARTITION, then use that storage group.
  4. No storage group is found and an exception is thrown.

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB.

For more information, see Using Table Partitions and Storage Groups.

Examples