CREATE TABLE
CREATE TABLE — Define a new table
Synopsis
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT
EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
[ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT
EXISTS ] table_name
OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
[ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT
EXISTS ] table_name
PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
[ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH
SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator
[, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
[, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action
] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
like_option is:
{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED |
IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
An exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
Description
CREATE TABLE creates 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 given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table. The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in the same schema.
CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.
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 a SQL object that helps define the set of valid values in the table in various ways.
There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A constraint definition that is not tied to a particular column and can encompass more than one column is a table constraint. Every column constraint can also be written as a table constraint; a column constraint is simply a notational convenience for use when the constraint only affects one column.
To create a table, you must have the USAGE privilege on all column types or on the type in the OF clause, respectively.
Parameters
TEMPORARY or TEMP
If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). While the temporary table exists, an existing permanent table with the same name becomes invisible to the current session, though it can be referenced using a schema-qualified name. Any indexes created on a temporary table automatically become temporary as well.
The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed through the session's SQL commands. For example, if a temporary table is to be used for complex queries, it is best to run ANALYZE on it after it has been populated.
GLOBAL or LOCAL can optionally be written before TEMPORARY or TEMP.
UNLOGGED If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than regular tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.
IF NOT EXISTS
Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the table that would be created.
table_name
The name (optionally schema-qualified) of the table to be created.
OF type_name
Creates a typed table, which takes its structure from the specified composite type (the name can be schema-qualified).
A typed table is tied to its type; for example, if the type is dropped, the table will be dropped as well (using DROP TYPE ... CASCADE).
When a typed table is created, the data types of the columns are determined by the underlying composite type and are not specified directly in the CREATE TABLE command. However, the CREATE TABLE command can add defaults and constraints to the table and can specify storage parameters.
column_name
The name of a column to be created in the new table.
data_type
The data type of the column. This may include array specifications.
COLLATE collation
The COLLATE clause assigns a collation to the column (which must be of a collatable data type). If not specified, the default collation of the column's data type is used.
INHERITS ( parent_table [, ... ] )
The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Parent tables can be plain tables or foreign tables.
Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to the child, and by default the data of the child table is included in scans of the parent(s).
If the same column name exists in multiple parent tables, an error will be reported unless the data types of each such column in the parent tables match. If there are no conflicts, the duplicate columns are merged to form a single column in the new table. If the column name list of the new table includes a column that is also inherited, the data type must likewise match the inherited column, and the column definitions will be merged into one. If the new table explicitly specifies a default value for a column, this default value overrides any defaults from inherited column declarations. Otherwise, any parent must specify the same default value for the column, or an error will be reported.
CHECK constraints are also merged in essentially the same way as columns: if multiple parent tables or the new table definition contain identically named CHECK constraints, these constraints must all have the same check expression, or an error will be reported. Constraints with the same name and expression will be merged into one copy. A constraint marked NO INHERIT in a parent table will not be considered. Note that an unnamed CHECK constraint in the new table will never be merged, since a unique name will always be chosen for it. The STORAGE settings of columns are also copied from parent tables.
If a column in the parent table is an identity column, that property is not inherited. If needed, a column in the child table can be declared as an identity column.
PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ opclass ] [, ...] )
The optional PARTITION BY clause specifies a partitioning strategy for the table. The table created in this manner is called a partitioned table. The parenthesized list of columns or expressions forms the partition key for the table. When using range or hash partitioning, the partition key can include multiple columns or expressions (up to 32), but for list partitioning, the partition key must consist of a single column or expression.
Range and list partitioning require a btree operator class, while hash partitioning requires a hash operator class. If no operator class is explicitly specified, the default operator class for the respective type will be used; if no default operator class exists, an error will be raised. When using hash partitioning, the operator class used must implement support functions.
A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table itself is empty. Data rows inserted into the table will be routed to a partition based on the values of the columns or expressions in the partition key. If no existing partition matches the values in the new row, an error will be reported.
Partitioned tables do not support EXCLUDE constraints; however, you can define these constraints on individual partitions.
PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
Creates the table as a partition of the specified parent table. The table can be created as a partition for specific values using FOR VALUES, or as a default partition using DEFAULT. Any indexes, constraints, and user-defined row-level triggers present in the parent table will be cloned to the new partition.
partition_bound_spec must correspond to the partitioning method and partition key of the parent table, and must not overlap with any existing partition of the parent table. The form with IN is used for list partitioning, the form with FROM and TO is used for range partitioning, and the form with WITH is used for hash partitioning.
partition_bound_expr is any variable-free expression (subqueries, window functions, aggregate functions, and set-returning functions are not allowed). Its data type must match the data type of the corresponding partition key column. The expression is evaluated only once when the table is created, so it can even contain volatile expressions such as CURRENT_TIMESTAMP.
When creating a list partition, NULL can be specified to indicate that the partition allows the partition key column to be null. However, a given parent table cannot have more than one such list partition. NULL cannot be specified for range partitions.
When creating a range partition, the lower bound specified with FROM is inclusive, while the upper bound specified with TO is exclusive.
That is, the values specified in the FROM list are valid values for the corresponding partition key columns of the partition, while the values in the TO list are not. For example, given PARTITION BY RANGE (x,y), a partition with range FROM (1, 2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
When creating range partitions, the special values MINVALUE and MAXVALUE can be used to indicate that there is no lower or upper bound for a column value.
For example, a partition defined with FROM (MINVALUE) TO (10) allows any value less than 10, and a partition defined with FROM (10) TO (MAXVALUE) allows any value greater than or equal to 10.
When creating range partitions involving multiple columns, it also makes sense to use MAXVALUE as part of a lower bound and MINVALUE as part of an upper bound. For example, a partition defined with FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any row where the first partition key column is greater than 0 and less than or equal to 10. Similarly, a partition defined with FROM ('a', MINVALUE) TO ('b', MINVALUE) allows any row where the first partition key column starts with "a".
Note that if MINVALUE or MAXVALUE is used for one column of a partition boundary, the same value must be used for all subsequent columns. For example, (10, MINVALUE, 0) is not a valid boundary; you should write (10, MINVALUE, MINVALUE) instead.
Also note that some element types, such as timestamp, have a concept of "infinity", which is simply another value that can be stored. This is different from MINVALUE and MAXVALUE, which are not actual values that can be stored but rather represent ways of indicating unbounded values. MAXVALUE can be considered as a value greater than any other value (including "infinity"), and MINVALUE can be considered as a value smaller than any other value (including negative "infinity"). Therefore, the range FROM ('infinity') TO (MAXVALUE) is not an empty range; it allows storing only one value — "infinity".
If DEFAULT is specified, the table will be created as the default partition of the parent table. This option is not applicable to hash-partitioned tables. Partition key values that do not match any other partition of the given parent table will be routed to the default partition.
When a table already has a DEFAULT partition and a new partition is being added to it, the default partition must be scanned to verify that it does not contain any rows that may belong to the new partition. If the default partition contains a large number of rows, this may be slow. If the default partition is a foreign table or it has a constraint that provably cannot contain rows that would be placed in the new partition, the scan will be skipped. When creating a hash partition, a modulus and remainder must be specified. The modulus must be a positive integer, and the remainder must be a non-negative integer less than the modulus. Typically, when initially setting up a hash-partitioned table, you should choose a modulus equal to the number of partitions and assign the same modulus and different remainders to each table (see the example below). However, it is not required that each partition have the same modulus; the only requirement is that each modulus that appears among partitions of a hash-partitioned table is a factor of the next larger modulus. This allows incrementally increasing the number of partitions without having to move all data at once. For example, suppose you have a hash-partitioned table with 8 partitions, each with modulus 8, but find it necessary to increase the number of partitions to 16. You can split one of the modulus-8 partitions and then create two new modulus-16 partitions to cover the same portion of the key space (one with a remainder equal to the remainder of the split partition, and another with a remainder equal to that value plus 8), and then repopulate them with data. Then you can repeat this process for each remainder-8 partition until none remain. Although each of these steps may involve significant data movement, it is still better than building an entirely new table and moving all data at once.
A partition must have the same column names and types as the partitioned table it belongs to. Changes to the column names or types of the partitioned table will automatically propagate to all partitions. CHECK constraints are automatically inherited by each partition, but individual partitions may specify additional CHECK constraints; additional constraints with the same name and condition as the parent table will be merged with the parent constraint. Default values can be specified separately for each partition. Note, however, that partition default values are not applied when tuples are inserted through the partitioned table.
Rows inserted into a partitioned table will automatically be routed to the correct partition. If no suitable partition exists, an error will occur.
Operations such as TRUNCATE that normally affect a table and all its inheritance children will cascade to all partitions, but can also be executed on individual partitions. Note that dropping a partition using DROP TABLE requires an ACCESS EXCLUSIVE lock on the parent table.
LIKE source_table [ like_option ... ]
LIKE specifies a table from which the new table automatically copies all column names, data types, and their not-null constraints.
Unlike INHERITS, the new table and the original table are completely separate after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include the new table's data in scans of the original table.
Also unlike INHERITS, columns and constraints copied with LIKE are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause, an error will be raised.
The optional like_option clauses specify additional attributes of the original table to copy. Specify INCLUDING to copy the attribute, or EXCLUDING to ignore it. EXCLUDING is the default. If multiple specifications for the same type of object are given, the last one applies. Available options include:
INCLUDING COMMENTS
Comments on copied columns, constraints, and indexes will be copied. The default behavior is to strip comments, resulting in copied columns and constraints in the new table having no comments.
INCLUDING CONSTRAINTS
CHECK constraints will be copied. There is no distinction between column constraints and table constraints. Not-null constraints are always copied to the new table.
INCLUDING DEFAULTS
Default expressions for copied column definitions will be copied. Otherwise, default expressions are not copied, resulting in copied columns in the new table having null defaults. Note that copying defaults that call database-modification functions, such as nextval, may create a functional association between the original table and the new table.
INCLUDING GENERATED
Any generation expressions for copied column definitions will be copied. By default, the new columns will be regular base columns.
INCLUDING IDENTITY
Any identity specifications for copied column definitions will be copied. A new sequence is created for each identity column in the new table, separate from the sequence associated with the old table.
INCLUDING INDEXES
Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be created on the new table. Names for the new indexes and constraints are chosen according to the default rules, regardless of the original naming. (This behavior avoids potential duplicate name failures for the new indexes.)
INCLUDING STATISTICS
Extended statistics will be copied to the new table.
INCLUDING STORAGE
STORAGE settings for copied column definitions will be copied. The default behavior is to exclude STORAGE settings, resulting in copied columns in the new table having type-specified default settings.
INCLUDING ALL
INCLUDING ALL is a shorthand for selecting all available individual options. (It can be used in combination with individual EXCLUDING clauses after INCLUDING ALL to select all options except specific ones.)
The LIKE clause can also be used to copy column definitions from a view, foreign table, or composite type. Inappropriate options (such as INCLUDING INDEXES from a view) are ignored.
CONSTRAINT constraint_name
An optional name for a column or table constraint. If the constraint is violated, the constraint name will appear in error messages, so constraint names like "column must be positive" can be used to communicate useful constraint information to client applications (double quotes are required when specifying constraint names containing spaces). If no constraint name is specified, the system will generate one.
NOT NULL
The column does not allow null values.
NULL
The column is allowed to contain null values. This is the default.
This clause is provided only for compatibility with non-standard SQL databases. Its use is not recommended in new applications.
CHECK ( expression ) [ NO INHERIT ]
CHECK specifies an expression that produces a Boolean result that new or updated rows must satisfy for an insert or update operation to succeed. Expressions that evaluate to TRUE or UNKNOWN succeed. If any inserted or updated row produces a FALSE result, an error exception is raised and the insert or update does not modify the database. A check constraint specified as a column constraint should reference only that column's value, while an expression appearing in a table constraint can reference multiple columns. Currently, CHECK expressions cannot contain subqueries and cannot reference variables other than columns of the current row. The system column tableoid can be referenced, but no other system column can.
A constraint marked NO INHERIT will not propagate to child tables.
When a table has multiple CHECK constraints, they will be checked for each row in alphabetical order of their names, after NOT NULL constraints have been checked.
DEFAULT default_expr
The DEFAULT clause assigns a default data value for the column in its definition. The value is an expression (in particular, cross-references to other columns are not allowed). Subqueries are also not allowed. The data type of the default expression must match the column's data type.
The default expression will be used in any insert operation that does not specify a value for the column. If a column has no default value, the default is null.
GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column. The column cannot be written to, and when read, it will return the result of the specified expression.
The keyword STORED indicates that the column will be computed on write and stored on disk.
The generation expression can reference other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
This clause creates the column as an identity column. It will have an implicit sequence attached to it, and the column in new rows will automatically receive a value from the assigned sequence.
The ALWAYS and BY DEFAULT clauses determine how user-specified values are handled in INSERT and UPDATE commands.
In an INSERT command, if ALWAYS is selected, a user-specified value is accepted only when the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, the user-specified value takes precedence. See INSERT for details. (In a COPY command, user-specified values are always used regardless of this setting.)
In an UPDATE command, if ALWAYS is selected, updating the column to any value other than DEFAULT will be rejected. If BY DEFAULT is selected, the column can be updated normally. (The UPDATE command does not have an OVERRIDING clause.)
The optional sequence_options clause can be used to override options for the sequence. See CREATE SEQUENCE for details.
UNIQUE (column constraint)
UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ] (table constraint)
The UNIQUE constraint specifies that a group of one or more columns of a table can contain only unique values. The behavior of a unique table constraint is the same as that of a column constraint, except that the table constraint can span multiple columns.
For the purpose of a unique constraint, null values are not considered equal.
Each unique table constraint must name a set of columns that is different from the set of columns named by any other unique or primary key constraint on the table (otherwise it would be a constraint listed twice).
When establishing unique constraints on a multi-level partition hierarchy, all columns in the partition key of the target partitioned table, and all partitioned tables derived from it, must be included in the constraint definition.
Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. The optional INCLUDE clause adds one or more columns to the index without enforcing uniqueness. Note that although the constraint is not enforced on included columns, it still depends on them. Therefore, certain operations on these columns (such as DROP COLUMN) may cause cascading constraint and index deletion.
PRIMARY KEY (column constraint)
PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ] (table constraint)
The PRIMARY KEY constraint specifies that one or more columns of a table can only contain unique (non-duplicate), non-null values. Only one primary key can be specified for a table, either as a column constraint or a table constraint.
The set of columns involved in the primary key constraint should be different from the set of columns of any unique constraint defined on the same table (otherwise the unique constraint is redundant and will be discarded).
The data constraint enforced by PRIMARY KEY can be viewed as a combination of UNIQUE and NOT NULL, but identifying a set of columns as a primary key also provides metadata for schema design, since the primary key identifies that this set of columns can be relied upon by other tables as a unique identifier for rows.
PRIMARY KEY constraints share the same restrictions as UNIQUE constraints when placed on partitioned tables.
Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used for the constraint. The optional INCLUDE clause allows specifying a list of columns to be included in the non-key portion of the index. Although uniqueness is not enforced on included columns, the constraint still depends on them. Therefore, certain operations on included columns (such as DROP COLUMN) may cause cascading constraint and index deletion.
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] )
index_parameters [ WHERE ( predicate ) ]
The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified columns or expressions using the specified operators, not all comparisons will return TRUE. If all specified operators test equality, this is equivalent to a UNIQUE constraint, although a plain unique constraint will be faster. However, exclusion constraints can specify more general constraints than simple equality. For example, you can use the && operator to specify a constraint that no two rows in the table contain overlapping circles.
Exclusion constraints are implemented using an index, so each specified operator must be associated with an appropriate operator class for the index access method index_method. The operators are required to be commutative. Each exclude_element can optionally specify an operator class or ordering options, which are fully described in CREATE INDEX.
The access method must support amgettuple, which currently means GIN cannot be used. Although allowed, using a B-tree or hash index in an exclusion constraint makes little sense, since it cannot do better than a plain unique index. Therefore, in practice, the access method will always be GiST or SP-GiST.
The predicate allows you to specify an exclusion constraint on a subset of the table. Internally, this creates a partial index. Note that the surrounding parentheses for this are required.
REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action
] (table constraint)
These clauses specify a foreign key constraint that requires the new table's column or group of columns to contain only values that match those in the referenced columns of some row in the referenced table. If the refcolumn list is omitted, the primary key of the reftable is used.
The referenced columns must be the columns of a non-deferrable unique constraint or primary key constraint in the referenced table. The user must have the REFERENCES privilege on the referenced table (or the entire table, or specific referenced columns). Adding a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note that foreign key constraints cannot be defined between temporary and permanent tables.
A value inserted into the referencing column(s) is matched against the values of the referenced table using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default).
MATCH FULL does not allow one column of a multi-column foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any foreign key column to be null; if any is null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented (of course, NOT NULL constraints can be applied to referencing columns to prevent these situations).
Additionally, when the data in the referenced columns is changed, certain actions can be performed on the data in the columns of this table. ON DELETE specifies the action to perform when a referenced row in the referenced table is deleted. Similarly, ON UPDATE specifies the action to perform when a referenced column in the referenced table is updated to a new value. If the row is updated but the referenced column is not actually changed, no action is taken. Referential actions other than NO ACTION checks cannot be deferred, even if the constraint is declared deferrable. The possible actions for each clause are:
NO ACTION
Produces an error indicating that the deletion or update would result in a foreign key constraint violation. If the constraint is deferred and referencing rows still exist, this error will be produced when the constraint is checked. This is the default action.
RESTRICT
Produces an error indicating that the deletion or update would result in a foreign key constraint violation. This action is essentially the same as NO ACTION, except that the check is not deferrable.
CASCADE
Deletes any row that references the deleted row, or updates the referencing column values to the new values of the referenced columns.
SET NULL
Sets the referencing columns to null.
SET DEFAULT
Sets the referencing columns to their default values (if the default value is non-null, there must be a row in the referenced table matching the default value, otherwise the operation will fail).
If the referenced columns are changed frequently, it is best to add an index on the referencing columns so that the referential actions associated with the foreign key constraint can be performed more efficiently.
DEFERRABLE
NOT DEFERRABLE
This clause controls whether the constraint can be deferred. A non-deferrable constraint is checked immediately after each command. A deferrable constraint's check is postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that in INSERT statements that include an ON CONFLICT DO UPDATE clause, deferrable constraints cannot be used as conflict arbiters.
INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default timing for checking the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check timing can be modified using the SET CONSTRAINTS command.
USING method
This optional clause specifies the table access method to use for storing the contents of the new table; the method must be an access method of type TABLE.
WITH ( storage_parameter [= value] [, ... ] )
This clause specifies optional storage parameters for a table or index; see Storage Parameters for details. For backward compatibility, the WITH clause for a table can also include OIDS=FALSE to specify that rows of the new table should not contain OIDs (object identifiers). OIDS=TRUE is no longer supported.
WITHOUT OIDS
This is backward-compatible syntax for declaring the table WITHOUT OIDS. Creating a table WITH OIDS is no longer supported.
ON COMMIT
The behavior of temporary tables at the end of a transaction block is controlled by ON COMMIT. The three options are:
PRESERVE ROWS
No special action is taken at the end of the transaction. This is the default.
DELETE ROWS
All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit. When applied to a partitioned table, this does not cascade to its partitions.
DROP
The temporary table will be dropped at the end of the current transaction block. When used on a partitioned table, this operation drops its partitions, and when used on a table with inheritance children, it drops the dependent children.
TABLESPACE tablespace_name
tablespace_name is the name of the tablespace in which the new table is to be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary. For partitioned tables, since the table itself does not need storage, specifying a tablespace overrides default_tablespace as the default tablespace for any newly created partitions that do not explicitly specify a different tablespace.
USING INDEX TABLESPACE tablespace_name
This clause allows you to select the tablespace in which the index associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary.
Storage Parameters
The WITH clause can specify storage parameters for a table or for the index associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint. Storage parameters for indexes have already been introduced in CREATE INDEX. The storage parameters currently available for tables are listed below. As shown below, for many of these parameters, there is an additional parameter with a toast. prefix that can be used to control the behavior of the table's secondary TOAST table (if one exists). If a table parameter value is set but the corresponding toast. parameter is not set, the TOAST table will use the table's parameter value. Specifying these parameters for partitioned tables is not supported, but they can be specified for individual leaf partitions.
fillfactor (integer)
The fill factor for a table is a percentage between 10 and 100. 100 (complete filling) is the default. When a smaller fill factor is specified, INSERT operations will fill table pages only to the specified percentage, leaving remaining space on each page for updates to rows on that page. This gives UPDATE a chance to place the updated version of a row on the same page as its original version, which is more efficient than placing it on a different page. For tables that are never updated, full filling is the best choice, but for heavily updated tables, a smaller fill factor is more appropriate. This parameter cannot be set for TOAST tables.
toast_tuple_target (integer)
The toast_tuple_target specifies the minimum tuple length required before we try to compress and/or move long column values to the TOAST table, and it is also the target length that toasting attempts to reduce to when starting. This affects columns marked as External (for moving), Main (for compression), or Extended (for both), and only applies to new tuples. It has no effect on existing rows. By default, this parameter is set to allow at least 4 tuples per block, with a default block size of 2040 bytes. Valid values are between 128 bytes and (block size - header), with a default size of 8160 bytes. Changing this value may not be useful for very short or very long rows. Note that the default setting is typically close to optimal, and setting this parameter may have negative effects in some cases. This parameter cannot be set for TOAST tables.
parallel_workers (integer)
This parameter sets the number of workers to use for auxiliary parallel scanning of this table. If this parameter is not set, the system will determine a value based on the relation's size. The number of workers chosen by the planner or by utilities using parallel scans may be lower, for example, if the max_worker_processes setting is too small.
autovacuum_enabled, toast.autovacuum_enabled (boolean)
Enables or disables the autovacuum daemon for a specific table. If true, the autovacuum daemon will perform automatic VACUUM or ANALYZE operations on this table following the rules discussed in. If false, this table will not be autovacuumed, although it will still be autovacuumed to prevent transaction ID wraparound. If the autovacuum parameter is false, the autovacuum daemon will not run at all (except to prevent transaction ID wraparound), and setting individual table storage parameters will not override this setting. Therefore, explicitly setting this storage parameter to true rarely has significant value; setting it to false is more useful.
vacuum_index_cleanup, toast.vacuum_index_cleanup (boolean)
Enables or disables index cleanup when VACUUM runs on this table. The default is true. Disabling index cleanup can significantly speed up VACUUM, but may also lead to severe index bloat if the table is frequently modified. The INDEX_CLEANUP parameter of VACUUM, if specified, overrides the value of this option.
vacuum_truncate, toast.vacuum_truncate (boolean)
Enables or disables vacuum's attempt to truncate any empty pages at the end of this table. The default is true. If true, VACUUM and autovacuum will perform truncation, and the disk space of truncated pages will be returned to the operating system. Note that truncation requires an ACCESS EXCLUSIVE lock on the table. The TRUNCATE parameter of VACUUM, if specified, overrides the value of this option.
autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold (integer)
Per-table value of the autovacuum_vacuum_threshold parameter.
autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (floating point)
Per-table value of the autovacuum_vacuum_scale_factor parameter.
autovacuum_vacuum_insert_threshold, toast.autovacuum_vacuum_insert_threshold (integer)
Per-table value of the autovacuum_vacuum_insert_threshold parameter. The special value -1 can be used to disable insert vacuuming on the table.
autovacuum_vacuum_insert_scale_factor,
toast.autovacuum_vacuum_insert_scale_factor (float4)
Per-table value of the autovacuum_vacuum_insert_scale_factor parameter.
autovacuum_analyze_threshold (integer)
Per-table value of the autovacuum_analyze_threshold parameter.
autovacuum_analyze_scale_factor (floating point)
Per-table value of the autovacuum_analyze_scale_factor parameter.
autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (floating point)
Per-table value of the autovacuum_vacuum_cost_delay parameter.
autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (integer)
Per-table value of the autovacuum_vacuum_cost_limit parameter.
autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
Per-table value of the vacuum_freeze_min_age parameter. Note that autovacuum will ignore per-table autovacuum_freeze_min_age parameters that exceed half of the system-wide autovacuum_freeze_max_age parameter.
autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
Per-table value of the autovacuum_freeze_max_age parameter. Note that autovacuum will ignore per-table autovacuum_freeze_max_age parameters that exceed half of the system-wide parameter (which can only be set lower).
autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age (integer)
Per-table value of the vacuum_freeze_table_age parameter.
autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer)
Per-table value of the vacuum_multixact_freeze_min_age parameter. Note that autovacuum will ignore per-table autovacuum_multixact_freeze_min_age parameters that exceed half of the system-wide autovacuum_multixact_freeze_max_age parameter.
autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer)
Per-table value of the autovacuum_multixact_freeze_max_age parameter. Note that autovacuum will ignore per-table autovacuum_multixact_freeze_max_age parameters that exceed half of the system-wide parameter (which can only be set lower).
autovacuum_multixact_freeze_table_age,
toast.autovacuum_multixact_freeze_table_age (integer)
Per-table value of the vacuum_multixact_freeze_table_age parameter.
log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer)
Per-table value of the log_autovacuum_min_duration parameter.
user_catalog_table (boolean)
Declares that the table is an additional catalog table for logical replication purposes.
Notes
The system creates an index for each unique constraint and primary key constraint to enforce uniqueness. Therefore, it is not necessary to explicitly create an index for the primary key columns (see CREATE INDEX for details).
In the current implementation, unique constraints and primary keys are not inherited. This makes the combination of inheritance and unique constraints rather non-standard.
A table cannot have more than 1600 columns (in practice, the effective limit is usually lower due to tuple length restrictions).
Examples
# Create tables films and distributors:
CREATE TABLE films (
code char(5) CONSTRAINT firstkey PRIMARY KEY,
title varchar(40) NOT NULL,
did integer NOT NULL,
date_prod date,
kind varchar(10),
len interval hour to minute
);
CREATE TABLE distributors (
did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
name varchar(40) NOT NULL CHECK (name <> '')
);
# Create a table with a two-dimensional array:
CREATE TABLE array_int (
vector int[][]);
# Define a unique table constraint for the table films. A unique table constraint can be defined on one or more columns of the table:
CREATE TABLE films (
code char(5),
title varchar(40),
did integer,
date_prod date,
kind varchar(10),
len interval hour to minute,
CONSTRAINT production UNIQUE(date_prod)
);
# Define a column check constraint:
CREATE TABLE distributors (
did integer CHECK (did > 100),
name varchar(40)
);
# Define a table check constraint:
CREATE TABLE distributors (
did integer,
name varchar(40),
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);
# Define a primary key table constraint for the table films:
CREATE TABLE films (
code char(5),
title varchar(40),
did integer,
date_prod date,
kind varchar(10),
len interval hour to minute,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
# Define a primary key constraint for the table distributors. The following two examples are equivalent, the first uses table constraint syntax, the second uses column constraint syntax:
CREATE TABLE distributors (
did integer,
name varchar(40),
PRIMARY KEY(did)
);
CREATE TABLE distributors (
did integer PRIMARY KEY,
name varchar(40)
);
# Assign a literal constant default value to the column name, arrange for the default value of column did to be generated by selecting the next value from a sequence object, and make the default value of modtime be the time when the row is inserted:
CREATE TABLE distributors (
name varchar(40) DEFAULT 'Luso Films',
did integer DEFAULT nextval('distributors_serial'),
modtime timestamp DEFAULT current_timestamp
);
# Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:
CREATE TABLE distributors (
did integer CONSTRAINT no_null NOT NULL,
name varchar(40) NOT NULL
);
# Define a unique constraint for the column name:
CREATE TABLE distributors (
did integer,
name varchar(40) UNIQUE
);
# The same unique constraint specified as a table constraint:
CREATE TABLE distributors (
did integer,
name varchar(40),
UNIQUE(name)
);
# Create the same table, specifying a 70% fill factor for the table and its unique index:
CREATE TABLE distributors (
did integer,
name varchar(40),
UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);
# Create table circles with an exclusion constraint that prevents any two circles from overlapping:
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);
# Create table cinemas in tablespace diskvol1:
CREATE TABLE cinemas (
id serial,
name text,
location text
) TABLESPACE diskvol1;
# Create a composite type and a typed table:
CREATE TYPE employee_type AS (name text, salary numeric);
CREATE TABLE employees OF employee_type (
PRIMARY KEY (name),
salary WITH OPTIONS DEFAULT 1000
);
# Create a range-partitioned table:
CREATE TABLE measurement (
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
# Create a range-partitioned table with multiple columns in the partition key:
CREATE TABLE measurement_year_month (
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
# Create a list-partitioned table:
CREATE TABLE cities (
city_id bigserial not null,
name text not null,
population bigint
) PARTITION BY LIST (left(lower(name), 1));
# Create a hash-partitioned table:
CREATE TABLE orders (
order_id bigint not null,
cust_id bigint not null,
status text
) PARTITION BY HASH (order_id);
# Create a partition of the range-partitioned table:
CREATE TABLE measurement_y2016m07
PARTITION OF measurement (
unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
# Create several partitions of a range-partitioned table with multiple columns in the partition key:
CREATE TABLE measurement_ym_older
PARTITION OF measurement_year_month
FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
CREATE TABLE measurement_ym_y2016m11
PARTITION OF measurement_year_month
FOR VALUES FROM (2016, 11) TO (2016, 12);
CREATE TABLE measurement_ym_y2016m12
PARTITION OF measurement_year_month
FOR VALUES FROM (2016, 12) TO (2017, 01);
CREATE TABLE measurement_ym_y2017m01
PARTITION OF measurement_year_month
FOR VALUES FROM (2017, 01) TO (2017, 02);
# Create a partition of the list-partitioned table:
CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');
# Create a partition of the list-partitioned table that is itself partitioned, then add a partition to it:
CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
CREATE TABLE cities_ab_10000_to_100000
PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
# Create partitions of a hash-partitioned table:
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
# Create a default partition:
CREATE TABLE cities_partdef
PARTITION OF cities DEFAULT;
Compatibility
The CREATE TABLE command conforms to the SQL standard, with the following exceptions.
Temporary Tables
Although the syntax of CREATE TEMPORARY TABLE closely resembles the SQL standard's syntax, it is in fact not the same. In the standard, temporary tables need to be defined only once and automatically exist (starting empty) in every session that needs them. The system requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard approach constrains all instances of a given temporary table name to have the same table structure.
Column Check Constraints
The SQL standard states that CHECK column constraints can only reference the column they apply to; only CHECK table constraints can reference multiple columns.
EXCLUDE Constraints
The EXCLUDE constraint type is a Halo extension.
NULL "Constraint"
The NULL "constraint" (actually a non-constraint) is an extension to the SQL standard, included for compatibility with some other database systems (along with the symmetric NOT NULL constraint). Since it is the default for any column, its presence is essentially noise.
Constraint Naming
The SQL standard requires that table and domain constraints have unique names within the schema containing the table or domain. Halo is more lenient: it only requires constraint names to be unique among constraints attached to a particular table or domain. However, for index-based constraints (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), this particular freedom does not exist, because the associated index is named the same as the constraint, and index names must be unique among all relations in the same schema.
Inheritance
Multiple inheritance via the INHERITS clause is a Halo extension. SQL:1999 and later standards define single inheritance using different syntax and different semantics.
Zero-column Tables
Halo allows creating a table with no columns (e.g., CREATE TABLE foo();). This is an extension to the SQL standard, which does not allow zero-column tables. Zero-column tables are not particularly useful in themselves, but disallowing them would create strange special cases for ALTER TABLE DROP COLUMN, so ignoring this restriction seems cleaner.
Multiple Identity Columns
Halo allows a table to have multiple identity columns. The standard specifies that a table can have at most one identity column. This is primarily to provide greater flexibility for schema changes or migrations. Note that the INSERT command only supports one override clause that applies to the entire statement, so multiple identity columns with different behaviors are not supported.
Generated Columns
The STORED option is not standard but is also used in other SQL implementations. The SQL standard does not specify the storage of generated columns.
LIKE Clause
Although the SQL standard includes a LIKE clause, the LIKE clause accepted by Halo is richer than the standard.
WITH Clause
The WITH clause is a Halo extension; storage parameters are not in the standard. The tablespace concept is not part of the standard.
Typed Tables
Typed tables implement a subset of the SQL standard. According to the standard, a typed table has columns corresponding to the underlying composite type, plus additional "self-referencing columns". Halo does not explicitly support self-referencing columns.
PARTITION BY Clause
The PARTITION BY clause is a Halo extension.
PARTITION OF Clause
The PARTITION OF clause is a Halo extension.
See Also
ALTER TABLE, DROP TABLE, CREATE TABLE AS, CREATE TABLESPACE, CREATE TYPE