2 doc/src/sgml/ref/create_table.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETABLE">
7 <indexterm zone="sql-createtable">
8 <primary>CREATE TABLE</primary>
12 <refentrytitle>CREATE TABLE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>CREATE TABLE</refname>
19 <refpurpose>define a new table</refpurpose>
24 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
25 { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
26 | <replaceable>table_constraint</replaceable>
27 | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
30 [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
31 [ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
32 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
33 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
34 [ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
36 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable>
37 OF <replaceable class="PARAMETER">type_name</replaceable> [ (
38 { <replaceable class="PARAMETER">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
39 | <replaceable>table_constraint</replaceable> }
42 [ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
43 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
44 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
45 [ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
47 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable>
48 PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable> [ (
49 { <replaceable class="PARAMETER">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
50 | <replaceable>table_constraint</replaceable> }
52 ) ] { FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable> | DEFAULT }
53 [ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
54 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
55 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
56 [ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
58 <phrase>where <replaceable class="PARAMETER">column_constraint</replaceable> is:</phrase>
60 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
63 CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
64 DEFAULT <replaceable>default_expr</replaceable> |
65 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
66 UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
67 PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
68 REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
69 [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
70 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
72 <phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase>
74 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
75 { CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
76 UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
77 PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
78 EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
79 FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
80 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
81 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
83 <phrase>and <replaceable class="PARAMETER">like_option</replaceable> is:</phrase>
85 { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }
87 <phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
89 IN ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | NULL } [, ...] ) |
90 FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] )
91 TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] )
93 <phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
95 [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
96 [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
98 <phrase><replaceable class="PARAMETER">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
100 { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
105 <refsect1 id="SQL-CREATETABLE-description">
106 <title>Description</title>
109 <command>CREATE TABLE</command> will create a new, initially empty table
110 in the current database. The table will be owned by the user issuing the
115 If a schema name is given (for example, <literal>CREATE TABLE
116 myschema.mytable ...</>) then the table is created in the specified
117 schema. Otherwise it is created in the current schema. Temporary
118 tables exist in a special schema, so a schema name cannot be given
119 when creating a temporary table. The name of the table must be
120 distinct from the name of any other table, sequence, index, view,
121 or foreign table in the same schema.
125 <command>CREATE TABLE</command> also automatically creates a data
126 type that represents the composite type corresponding
127 to one row of the table. Therefore, tables cannot have the same
128 name as any existing data type in the same schema.
132 The optional constraint clauses specify constraints (tests) that
133 new or updated rows must satisfy for an insert or update operation
134 to succeed. A constraint is an SQL object that helps define the
135 set of valid values in the table in various ways.
139 There are two ways to define constraints: table constraints and
140 column constraints. A column constraint is defined as part of a
141 column definition. A table constraint definition is not tied to a
142 particular column, and it can encompass more than one column.
143 Every column constraint can also be written as a table constraint;
144 a column constraint is only a notational convenience for use when the
145 constraint only affects one column.
149 To be able to create a table, you must have <literal>USAGE</literal>
150 privilege on all column types or the type in the <literal>OF</literal>
151 clause, respectively.
156 <title>Parameters</title>
160 <varlistentry id="SQL-CREATETABLE-TEMPORARY">
161 <term><literal>TEMPORARY</> or <literal>TEMP</></term>
164 If specified, the table is created as a temporary table.
165 Temporary tables are automatically dropped at the end of a
166 session, or optionally at the end of the current transaction
167 (see <literal>ON COMMIT</literal> below). Existing permanent
168 tables with the same name are not visible to the current session
169 while the temporary table exists, unless they are referenced
170 with schema-qualified names. Any indexes created on a temporary
171 table are automatically temporary as well.
175 The <link linkend="autovacuum">autovacuum daemon</link> cannot
176 access and therefore cannot vacuum or analyze temporary tables.
177 For this reason, appropriate vacuum and analyze operations should be
178 performed via session SQL commands. For example, if a temporary
179 table is going to be used in complex queries, it is wise to run
180 <command>ANALYZE</> on the temporary table after it is populated.
184 Optionally, <literal>GLOBAL</literal> or <literal>LOCAL</literal>
185 can be written before <literal>TEMPORARY</> or <literal>TEMP</>.
186 This presently makes no difference in <productname>PostgreSQL</>
187 and is deprecated; see
188 <xref linkend="sql-createtable-compatibility"
189 endterm="sql-createtable-compatibility-title">.
194 <varlistentry id="SQL-CREATETABLE-UNLOGGED">
195 <term><literal>UNLOGGED</></term>
198 If specified, the table is created as an unlogged table. Data written
199 to unlogged tables is not written to the write-ahead log (see <xref
200 linkend="wal">), which makes them considerably faster than ordinary
201 tables. However, they are not crash-safe: an unlogged table is
202 automatically truncated after a crash or unclean shutdown. The contents
203 of an unlogged table are also not replicated to standby servers.
204 Any indexes created on an unlogged table are automatically unlogged as
211 <term><literal>IF NOT EXISTS</></term>
214 Do not throw an error if a relation with the same name already exists.
215 A notice is issued in this case. Note that there is no guarantee that
216 the existing relation is anything like the one that would have been
223 <term><replaceable class="PARAMETER">table_name</replaceable></term>
226 The name (optionally schema-qualified) of the table to be created.
232 <term><literal>OF <replaceable class="PARAMETER">type_name</replaceable></literal></term>
235 Creates a <firstterm>typed table</firstterm>, which takes its
236 structure from the specified composite type (name optionally
237 schema-qualified). A typed table is tied to its type; for
238 example the table will be dropped if the type is dropped
239 (with <literal>DROP TYPE ... CASCADE</literal>).
243 When a typed table is created, then the data types of the
244 columns are determined by the underlying composite type and are
245 not specified by the <literal>CREATE TABLE</literal> command.
246 But the <literal>CREATE TABLE</literal> command can add defaults
247 and constraints to the table and can specify storage parameters.
252 <varlistentry id="SQL-CREATETABLE-PARTITION">
253 <term><literal>PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable> { FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable> | DEFAULT }</literal></term>
256 Creates the table as a <firstterm>partition</firstterm> of the specified
257 parent table. The table can be created either as a partition for specific
258 values using <literal>FOR VALUES</literal> or as a default partition
259 using <literal>DEFAULT</literal>.
263 The <replaceable class="PARAMETER">partition_bound_spec</replaceable>
264 must correspond to the partitioning method and partition key of the
265 parent table, and must not overlap with any existing partition of that
266 parent. The form with <literal>IN</> is used for list partitioning,
267 while the form with <literal>FROM</> and <literal>TO</> is used for
272 Each of the values specified in
273 the <replaceable class="PARAMETER">partition_bound_spec</> is
274 a literal, <literal>NULL</literal>, <literal>MINVALUE</literal>, or
275 <literal>MAXVALUE</literal>. Each literal value must be either a
276 numeric constant that is coercible to the corresponding partition key
277 column's type, or a string literal that is valid input for that type.
281 When creating a list partition, <literal>NULL</literal> can be
282 specified to signify that the partition allows the partition key
283 column to be null. However, there cannot be more than one such
284 list partition for a given parent table. <literal>NULL</literal>
285 cannot be specified for range partitions.
289 When creating a range partition, the lower bound specified with
290 <literal>FROM</literal> is an inclusive bound, whereas the upper
291 bound specified with <literal>TO</literal> is an exclusive bound.
292 That is, the values specified in the <literal>FROM</literal> list
293 are valid values of the corresponding partition key columns for this
294 partition, whereas those in the <literal>TO</literal> list are
295 not. Note that this statement must be understood according to the
296 rules of row-wise comparison (<xref linkend="row-wise-comparison">).
297 For example, given <literal>PARTITION BY RANGE (x,y)</>, a partition
298 bound <literal>FROM (1, 2) TO (3, 4)</literal>
299 allows <literal>x=1</> with any <literal>y>=2</>,
300 <literal>x=2</> with any non-null <literal>y</>,
301 and <literal>x=3</> with any <literal>y<4</>.
305 The special values <literal>MINVALUE</> and <literal>MAXVALUE</>
306 may be used when creating a range partition to indicate that there
307 is no lower or upper bound on the column's value. For example, a
308 partition defined using <literal>FROM (MINVALUE) TO (10)</> allows
309 any values less than 10, and a partition defined using
310 <literal>FROM (10) TO (MAXVALUE)</> allows any values greater than
315 When creating a range partition involving more than one column, it
316 can also make sense to use <literal>MAXVALUE</> as part of the lower
317 bound, and <literal>MINVALUE</> as part of the upper bound. For
318 example, a partition defined using
319 <literal>FROM (0, MAXVALUE) TO (10, MAXVALUE)</> allows any rows
320 where the first partition key column is greater than 0 and less than
321 or equal to 10. Similarly, a partition defined using
322 <literal>FROM ('a', MINVALUE) TO ('b', MINVALUE)</> allows any rows
323 where the first partition key column starts with "a".
327 Note that if <literal>MINVALUE</> or <literal>MAXVALUE</> is used for
328 one column of a partitioning bound, the same value must be used for all
329 subsequent columns. For example, <literal>(10, MINVALUE, 0)</> is not
330 a valid bound; you should write <literal>(10, MINVALUE, MINVALUE)</>.
334 Also note that some element types, such as <literal>timestamp</>,
335 have a notion of "infinity", which is just another value that can
336 be stored. This is different from <literal>MINVALUE</> and
337 <literal>MAXVALUE</>, which are not real values that can be stored,
338 but rather they are ways of saying that the value is unbounded.
339 <literal>MAXVALUE</> can be thought of as being greater than any
340 other value, including "infinity" and <literal>MINVALUE</> as being
341 less than any other value, including "minus infinity". Thus the range
342 <literal>FROM ('infinity') TO (MAXVALUE)</> is not an empty range; it
343 allows precisely one value to be stored — "infinity".
347 If <literal>DEFAULT</literal> is specified, the table will be
348 created as a default partition of the parent table. The parent can
349 either be a list or range partitioned table. A partition key value
350 not fitting into any other partition of the given parent will be
351 routed to the default partition. There can be only one default
352 partition for a given parent table.
356 When a table has an existing <literal>DEFAULT</literal> partition and
357 a new partition is added to it, the existing default partition must
358 be scanned to verify that it does not contain any rows which properly
359 belong in the new partition. If the default partition contains a
360 large number of rows, this may be slow. The scan will be skipped if
361 the default partition is a foreign table or if it has a constraint which
362 proves that it cannot contain rows which should be placed in the new
367 A partition must have the same column names and types as the partitioned
368 table to which it belongs. If the parent is specified <literal>WITH
369 OIDS</literal> then all partitions must have OIDs; the parent's OID
370 column will be inherited by all partitions just like any other column.
371 Modifications to the column names or types of a partitioned table, or
372 the addition or removal of an OID column, will automatically propagate
373 to all partitions. <literal>CHECK</> constraints will be inherited
374 automatically by every partition, but an individual partition may specify
375 additional <literal>CHECK</> constraints; additional constraints with
376 the same name and condition as in the parent will be merged with the
377 parent constraint. Defaults may be specified separately for each
382 Rows inserted into a partitioned table will be automatically routed to
383 the correct partition. If no suitable partition exists, an error will
384 occur. Also, if updating a row in a given partition would require it
385 to move to another partition due to new partition key values, an error
390 Operations such as TRUNCATE which normally affect a table and all of its
391 inheritance children will cascade to all partitions, but may also be
392 performed on an individual partition. Note that dropping a partition
393 with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS
394 EXCLUSIVE</literal> lock on the parent table.
400 <term><replaceable class="PARAMETER">column_name</replaceable></term>
403 The name of a column to be created in the new table.
409 <term><replaceable class="PARAMETER">data_type</replaceable></term>
412 The data type of the column. This can include array
413 specifiers. For more information on the data types supported by
414 <productname>PostgreSQL</productname>, refer to <xref
421 <term><literal>COLLATE <replaceable>collation</replaceable></literal></term>
424 The <literal>COLLATE</> clause assigns a collation to
425 the column (which must be of a collatable data type).
426 If not specified, the column data type's default collation is used.
432 <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
435 The optional <literal>INHERITS</> clause specifies a list of
436 tables from which the new table automatically inherits all
437 columns. Parent tables can be plain tables or foreign tables.
441 Use of <literal>INHERITS</> creates a persistent relationship
442 between the new child table and its parent table(s). Schema
443 modifications to the parent(s) normally propagate to children
444 as well, and by default the data of the child table is included in
445 scans of the parent(s).
449 If the same column name exists in more than one parent
450 table, an error is reported unless the data types of the columns
451 match in each of the parent tables. If there is no conflict,
452 then the duplicate columns are merged to form a single column in
453 the new table. If the column name list of the new table
454 contains a column name that is also inherited, the data type must
455 likewise match the inherited column(s), and the column
456 definitions are merged into one. If the
457 new table explicitly specifies a default value for the column,
458 this default overrides any defaults from inherited declarations
459 of the column. Otherwise, any parents that specify default
460 values for the column must all specify the same default, or an
461 error will be reported.
465 <literal>CHECK</> constraints are merged in essentially the same way as
466 columns: if multiple parent tables and/or the new table definition
467 contain identically-named <literal>CHECK</> constraints, these
468 constraints must all have the same check expression, or an error will be
469 reported. Constraints having the same name and expression will
470 be merged into one copy. A constraint marked <literal>NO INHERIT</> in a
471 parent will not be considered. Notice that an unnamed <literal>CHECK</>
472 constraint in the new table will never be merged, since a unique name
473 will always be chosen for it.
477 Column <literal>STORAGE</> settings are also copied from parent tables.
481 If a column in the parent table is an identity column, that property is
482 not inherited. A column in the child table can be declared identity
489 <term><literal>PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
492 The optional <literal>PARTITION BY</literal> clause specifies a strategy
493 of partitioning the table. The table thus created is called a
494 <firstterm>partitioned</firstterm> table. The parenthesized list of
495 columns or expressions forms the <firstterm>partition key</firstterm>
496 for the table. When using range partitioning, the partition key can
497 include multiple columns or expressions (up to 32, but this limit can be
498 altered when building <productname>PostgreSQL</productname>), but for
499 list partitioning, the partition key must consist of a single column or
500 expression. If no B-tree operator class is specified when creating a
501 partitioned table, the default B-tree operator class for the datatype will
502 be used. If there is none, an error will be reported.
506 A partitioned table is divided into sub-tables (called partitions),
507 which are created using separate <literal>CREATE TABLE</> commands.
508 The partitioned table is itself empty. A data row inserted into the
509 table is routed to a partition based on the value of columns or
510 expressions in the partition key. If no existing partition matches
511 the values in the new row, an error will be reported.
515 Partitioned tables do not support <literal>UNIQUE</literal>,
516 <literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or
517 <literal>FOREIGN KEY</literal> constraints; however, you can define
518 these constraints on individual partitions.
525 <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
528 The <literal>LIKE</literal> clause specifies a table from which
529 the new table automatically copies all column names, their data types,
530 and their not-null constraints.
533 Unlike <literal>INHERITS</literal>, the new table and original table
534 are completely decoupled after creation is complete. Changes to the
535 original table will not be applied to the new table, and it is not
536 possible to include data of the new table in scans of the original
540 Default expressions for the copied column definitions will be copied
541 only if <literal>INCLUDING DEFAULTS</literal> is specified. The
542 default behavior is to exclude default expressions, resulting in the
543 copied columns in the new table having null defaults.
544 Note that copying defaults that call database-modification functions,
545 such as <function>nextval</>, may create a functional linkage between
546 the original and new tables.
549 Any identity specifications of copied column definitions will only be
550 copied if <literal>INCLUDING IDENTITY</literal> is specified. A new
551 sequence is created for each identity column of the new table, separate
552 from the sequences associated with the old table.
555 Not-null constraints are always copied to the new table.
556 <literal>CHECK</literal> constraints will be copied only if
557 <literal>INCLUDING CONSTRAINTS</literal> is specified.
558 No distinction is made between column constraints and table
562 Indexes, <literal>PRIMARY KEY</>, <literal>UNIQUE</>,
563 and <literal>EXCLUDE</> constraints on the original table will be
564 created on the new table only if <literal>INCLUDING INDEXES</literal>
565 is specified. Names for the new indexes and constraints are
566 chosen according to the default rules, regardless of how the originals
567 were named. (This behavior avoids possible duplicate-name failures for
571 <literal>STORAGE</> settings for the copied column definitions will be
572 copied only if <literal>INCLUDING STORAGE</literal> is specified. The
573 default behavior is to exclude <literal>STORAGE</> settings, resulting
574 in the copied columns in the new table having type-specific default
575 settings. For more on <literal>STORAGE</> settings, see
576 <xref linkend="storage-toast">.
579 Comments for the copied columns, constraints, and indexes
580 will be copied only if <literal>INCLUDING COMMENTS</literal>
581 is specified. The default behavior is to exclude comments, resulting in
582 the copied columns and constraints in the new table having no comments.
585 <literal>INCLUDING ALL</literal> is an abbreviated form of
586 <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.
589 Note that unlike <literal>INHERITS</literal>, columns and
590 constraints copied by <literal>LIKE</> are not merged with similarly
591 named columns and constraints.
592 If the same name is specified explicitly or in another
593 <literal>LIKE</literal> clause, an error is signaled.
596 The <literal>LIKE</literal> clause can also be used to copy column
597 definitions from views, foreign tables, or composite types.
598 Inapplicable options (e.g., <literal>INCLUDING INDEXES</literal> from
605 <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
608 An optional name for a column or table constraint. If the
609 constraint is violated, the constraint name is present in error messages,
610 so constraint names like <literal>col must be positive</> can be used
611 to communicate helpful constraint information to client applications.
612 (Double-quotes are needed to specify constraint names that contain spaces.)
613 If a constraint name is not specified, the system generates a name.
619 <term><literal>NOT NULL</></term>
622 The column is not allowed to contain null values.
628 <term><literal>NULL</></term>
631 The column is allowed to contain null values. This is the default.
635 This clause is only provided for compatibility with
636 non-standard SQL databases. Its use is discouraged in new
643 <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] </literal></term>
646 The <literal>CHECK</> clause specifies an expression producing a
647 Boolean result which new or updated rows must satisfy for an
648 insert or update operation to succeed. Expressions evaluating
649 to TRUE or UNKNOWN succeed. Should any row of an insert or
650 update operation produce a FALSE result, an error exception is
651 raised and the insert or update does not alter the database. A
652 check constraint specified as a column constraint should
653 reference that column's value only, while an expression
654 appearing in a table constraint can reference multiple columns.
658 Currently, <literal>CHECK</literal> expressions cannot contain
659 subqueries nor refer to variables other than columns of the
660 current row. The system column <literal>tableoid</literal>
661 may be referenced, but not any other system column.
665 A constraint marked with <literal>NO INHERIT</> will not propagate to
670 When a table has multiple <literal>CHECK</literal> constraints,
671 they will be tested for each row in alphabetical order by name,
672 after checking <literal>NOT NULL</> constraints.
673 (<productname>PostgreSQL</> versions before 9.5 did not honor any
674 particular firing order for <literal>CHECK</literal> constraints.)
680 <term><literal>DEFAULT
681 <replaceable>default_expr</replaceable></literal></term>
684 The <literal>DEFAULT</> clause assigns a default data value for
685 the column whose column definition it appears within. The value
686 is any variable-free expression (subqueries and cross-references
687 to other columns in the current table are not allowed). The
688 data type of the default expression must match the data type of the
693 The default expression will be used in any insert operation that
694 does not specify a value for the column. If there is no default
695 for a column, then the default is null.
701 <term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
704 This clause creates the column as an <firstterm>identity
705 column</firstterm>. It will have an implicit sequence attached to it
706 and the column in new rows will automatically have values from the
707 sequence assigned to it.
711 The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
712 determine how the sequence value is given precedence over a
713 user-specified value in an <command>INSERT</command> statement.
714 If <literal>ALWAYS</literal> is specified, a user-specified value is
715 only accepted if the <command>INSERT</command> statement
716 specifies <literal>OVERRIDING SYSTEM VALUE</literal>. If <literal>BY
717 DEFAULT</literal> is specified, then the user-specified value takes
718 precedence. See <xref linkend="sql-insert"> for details. (In
719 the <command>COPY</command> command, user-specified values are always
720 used regardless of this setting.)
724 The optional <replaceable>sequence_options</replaceable> clause can be
725 used to override the options of the sequence.
726 See <xref linkend="sql-createsequence"> for details.
732 <term><literal>UNIQUE</> (column constraint)</term>
733 <term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
737 The <literal>UNIQUE</literal> constraint specifies that a
738 group of one or more columns of a table can contain
739 only unique values. The behavior of the unique table constraint
740 is the same as that for column constraints, with the additional
741 capability to span multiple columns.
745 For the purpose of a unique constraint, null values are not
750 Each unique table constraint must name a set of columns that is
751 different from the set of columns named by any other unique or
752 primary key constraint defined for the table. (Otherwise it
753 would just be the same constraint listed twice.)
759 <term><literal>PRIMARY KEY</> (column constraint)</term>
760 <term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
763 The <literal>PRIMARY KEY</> constraint specifies that a column or
764 columns of a table can contain only unique (non-duplicate), nonnull
765 values. Only one primary key can be specified for a table, whether as a
766 column constraint or a table constraint.
770 The primary key constraint should name a set of columns that is
771 different from the set of columns named by any unique
772 constraint defined for the same table. (Otherwise, the unique
773 constraint is redundant and will be discarded.)
777 <literal>PRIMARY KEY</literal> enforces the same data constraints as
778 a combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but
779 identifying a set of columns as the primary key also provides metadata
780 about the design of the schema, since a primary key implies that other
781 tables can rely on this set of columns as a unique identifier for rows.
786 <varlistentry id="SQL-CREATETABLE-EXCLUDE">
787 <term><literal>EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ]</literal></term>
790 The <literal>EXCLUDE</> clause defines an exclusion
791 constraint, which guarantees that if
792 any two rows are compared on the specified column(s) or
793 expression(s) using the specified operator(s), not all of these
794 comparisons will return <literal>TRUE</>. If all of the
795 specified operators test for equality, this is equivalent to a
796 <literal>UNIQUE</> constraint, although an ordinary unique constraint
797 will be faster. However, exclusion constraints can specify
798 constraints that are more general than simple equality.
799 For example, you can specify a constraint that
800 no two rows in the table contain overlapping circles
801 (see <xref linkend="datatype-geometric">) by using the
802 <literal>&&</> operator.
806 Exclusion constraints are implemented using
807 an index, so each specified operator must be associated with an
808 appropriate operator class
809 (see <xref linkend="indexes-opclass">) for the index access
810 method <replaceable>index_method</>.
811 The operators are required to be commutative.
812 Each <replaceable class="parameter">exclude_element</replaceable>
813 can optionally specify an operator class and/or ordering options;
814 these are described fully under
815 <xref linkend="sql-createindex">.
819 The access method must support <literal>amgettuple</> (see <xref
820 linkend="indexam">); at present this means <acronym>GIN</>
821 cannot be used. Although it's allowed, there is little point in using
822 B-tree or hash indexes with an exclusion constraint, because this
823 does nothing that an ordinary unique constraint doesn't do better.
824 So in practice the access method will always be <acronym>GiST</> or
829 The <replaceable class="parameter">predicate</> allows you to specify an
830 exclusion constraint on a subset of the table; internally this creates a
831 partial index. Note that parentheses are required around the predicate.
837 <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term>
839 <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )
840 REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
841 [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
842 [ ON DELETE <replaceable class="parameter">action</replaceable> ]
843 [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal>
844 (table constraint)</term>
848 These clauses specify a foreign key constraint, which requires
849 that a group of one or more columns of the new table must only
850 contain values that match values in the referenced
851 column(s) of some row of the referenced table. If the <replaceable
852 class="parameter">refcolumn</replaceable> list is omitted, the
853 primary key of the <replaceable class="parameter">reftable</replaceable>
854 is used. The referenced columns must be the columns of a non-deferrable
855 unique or primary key constraint in the referenced table. The user
856 must have <literal>REFERENCES</> permission on the referenced table
857 (either the whole table, or the specific referenced columns).
858 Note that foreign key constraints cannot be defined between temporary
859 tables and permanent tables.
863 A value inserted into the referencing column(s) is matched against the
864 values of the referenced table and referenced columns using the
865 given match type. There are three match types: <literal>MATCH
866 FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
867 SIMPLE</literal> (which is the default). <literal>MATCH
868 FULL</> will not allow one column of a multicolumn foreign key
869 to be null unless all foreign key columns are null; if they are all
870 null, the row is not required to have a match in the referenced table.
871 <literal>MATCH SIMPLE</literal> allows any of the foreign key columns
872 to be null; if any of them are null, the row is not required to have a
873 match in the referenced table.
874 <literal>MATCH PARTIAL</> is not yet implemented.
875 (Of course, <literal>NOT NULL</> constraints can be applied to the
876 referencing column(s) to prevent these cases from arising.)
880 In addition, when the data in the referenced columns is changed,
881 certain actions are performed on the data in this table's
882 columns. The <literal>ON DELETE</literal> clause specifies the
883 action to perform when a referenced row in the referenced table is
884 being deleted. Likewise, the <literal>ON UPDATE</literal>
885 clause specifies the action to perform when a referenced column
886 in the referenced table is being updated to a new value. If the
887 row is updated, but the referenced column is not actually
888 changed, no action is done. Referential actions other than the
889 <literal>NO ACTION</literal> check cannot be deferred, even if
890 the constraint is declared deferrable. There are the following possible
891 actions for each clause:
895 <term><literal>NO ACTION</literal></term>
898 Produce an error indicating that the deletion or update
899 would create a foreign key constraint violation.
900 If the constraint is deferred, this
901 error will be produced at constraint check time if there still
902 exist any referencing rows. This is the default action.
908 <term><literal>RESTRICT</literal></term>
911 Produce an error indicating that the deletion or update
912 would create a foreign key constraint violation.
913 This is the same as <literal>NO ACTION</literal> except that
914 the check is not deferrable.
920 <term><literal>CASCADE</literal></term>
923 Delete any rows referencing the deleted row, or update the
924 values of the referencing column(s) to the new values of the
925 referenced columns, respectively.
931 <term><literal>SET NULL</literal></term>
934 Set the referencing column(s) to null.
940 <term><literal>SET DEFAULT</literal></term>
943 Set the referencing column(s) to their default values.
944 (There must be a row in the referenced table matching the default
945 values, if they are not null, or the operation will fail.)
953 If the referenced column(s) are changed frequently, it might be wise to
954 add an index to the referencing column(s) so that referential actions
955 associated with the foreign key constraint can be performed more
962 <term><literal>DEFERRABLE</literal></term>
963 <term><literal>NOT DEFERRABLE</literal></term>
966 This controls whether the constraint can be deferred. A
967 constraint that is not deferrable will be checked immediately
968 after every command. Checking of constraints that are
969 deferrable can be postponed until the end of the transaction
970 (using the <xref linkend="sql-set-constraints"> command).
971 <literal>NOT DEFERRABLE</literal> is the default.
972 Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>,
973 <literal>EXCLUDE</>, and
974 <literal>REFERENCES</> (foreign key) constraints accept this
975 clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not
976 deferrable. Note that deferrable constraints cannot be used as
977 conflict arbitrators in an <command>INSERT</command> statement that
978 includes an <literal>ON CONFLICT DO UPDATE</> clause.
984 <term><literal>INITIALLY IMMEDIATE</literal></term>
985 <term><literal>INITIALLY DEFERRED</literal></term>
988 If a constraint is deferrable, this clause specifies the default
989 time to check the constraint. If the constraint is
990 <literal>INITIALLY IMMEDIATE</literal>, it is checked after each
991 statement. This is the default. If the constraint is
992 <literal>INITIALLY DEFERRED</literal>, it is checked only at the
993 end of the transaction. The constraint check time can be
994 altered with the <xref linkend="sql-set-constraints"> command.
1000 <term><literal>WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )</literal></term>
1003 This clause specifies optional storage parameters for a table or index;
1004 see <xref linkend="sql-createtable-storage-parameters"
1005 endterm="sql-createtable-storage-parameters-title"> for more
1006 information. The <literal>WITH</> clause for a
1007 table can also include <literal>OIDS=TRUE</> (or just <literal>OIDS</>)
1008 to specify that rows of the new table
1009 should have OIDs (object identifiers) assigned to them, or
1010 <literal>OIDS=FALSE</> to specify that the rows should not have OIDs.
1011 If <literal>OIDS</> is not specified, the default setting depends upon
1012 the <xref linkend="guc-default-with-oids"> configuration parameter.
1013 (If the new table inherits from any tables that have OIDs, then
1014 <literal>OIDS=TRUE</> is forced even if the command says
1015 <literal>OIDS=FALSE</>.)
1019 If <literal>OIDS=FALSE</literal> is specified or implied, the new
1020 table does not store OIDs and no OID will be assigned for a row inserted
1021 into it. This is generally considered worthwhile, since it
1022 will reduce OID consumption and thereby postpone the wraparound
1023 of the 32-bit OID counter. Once the counter wraps around, OIDs
1024 can no longer be assumed to be unique, which makes them
1025 considerably less useful. In addition, excluding OIDs from a
1026 table reduces the space required to store the table on disk by
1027 4 bytes per row (on most machines), slightly improving performance.
1031 To remove OIDs from a table after it has been created, use <xref
1032 linkend="sql-altertable">.
1038 <term><literal>WITH OIDS</></term>
1039 <term><literal>WITHOUT OIDS</></term>
1042 These are obsolescent syntaxes equivalent to <literal>WITH (OIDS)</>
1043 and <literal>WITH (OIDS=FALSE)</>, respectively. If you wish to give
1044 both an <literal>OIDS</> setting and storage parameters, you must use
1045 the <literal>WITH ( ... )</> syntax; see above.
1051 <term><literal>ON COMMIT</literal></term>
1054 The behavior of temporary tables at the end of a transaction
1055 block can be controlled using <literal>ON COMMIT</literal>.
1056 The three options are:
1060 <term><literal>PRESERVE ROWS</literal></term>
1063 No special action is taken at the ends of transactions.
1064 This is the default behavior.
1070 <term><literal>DELETE ROWS</literal></term>
1073 All rows in the temporary table will be deleted at the end
1074 of each transaction block. Essentially, an automatic <xref
1075 linkend="sql-truncate"> is done
1082 <term><literal>DROP</literal></term>
1085 The temporary table will be dropped at the end of the current
1090 </variablelist></para>
1095 <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable></literal></term>
1098 The <replaceable class="PARAMETER">tablespace_name</replaceable> is the name
1099 of the tablespace in which the new table is to be created.
1101 <xref linkend="guc-default-tablespace"> is consulted, or
1102 <xref linkend="guc-temp-tablespaces"> if the table is temporary.
1108 <term><literal>USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable></literal></term>
1111 This clause allows selection of the tablespace in which the index
1112 associated with a <literal>UNIQUE</literal>, <literal>PRIMARY
1113 KEY</literal>, or <literal>EXCLUDE</> constraint will be created.
1115 <xref linkend="guc-default-tablespace"> is consulted, or
1116 <xref linkend="guc-temp-tablespaces"> if the table is temporary.
1123 <refsect2 id="SQL-CREATETABLE-storage-parameters">
1124 <title id="SQL-CREATETABLE-storage-parameters-title">Storage Parameters</title>
1126 <indexterm zone="sql-createtable-storage-parameters">
1127 <primary>storage parameters</primary>
1131 The <literal>WITH</> clause can specify <firstterm>storage parameters</>
1132 for tables, and for indexes associated with a <literal>UNIQUE</literal>,
1133 <literal>PRIMARY KEY</literal>, or <literal>EXCLUDE</> constraint.
1134 Storage parameters for
1135 indexes are documented in <xref linkend="SQL-CREATEINDEX">.
1136 The storage parameters currently
1137 available for tables are listed below. For many of these parameters, as
1138 shown, there is an additional parameter with the same name prefixed with
1139 <literal>toast.</literal>, which controls the behavior of the
1140 table's secondary <acronym>TOAST</> table, if any
1141 (see <xref linkend="storage-toast"> for more information about TOAST).
1142 If a table parameter value is set and the
1143 equivalent <literal>toast.</literal> parameter is not, the TOAST table
1144 will use the table's parameter value.
1145 Specifying these parameters for partitioned tables is not supported,
1146 but you may specify them for individual leaf partitions.
1152 <term><literal>fillfactor</> (<type>integer</>)</term>
1155 The fillfactor for a table is a percentage between 10 and 100.
1156 100 (complete packing) is the default. When a smaller fillfactor
1157 is specified, <command>INSERT</> operations pack table pages only
1158 to the indicated percentage; the remaining space on each page is
1159 reserved for updating rows on that page. This gives <command>UPDATE</>
1160 a chance to place the updated copy of a row on the same page as the
1161 original, which is more efficient than placing it on a different page.
1162 For a table whose entries are never updated, complete packing is the
1163 best choice, but in heavily updated tables smaller fillfactors are
1164 appropriate. This parameter cannot be set for TOAST tables.
1170 <term><literal>parallel_workers</> (<type>integer</>)</term>
1173 This sets the number of workers that should be used to assist a parallel
1174 scan of this table. If not set, the system will determine a value based
1175 on the relation size. The actual number of workers chosen by the planner
1176 may be less, for example due to
1177 the setting of <xref linkend="guc-max-worker-processes">.
1183 <term><literal>autovacuum_enabled</>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</>)</term>
1186 Enables or disables the autovacuum daemon for a particular table.
1187 If true, the autovacuum daemon will perform automatic <command>VACUUM</>
1188 and/or <command>ANALYZE</> operations on this table following the rules
1189 discussed in <xref linkend="autovacuum">.
1190 If false, this table will not be autovacuumed, except to prevent
1191 transaction ID wraparound. See <xref linkend="vacuum-for-wraparound"> for
1192 more about wraparound prevention.
1193 Note that the autovacuum daemon does not run at all (except to prevent
1194 transaction ID wraparound) if the <xref linkend="guc-autovacuum">
1195 parameter is false; setting individual tables' storage parameters does
1196 not override that. Therefore there is seldom much point in explicitly
1197 setting this storage parameter to <literal>true</>, only
1198 to <literal>false</>.
1204 <term><literal>autovacuum_vacuum_threshold</>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</>)</term>
1207 Per-table value for <xref linkend="guc-autovacuum-vacuum-threshold">
1214 <term><literal>autovacuum_vacuum_scale_factor</>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>float4</>)</term>
1217 Per-table value for <xref linkend="guc-autovacuum-vacuum-scale-factor">
1224 <term><literal>autovacuum_analyze_threshold</> (<type>integer</>)</term>
1227 Per-table value for <xref linkend="guc-autovacuum-analyze-threshold">
1234 <term><literal>autovacuum_analyze_scale_factor</> (<type>float4</>)</term>
1237 Per-table value for <xref linkend="guc-autovacuum-analyze-scale-factor">
1244 <term><literal>autovacuum_vacuum_cost_delay</>, <literal>toast.autovacuum_vacuum_cost_delay</literal> (<type>integer</>)</term>
1247 Per-table value for <xref linkend="guc-autovacuum-vacuum-cost-delay">
1254 <term><literal>autovacuum_vacuum_cost_limit</>, <literal>toast.autovacuum_vacuum_cost_limit</literal> (<type>integer</>)</term>
1257 Per-table value for <xref linkend="guc-autovacuum-vacuum-cost-limit">
1264 <term><literal>autovacuum_freeze_min_age</>, <literal>toast.autovacuum_freeze_min_age</literal> (<type>integer</>)</term>
1267 Per-table value for <xref linkend="guc-vacuum-freeze-min-age">
1268 parameter. Note that autovacuum will ignore
1269 per-table <literal>autovacuum_freeze_min_age</> parameters that are
1270 larger than half the
1271 system-wide <xref linkend="guc-autovacuum-freeze-max-age"> setting.
1277 <term><literal>autovacuum_freeze_max_age</>, <literal>toast.autovacuum_freeze_max_age</literal> (<type>integer</>)</term>
1280 Per-table value for <xref linkend="guc-autovacuum-freeze-max-age">
1281 parameter. Note that autovacuum will ignore
1282 per-table <literal>autovacuum_freeze_max_age</> parameters that are
1283 larger than the system-wide setting (it can only be set smaller).
1289 <term><literal>autovacuum_freeze_table_age</literal>, <literal>toast.autovacuum_freeze_table_age</literal> (<type>integer</type>)</term>
1292 Per-table value for <xref linkend="guc-vacuum-freeze-table-age">
1299 <term><literal>autovacuum_multixact_freeze_min_age</literal>, <literal>toast.autovacuum_multixact_freeze_min_age</literal> (<type>integer</type>)</term>
1302 Per-table value for <xref linkend="guc-vacuum-multixact-freeze-min-age">
1303 parameter. Note that autovacuum will ignore
1304 per-table <literal>autovacuum_multixact_freeze_min_age</> parameters
1305 that are larger than half the
1306 system-wide <xref linkend="guc-autovacuum-multixact-freeze-max-age">
1313 <term><literal>autovacuum_multixact_freeze_max_age</literal>, <literal>toast.autovacuum_multixact_freeze_max_age</literal> (<type>integer</type>)</term>
1317 for <xref linkend="guc-autovacuum-multixact-freeze-max-age"> parameter.
1318 Note that autovacuum will ignore
1319 per-table <literal>autovacuum_multixact_freeze_max_age</> parameters
1320 that are larger than the system-wide setting (it can only be set
1327 <term><literal>autovacuum_multixact_freeze_table_age</literal>, <literal>toast.autovacuum_multixact_freeze_table_age</literal> (<type>integer</type>)</term>
1331 for <xref linkend="guc-vacuum-multixact-freeze-table-age"> parameter.
1337 <term><literal>log_autovacuum_min_duration</literal>, <literal>toast.log_autovacuum_min_duration</literal> (<type>integer</type>)</term>
1340 Per-table value for <xref linkend="guc-log-autovacuum-min-duration">
1347 <term><literal>user_catalog_table</literal> (<type>boolean</type>)</term>
1350 Declare the table as an additional catalog table for purposes of
1351 logical replication. See
1352 <xref linkend="logicaldecoding-capabilities"> for details.
1353 This parameter cannot be set for TOAST tables.
1363 <refsect1 id="SQL-CREATETABLE-notes">
1364 <title>Notes</title>
1367 Using OIDs in new applications is not recommended: where
1368 possible, using an identity column or other sequence
1369 generator as the table's primary key is preferred. However, if
1370 your application does make use of OIDs to identify specific
1371 rows of a table, it is recommended to create a unique constraint
1372 on the <structfield>oid</> column of that table, to ensure that
1373 OIDs in the table will indeed uniquely identify rows even after
1374 counter wraparound. Avoid assuming that OIDs are unique across
1375 tables; if you need a database-wide unique identifier, use the
1376 combination of <structfield>tableoid</> and row OID for the
1382 The use of <literal>OIDS=FALSE</literal> is not recommended
1383 for tables with no primary key, since without either an OID or a
1384 unique data key, it is difficult to identify specific rows.
1389 <productname>PostgreSQL</productname> automatically creates an
1390 index for each unique constraint and primary key constraint to
1391 enforce uniqueness. Thus, it is not necessary to create an
1392 index explicitly for primary key columns. (See <xref
1393 linkend="sql-createindex"> for more information.)
1397 Unique constraints and primary keys are not inherited in the
1398 current implementation. This makes the combination of
1399 inheritance and unique constraints rather dysfunctional.
1403 A table cannot have more than 1600 columns. (In practice, the
1404 effective limit is usually lower because of tuple-length constraints.)
1410 <refsect1 id="SQL-CREATETABLE-examples">
1411 <title>Examples</title>
1414 Create table <structname>films</> and table
1415 <structname>distributors</>:
1418 CREATE TABLE films (
1419 code char(5) CONSTRAINT firstkey PRIMARY KEY,
1420 title varchar(40) NOT NULL,
1421 did integer NOT NULL,
1424 len interval hour to minute
1427 CREATE TABLE distributors (
1428 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1429 name varchar(40) NOT NULL CHECK (name <> '')
1435 Create a table with a 2-dimensional array:
1438 CREATE TABLE array_int (
1445 Define a unique table constraint for the table
1446 <literal>films</literal>. Unique table constraints can be defined
1447 on one or more columns of the table:
1450 CREATE TABLE films (
1456 len interval hour to minute,
1457 CONSTRAINT production UNIQUE(date_prod)
1463 Define a check column constraint:
1466 CREATE TABLE distributors (
1467 did integer CHECK (did > 100),
1474 Define a check table constraint:
1477 CREATE TABLE distributors (
1480 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
1486 Define a primary key table constraint for the table
1487 <structname>films</>:
1490 CREATE TABLE films (
1496 len interval hour to minute,
1497 CONSTRAINT code_title PRIMARY KEY(code,title)
1503 Define a primary key constraint for table
1504 <structname>distributors</>. The following two examples are
1505 equivalent, the first using the table constraint syntax, the second
1506 the column constraint syntax:
1509 CREATE TABLE distributors (
1515 CREATE TABLE distributors (
1516 did integer PRIMARY KEY,
1523 Assign a literal constant default value for the column
1524 <literal>name</literal>, arrange for the default value of column
1525 <literal>did</literal> to be generated by selecting the next value
1526 of a sequence object, and make the default value of
1527 <literal>modtime</literal> be the time at which the row is
1531 CREATE TABLE distributors (
1532 name varchar(40) DEFAULT 'Luso Films',
1533 did integer DEFAULT nextval('distributors_serial'),
1534 modtime timestamp DEFAULT current_timestamp
1540 Define two <literal>NOT NULL</> column constraints on the table
1541 <classname>distributors</classname>, one of which is explicitly
1545 CREATE TABLE distributors (
1546 did integer CONSTRAINT no_null NOT NULL,
1547 name varchar(40) NOT NULL
1553 Define a unique constraint for the <literal>name</literal> column:
1556 CREATE TABLE distributors (
1558 name varchar(40) UNIQUE
1562 The same, specified as a table constraint:
1565 CREATE TABLE distributors (
1574 Create the same table, specifying 70% fill factor for both the table
1575 and its unique index:
1578 CREATE TABLE distributors (
1581 UNIQUE(name) WITH (fillfactor=70)
1583 WITH (fillfactor=70);
1588 Create table <structname>circles</> with an exclusion
1589 constraint that prevents any two circles from overlapping:
1592 CREATE TABLE circles (
1594 EXCLUDE USING gist (c WITH &&)
1600 Create table <structname>cinemas</> in tablespace <structname>diskvol1</>:
1603 CREATE TABLE cinemas (
1607 ) TABLESPACE diskvol1;
1612 Create a composite type and a typed table:
1614 CREATE TYPE employee_type AS (name text, salary numeric);
1616 CREATE TABLE employees OF employee_type (
1618 salary WITH OPTIONS DEFAULT 1000
1620 </programlisting></para>
1623 Create a range partitioned table:
1625 CREATE TABLE measurement (
1626 logdate date not null,
1629 ) PARTITION BY RANGE (logdate);
1630 </programlisting></para>
1633 Create a range partitioned table with multiple columns in the partition key:
1635 CREATE TABLE measurement_year_month (
1636 logdate date not null,
1639 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1640 </programlisting></para>
1643 Create a list partitioned table:
1645 CREATE TABLE cities (
1646 city_id bigserial not null,
1649 ) PARTITION BY LIST (left(lower(name), 1));
1650 </programlisting></para>
1653 Create partition of a range partitioned table:
1655 CREATE TABLE measurement_y2016m07
1656 PARTITION OF measurement (
1658 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1659 </programlisting></para>
1662 Create a few partitions of a range partitioned table with multiple
1663 columns in the partition key:
1665 CREATE TABLE measurement_ym_older
1666 PARTITION OF measurement_year_month
1667 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1669 CREATE TABLE measurement_ym_y2016m11
1670 PARTITION OF measurement_year_month
1671 FOR VALUES FROM (2016, 11) TO (2016, 12);
1673 CREATE TABLE measurement_ym_y2016m12
1674 PARTITION OF measurement_year_month
1675 FOR VALUES FROM (2016, 12) TO (2017, 01);
1677 CREATE TABLE measurement_ym_y2017m01
1678 PARTITION OF measurement_year_month
1679 FOR VALUES FROM (2017, 01) TO (2017, 02);
1680 </programlisting></para>
1683 Create partition of a list partitioned table:
1685 CREATE TABLE cities_ab
1686 PARTITION OF cities (
1687 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1688 ) FOR VALUES IN ('a', 'b');
1689 </programlisting></para>
1692 Create partition of a list partitioned table that is itself further
1693 partitioned and then add a partition to it:
1695 CREATE TABLE cities_ab
1696 PARTITION OF cities (
1697 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1698 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1700 CREATE TABLE cities_ab_10000_to_100000
1701 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1702 </programlisting></para>
1705 Create a default partition:
1707 CREATE TABLE cities_partdef
1708 PARTITION OF cities DEFAULT;
1709 </programlisting></para>
1712 <refsect1 id="SQL-CREATETABLE-compatibility">
1713 <title id="SQL-CREATETABLE-compatibility-title">Compatibility</title>
1716 The <command>CREATE TABLE</command> command conforms to the
1717 <acronym>SQL</acronym> standard, with exceptions listed below.
1721 <title>Temporary Tables</title>
1724 Although the syntax of <literal>CREATE TEMPORARY TABLE</literal>
1725 resembles that of the SQL standard, the effect is not the same. In the
1727 temporary tables are defined just once and automatically exist (starting
1728 with empty contents) in every session that needs them.
1729 <productname>PostgreSQL</productname> instead
1730 requires each session to issue its own <literal>CREATE TEMPORARY
1731 TABLE</literal> command for each temporary table to be used. This allows
1732 different sessions to use the same temporary table name for different
1733 purposes, whereas the standard's approach constrains all instances of a
1734 given temporary table name to have the same table structure.
1738 The standard's definition of the behavior of temporary tables is
1739 widely ignored. <productname>PostgreSQL</productname>'s behavior
1740 on this point is similar to that of several other SQL databases.
1744 The SQL standard also distinguishes between global and local temporary
1745 tables, where a local temporary table has a separate set of contents for
1746 each SQL module within each session, though its definition is still shared
1747 across sessions. Since <productname>PostgreSQL</productname> does not
1748 support SQL modules, this distinction is not relevant in
1749 <productname>PostgreSQL</productname>.
1753 For compatibility's sake, <productname>PostgreSQL</productname> will
1754 accept the <literal>GLOBAL</literal> and <literal>LOCAL</literal> keywords
1755 in a temporary table declaration, but they currently have no effect.
1756 Use of these keywords is discouraged, since future versions of
1757 <productname>PostgreSQL</productname> might adopt a more
1758 standard-compliant interpretation of their meaning.
1762 The <literal>ON COMMIT</literal> clause for temporary tables
1763 also resembles the SQL standard, but has some differences.
1764 If the <literal>ON COMMIT</> clause is omitted, SQL specifies that the
1765 default behavior is <literal>ON COMMIT DELETE ROWS</>. However, the
1766 default behavior in <productname>PostgreSQL</productname> is
1767 <literal>ON COMMIT PRESERVE ROWS</literal>. The <literal>ON COMMIT
1768 DROP</literal> option does not exist in SQL.
1773 <title>Non-deferred Uniqueness Constraints</title>
1776 When a <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint is
1777 not deferrable, <productname>PostgreSQL</productname> checks for
1778 uniqueness immediately whenever a row is inserted or modified.
1779 The SQL standard says that uniqueness should be enforced only at
1780 the end of the statement; this makes a difference when, for example,
1781 a single command updates multiple key values. To obtain
1782 standard-compliant behavior, declare the constraint as
1783 <literal>DEFERRABLE</> but not deferred (i.e., <literal>INITIALLY
1784 IMMEDIATE</>). Be aware that this can be significantly slower than
1785 immediate uniqueness checking.
1790 <title>Column Check Constraints</title>
1793 The SQL standard says that <literal>CHECK</> column constraints
1794 can only refer to the column they apply to; only <literal>CHECK</>
1795 table constraints can refer to multiple columns.
1796 <productname>PostgreSQL</productname> does not enforce this
1797 restriction; it treats column and table check constraints alike.
1802 <title><literal>EXCLUDE</literal> Constraint</title>
1805 The <literal>EXCLUDE</> constraint type is a
1806 <productname>PostgreSQL</productname> extension.
1811 <title><literal>NULL</literal> <quote>Constraint</quote></title>
1814 The <literal>NULL</> <quote>constraint</quote> (actually a
1815 non-constraint) is a <productname>PostgreSQL</productname>
1816 extension to the SQL standard that is included for compatibility with some
1817 other database systems (and for symmetry with the <literal>NOT
1818 NULL</literal> constraint). Since it is the default for any
1819 column, its presence is simply noise.
1824 <title>Inheritance</title>
1827 Multiple inheritance via the <literal>INHERITS</literal> clause is
1828 a <productname>PostgreSQL</productname> language extension.
1829 SQL:1999 and later define single inheritance using a
1830 different syntax and different semantics. SQL:1999-style
1831 inheritance is not yet supported by
1832 <productname>PostgreSQL</productname>.
1837 <title>Zero-column Tables</title>
1840 <productname>PostgreSQL</productname> allows a table of no columns
1841 to be created (for example, <literal>CREATE TABLE foo();</>). This
1842 is an extension from the SQL standard, which does not allow zero-column
1843 tables. Zero-column tables are not in themselves very useful, but
1844 disallowing them creates odd special cases for <command>ALTER TABLE
1845 DROP COLUMN</>, so it seems cleaner to ignore this spec restriction.
1850 <title>Multiple Identity Columns</title>
1853 <productname>PostgreSQL</productname> allows a table to have more than one
1854 identity column. The standard specifies that a table can have at most one
1855 identity column. This is relaxed mainly to give more flexibility for
1856 doing schema changes or migrations. Note that
1857 the <command>INSERT</command> command supports only one override clause
1858 that applies to the entire statement, so having multiple identity columns
1859 with different behaviors is not well supported.
1864 <title><literal>LIKE</> Clause</title>
1867 While a <literal>LIKE</> clause exists in the SQL standard, many of the
1868 options that <productname>PostgreSQL</productname> accepts for it are not
1869 in the standard, and some of the standard's options are not implemented
1870 by <productname>PostgreSQL</productname>.
1875 <title><literal>WITH</> Clause</title>
1878 The <literal>WITH</> clause is a <productname>PostgreSQL</productname>
1879 extension; neither storage parameters nor OIDs are in the standard.
1884 <title>Tablespaces</title>
1887 The <productname>PostgreSQL</productname> concept of tablespaces is not
1888 part of the standard. Hence, the clauses <literal>TABLESPACE</literal>
1889 and <literal>USING INDEX TABLESPACE</literal> are extensions.
1894 <title>Typed Tables</title>
1897 Typed tables implement a subset of the SQL standard. According to
1898 the standard, a typed table has columns corresponding to the
1899 underlying composite type as well as one other column that is
1900 the <quote>self-referencing column</quote>. PostgreSQL does not
1901 support these self-referencing columns explicitly, but the same
1902 effect can be had using the OID feature.
1907 <title><literal>PARTITION BY</> Clause</title>
1910 The <literal>PARTITION BY</> clause is a
1911 <productname>PostgreSQL</productname> extension.
1916 <title><literal>PARTITION OF</> Clause</title>
1919 The <literal>PARTITION OF</> clause is a
1920 <productname>PostgreSQL</productname> extension.
1928 <title>See Also</title>
1930 <simplelist type="inline">
1931 <member><xref linkend="sql-altertable"></member>
1932 <member><xref linkend="sql-droptable"></member>
1933 <member><xref linkend="sql-createtableas"></member>
1934 <member><xref linkend="sql-createtablespace"></member>
1935 <member><xref linkend="sql-createtype"></member>