CREATE TABLE

Create a new, empty table in a database.

The table is owned by the user who runs the CREATE TABLE command. If you specify a schema name (schema1.table1), the table is created in that schema; otherwise, it belongs to the current schema. See the parameter descriptions for more details.

The optional CONSTRAINT clauses specify constraints that new or updated rows must satisfy. For example, if a column is declared NOT NULL, an attempt to insert a NULL value into that column will fail. A constraint defines the set of valid values that one or more columns may contain. You can define constraints on tables or columns. A column constraint is part of a single column definition, but a table constraint may apply to more than one column. See the parameter descriptions for more details.

Syntax

CREATE [ { TEMPORARY | TEMP } ] TABLE [ IF NOT EXISTS ] table_name 
( [
  { column_name data_type 
    [ column_constraint [ ... ] ]
    | table_constraint }
  [, ... ]
] )
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ DISTRIBUTE { ON (column) | [ ON ] REPLICATE | [ ON ] RANDOM } ]
{ [ SORT ON (column) ] |
[ CLUSTER ON (column [, ... ] }
[ PARTITION BY ( { range_partition_spec | hash_partition_spec } [ , ... ] ) 

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT expression |
  UNIQUE |
  PRIMARY KEY |
  REFERENCES reftable [ ( refcolumn ) | 
  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=key, ENCRYPTION_TYPE={ DETERMINISTIC | RANDOMIZED }, ALGORITHM='algorithm') }

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
  PRIMARY KEY ( column_name [, ... ] ) }

and range_partition_spec is:

RANGE ( column BETWEEN value AND value EACH value [, OUTSIDE RANGE] [, IS NULL] ) 

and hash_partition_spec is:

HASH ( column WITH number PARTITIONS [, IS NULL] )

Parameters

TEMPORARY | TEMP
Create a "local" temporary table that is automatically dropped at the end of the session. Temporary tables belong to a temporary schema. You cannot qualify a temporary table name with a user-defined schema name.

Temporary tables are visible only to the user who created the table and within the session where the table was created. You cannot create "global" temporary tables that are visible across sessions and users.

Temporary tables that the system creates during queries are limited to a size of 30MB. This limitation does not apply to temporary tables created by users.

IF NOT EXISTS
Create the table if it does not already exist. If it does exist, do not create it and do not return an error.
table_name
Give the table a name that is distinct from the name of any other table, sequence, or view in the same schema. The maximum length of a table name is 128 bytes; longer names are automatically truncated. See SQL Identifiers.

Optionally, qualify the name of a persistent table with the schema name. You can also qualify table names with the name of the current database, but you cannot create a table in a different database; the table must belong to the current database.

You can create temporary and persistent tables with the same name. However, a persistent table with the same name as an existing temporary table is only visible to the current session if you reference it by using its schema-qualified name.

In this example, the books table belongs to the public schema and the current premdb database:
premdb=# create table premdb.public.books(isbn varchar(20));
CREATE TABLE
column_name
Name each column uniquely. See SQL Identifiers.
data_type
Define the data type for each column. See SQL Data Types.
column_constraint
Define constraints for a specific column and optionally provide a constraint name. Column constraints are a convenient way of defining single-column attributes. A PRIMARY KEY constraint implies that the column is both NOT NULL and UNIQUE. If you want to define a PRIMARY KEY or UNIQUE constraint that involves multiple columns, use a table constraint.
Note: PRIMARY KEY and UNIQUE constraints may be declared, but they are not enforced. Explicit creation of FOREIGN KEY constraints is not supported; however, they are created by the system when a PRIMARY KEY is declared with the REFERENCES clause. In this case, you can drop the FOREIGN KEY constraint if necessary. See ALTER TABLE.
  • NOT NULL: do not allow null values in the column.
  • NULL: allow null values in the column (the default behavior).
  • DEFAULT: use an expression to set a default value for the column. Make sure the expression is compatible with the data type of the column.
    Note: If you use ALTER TABLE to add a column with a DEFAULT constraint, the default value is applied only for new rows that are inserted after the column was added. Existing rows in the table will contain a NULL value for that column.
  • UNIQUE: declare that all values in the specified column should be distinct.
  • PRIMARY KEY: declare the column as the primary key of the table.
  • REFERENCES: declare the column as a foreign key by naming the referenced table and column.
ENCRYPTED WITH
Encrypt a VARCHAR column with an encryption key that is created with a CREATE KEY statement. (The key does not have to exist before the CREATE TABLE statement is run; it can be created later.) Data inserted or bulk-loaded into an encrypted column will be protected, using the ENCRYPT_KS function. See also Encrypting Sensitive Data.
You must specify an encryption key, type, and algorithm in the WITH clause. These parameters do not have defaults:
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=key_name, ENCRYPTION_TYPE={ DETERMINISTIC | RANDOMIZED }, ALGORITHM='algorithm')

