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, thebooks
table belongs to thepublic
schema and the currentpremdb
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 aDEFAULT
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 aNULL
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. APRIMARY KEY
constraint implies that the column is bothNOT NULL
andUNIQUE
. For example:seasonid smallint primary key
REFERENCES
: declare the column as a foreign key by naming the referenced table and column, which must beUNIQUE
. For example:playerid bigint references player(playerid)
FOREIGN KEY
constraints are also created implicitly when aPRIMARY KEY
constraint references a column in another table. The referenced column must beUNIQUE
. For example:seasonid smallint primary key references season(seasonid)
If you want to define a
PRIMARY KEY
,FOREIGN KEY
, orUNIQUE
constraint that involves multiple columns, use a table constraint.Important: AlthoughPRIMARY KEY
,FOREIGN KEY
, andUNIQUE
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
, andUNIQUE
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 bothNOT NULL
andUNIQUE
.FOREIGN KEY
: declare columns as foreign keys by naming the referenced table and columns. If norefcolumn
list is specified, the primary key of thereftable
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 theWITH
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 areAES_128_OFB
,AES_192_OFB
, andAES_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 forENCRYPT_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
andCLUSTER 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
andCLUSTER 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.
CREATE TABLE
statement must appear in the order shown in the syntax diagram: ON COMMIT
DISTRIBUTE
SORT ON
orCLUSTER ON
PARTITION BY