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 [, ... ] ) |  
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] }

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.

Tables have a limit of 2,000 user-defined columns.

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.

• 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. A PRIMARY KEY constraint implies that the column is both NOT NULL and UNIQUE. For example:

  seasonid smallint primary key

• REFERENCES: declare the column as a foreign key by naming the referenced table and column, which must be UNIQUE. For example:

  playerid bigint references player(playerid)

  FOREIGN KEY constraints are also created implicitly when a PRIMARY KEY constraint references a column in another table. The referenced column must be UNIQUE. For example:

  seasonid smallint primary key references season(seasonid)

If you want to define a PRIMARY KEY, FOREIGN KEY, or UNIQUE constraint that involves multiple columns, use a table constraint.

Important: Although PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints may be declared, they are not enforced.

If defined, these constraints may be used as hints during query planning, so you should declare them if an ETL process or some other process in your application enforces their integrity. For example, the query planner sometimes uses primary and foreign keys in numeric computations, subquery decorrelation, join ordering, and join elimination. The planner assumes that all keys in Yellowbrick tables are valid as loaded. If your application allows invalid keys, queries may return wrong results. As a rule of thumb, do not define key constraints for your tables if you doubt their validity, but always declare them when you know that they are valid.

NOT NULL column constraints are enforced.

If necessary, you can drop constraints by using ALTER TABLE commands.

table_constraint

Define constraints that apply to one or more columns in the table.

Note: PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints may be declared, but they are not enforced. See also column_constraint.

• 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.
• FOREIGN KEY: declare columns as foreign keys by naming the referenced table and columns. If no refcolumn list is specified, the primary key of the reftable is used.

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.

Note: You cannot create keys in one database and reference them in a table that you create in another database.

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

Note: 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

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

In This Section

• CREATE TABLE Examples

• Distribution Options

• Partitioning Options

• Sorted and Clustered Tables

Parent topic:SQL Commands