DETERMINISTIC: guaranteed to encrypt the same string with the same binary result every time, which may offer less protection, especially for low-cardinality columns. This option may be a good choice for high-cardinality columns.

RANDOMIZED: binary results for the same string are randomized, providing more protection. This option is safer for low-cardinality columns.

ALGORITHM: All of the encryption algorithms use Output Feedback Mode (OFB). Valid values are AES_128_OFB, AES_192_OFB, and AES_256_OFB.

The following combinations of encryption types and algorithms are supported. (The third column in the table shows the equivalent numeric parameter that you would use to emulate this combination when calling the ENCRYPT_KS function explicitly. Parameters 1-3 for ENCRYPT_KS are not supported in CREATE TABLE.) For details about how the algorithms work, see Encryption and Decryption Algorithms.

Encryption Type Algorithm ENCRYPT_KS Function Parameter
DETERMINISTIC AES_128_OFB 4
DETERMINISTIC AES_192_OFB 5
DETERMINISTIC AES_256_OFB 6
RANDOMIZED AES_128_OFB 7
RANDOMIZED AES_192_OFB 8
RANDOMIZED AES_256_OFB 9

You must create encryption keys in the same database as the tables that will reference them. Do not use cross-database references to encryption keys in your CREATE TABLE statements. If you do this, you will lose those references when the database is backed up and restored (or replicated).

You cannot modify the definition of encrypted columns, but you can add encrypted columns to a table, using ALTER TABLE ADD COLUMN.

Encrypted columns do not support certain CREATE TABLE options, as shown in the following table:
Option DETERMINISTIC RANDOMIZED
Hash distribution (DISTRIBUTE ON column) Supported Not supported
Hash partitioning (HASH column) Supported Not supported
Range partitioning (RANGE column) Not supported Not supported
SORT ON column Not supported Not supported
CLUSTER ON column Not supported Not supported
table_constraint
Define constraints that apply to multiple columns in the table.
Note: PRIMARY KEY and UNIQUE constraints may be declared, but they are not enforced.
  • UNIQUE: declare that all values in the specified columns should be distinct.
  • PRIMARY KEY: declare a set of columns as the primary key of the table; implies that these columns are both NOT NULL and UNIQUE.
ON COMMIT
Set the behavior for temporary tables when a transaction commits.
  • PRESERVE ROWS: do not delete rows from temporary tables when transactions commit.
  • DELETE ROWS: delete rows from temporary tables when transactions commit.
  • DROP: drop temporary tables when transactions commit.
DISTRIBUTE
Define the data distribution scheme for the table (how the rows are distributed across the cluster). See Distribution Options.
Note: You cannot distribute a table on a floating-point column (FLOAT, FLOAT4, FLOAT8).
SORT ON
Define a sort column for the table (how the data is sorted when it is loaded). The SORT ON and CLUSTER ON options are mutually exclusive. See Sorted and Clustered Tables. Tables may be partitioned and sorted on the same column.
CLUSTER ON
Define up to four columns as cluster columns. The SORT ON and CLUSTER ON options are mutually exclusive. Tables may be partitioned and clustered on the same column. See Sorted and Clustered Tables.
Note: REAL and DOUBLE PRECISION columns cannot be defined as cluster columns.
PARTITION BY
Partition the table by one or more columns or expressions. See Partitioning Options. Tables may be partitioned and sorted (or clustered) on the same column. You cannot partition replicated tables.
Note: If used, the following clauses at the end of the CREATE TABLE statement must appear in the order shown in the syntax diagram:
  1. ON COMMIT
  2. DISTRIBUTE
  3. SORT ON or CLUSTER ON
  4. PARTITION BY