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) | REPLICATE | 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 ) ]}

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. FOREIGN KEY constraints are not supported.
  • 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.
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. FOREIGN KEY constraints are not supported.
  • 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