1CREATE TABLE(7)          PostgreSQL 13.4 Documentation         CREATE TABLE(7)
2
3
4

NAME

6       CREATE_TABLE - define a new table
7

SYNOPSIS

9       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
10         { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
11           | table_constraint
12           | LIKE source_table [ like_option ... ] }
13           [, ... ]
14       ] )
15       [ INHERITS ( parent_table [, ... ] ) ]
16       [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
17       [ USING method ]
18       [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
19       [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
20       [ TABLESPACE tablespace_name ]
21
22       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
23           OF type_name [ (
24         { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
25           | table_constraint }
26           [, ... ]
27       ) ]
28       [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
29       [ USING method ]
30       [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
31       [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
32       [ TABLESPACE tablespace_name ]
33
34       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
35           PARTITION OF parent_table [ (
36         { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
37           | table_constraint }
38           [, ... ]
39       ) ] { FOR VALUES partition_bound_spec | DEFAULT }
40       [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
41       [ USING method ]
42       [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
43       [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
44       [ TABLESPACE tablespace_name ]
45
46       where column_constraint is:
47
48       [ CONSTRAINT constraint_name ]
49       { NOT NULL |
50         NULL |
51         CHECK ( expression ) [ NO INHERIT ] |
52         DEFAULT default_expr |
53         GENERATED ALWAYS AS ( generation_expr ) STORED |
54         GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
55         UNIQUE index_parameters |
56         PRIMARY KEY index_parameters |
57         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
58           [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
59       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
60
61       and table_constraint is:
62
63       [ CONSTRAINT constraint_name ]
64       { CHECK ( expression ) [ NO INHERIT ] |
65         UNIQUE ( column_name [, ... ] ) index_parameters |
66         PRIMARY KEY ( column_name [, ... ] ) index_parameters |
67         EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
68         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
69           [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
70       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
71
72       and like_option is:
73
74       { INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
75
76       and partition_bound_spec is:
77
78       IN ( partition_bound_expr [, ...] ) |
79       FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
80         TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
81       WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
82
83       index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
84
85       [ INCLUDE ( column_name [, ... ] ) ]
86       [ WITH ( storage_parameter [= value] [, ... ] ) ]
87       [ USING INDEX TABLESPACE tablespace_name ]
88
89       exclude_element in an EXCLUDE constraint is:
90
91       { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
92

DESCRIPTION

94       CREATE TABLE will create a new, initially empty table in the current
95       database. The table will be owned by the user issuing the command.
96
97       If a schema name is given (for example, CREATE TABLE myschema.mytable
98       ...) then the table is created in the specified schema. Otherwise it is
99       created in the current schema. Temporary tables exist in a special
100       schema, so a schema name cannot be given when creating a temporary
101       table. The name of the table must be distinct from the name of any
102       other table, sequence, index, view, or foreign table in the same
103       schema.
104
105       CREATE TABLE also automatically creates a data type that represents the
106       composite type corresponding to one row of the table. Therefore, tables
107       cannot have the same name as any existing data type in the same schema.
108
109       The optional constraint clauses specify constraints (tests) that new or
110       updated rows must satisfy for an insert or update operation to succeed.
111       A constraint is an SQL object that helps define the set of valid values
112       in the table in various ways.
113
114       There are two ways to define constraints: table constraints and column
115       constraints. A column constraint is defined as part of a column
116       definition. A table constraint definition is not tied to a particular
117       column, and it can encompass more than one column. Every column
118       constraint can also be written as a table constraint; a column
119       constraint is only a notational convenience for use when the constraint
120       only affects one column.
121
122       To be able to create a table, you must have USAGE privilege on all
123       column types or the type in the OF clause, respectively.
124

PARAMETERS

126       TEMPORARY or TEMP
127           If specified, the table is created as a temporary table. Temporary
128           tables are automatically dropped at the end of a session, or
129           optionally at the end of the current transaction (see ON COMMIT
130           below). Existing permanent tables with the same name are not
131           visible to the current session while the temporary table exists,
132           unless they are referenced with schema-qualified names. Any indexes
133           created on a temporary table are automatically temporary as well.
134
135           The autovacuum daemon cannot access and therefore cannot vacuum or
136           analyze temporary tables. For this reason, appropriate vacuum and
137           analyze operations should be performed via session SQL commands.
138           For example, if a temporary table is going to be used in complex
139           queries, it is wise to run ANALYZE on the temporary table after it
140           is populated.
141
142           Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
143           TEMP. This presently makes no difference in PostgreSQL and is
144           deprecated; see Compatibility below.
145
146       UNLOGGED
147           If specified, the table is created as an unlogged table. Data
148           written to unlogged tables is not written to the write-ahead log
149           (see Chapter 29), which makes them considerably faster than
150           ordinary tables. However, they are not crash-safe: an unlogged
151           table is automatically truncated after a crash or unclean shutdown.
152           The contents of an unlogged table are also not replicated to
153           standby servers. Any indexes created on an unlogged table are
154           automatically unlogged as well.
155
156       IF NOT EXISTS
157           Do not throw an error if a relation with the same name already
158           exists. A notice is issued in this case. Note that there is no
159           guarantee that the existing relation is anything like the one that
160           would have been created.
161
162       table_name
163           The name (optionally schema-qualified) of the table to be created.
164
165       OF type_name
166           Creates a typed table, which takes its structure from the specified
167           composite type (name optionally schema-qualified). A typed table is
168           tied to its type; for example the table will be dropped if the type
169           is dropped (with DROP TYPE ... CASCADE).
170
171           When a typed table is created, then the data types of the columns
172           are determined by the underlying composite type and are not
173           specified by the CREATE TABLE command. But the CREATE TABLE command
174           can add defaults and constraints to the table and can specify
175           storage parameters.
176
177       column_name
178           The name of a column to be created in the new table.
179
180       data_type
181           The data type of the column. This can include array specifiers. For
182           more information on the data types supported by PostgreSQL, refer
183           to Chapter 8.
184
185       COLLATE collation
186           The COLLATE clause assigns a collation to the column (which must be
187           of a collatable data type). If not specified, the column data
188           type's default collation is used.
189
190       INHERITS ( parent_table [, ... ] )
191           The optional INHERITS clause specifies a list of tables from which
192           the new table automatically inherits all columns. Parent tables can
193           be plain tables or foreign tables.
194
195           Use of INHERITS creates a persistent relationship between the new
196           child table and its parent table(s). Schema modifications to the
197           parent(s) normally propagate to children as well, and by default
198           the data of the child table is included in scans of the parent(s).
199
200           If the same column name exists in more than one parent table, an
201           error is reported unless the data types of the columns match in
202           each of the parent tables. If there is no conflict, then the
203           duplicate columns are merged to form a single column in the new
204           table. If the column name list of the new table contains a column
205           name that is also inherited, the data type must likewise match the
206           inherited column(s), and the column definitions are merged into
207           one. If the new table explicitly specifies a default value for the
208           column, this default overrides any defaults from inherited
209           declarations of the column. Otherwise, any parents that specify
210           default values for the column must all specify the same default, or
211           an error will be reported.
212
213           CHECK constraints are merged in essentially the same way as
214           columns: if multiple parent tables and/or the new table definition
215           contain identically-named CHECK constraints, these constraints must
216           all have the same check expression, or an error will be reported.
217           Constraints having the same name and expression will be merged into
218           one copy. A constraint marked NO INHERIT in a parent will not be
219           considered. Notice that an unnamed CHECK constraint in the new
220           table will never be merged, since a unique name will always be
221           chosen for it.
222
223           Column STORAGE settings are also copied from parent tables.
224
225           If a column in the parent table is an identity column, that
226           property is not inherited. A column in the child table can be
227           declared identity column if desired.
228
229       PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
230       [ opclass ] [, ...] )
231           The optional PARTITION BY clause specifies a strategy of
232           partitioning the table. The table thus created is called a
233           partitioned table. The parenthesized list of columns or expressions
234           forms the partition key for the table. When using range or hash
235           partitioning, the partition key can include multiple columns or
236           expressions (up to 32, but this limit can be altered when building
237           PostgreSQL), but for list partitioning, the partition key must
238           consist of a single column or expression.
239
240           Range and list partitioning require a btree operator class, while
241           hash partitioning requires a hash operator class. If no operator
242           class is specified explicitly, the default operator class of the
243           appropriate type will be used; if no default operator class exists,
244           an error will be raised. When hash partitioning is used, the
245           operator class used must implement support function 2 (see
246           Section 37.16.3 for details).
247
248           A partitioned table is divided into sub-tables (called partitions),
249           which are created using separate CREATE TABLE commands. The
250           partitioned table is itself empty. A data row inserted into the
251           table is routed to a partition based on the value of columns or
252           expressions in the partition key. If no existing partition matches
253           the values in the new row, an error will be reported.
254
255           Partitioned tables do not support EXCLUDE constraints; however, you
256           can define these constraints on individual partitions.
257
258           See Section 5.11 for more discussion on table partitioning.
259
260       PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
261           Creates the table as a partition of the specified parent table. The
262           table can be created either as a partition for specific values
263           using FOR VALUES or as a default partition using DEFAULT. Any
264           indexes, constraints and user-defined row-level triggers that exist
265           in the parent table are cloned on the new partition.
266
267           The partition_bound_spec must correspond to the partitioning method
268           and partition key of the parent table, and must not overlap with
269           any existing partition of that parent. The form with IN is used for
270           list partitioning, the form with FROM and TO is used for range
271           partitioning, and the form with WITH is used for hash partitioning.
272
273           partition_bound_expr is any variable-free expression (subqueries,
274           window functions, aggregate functions, and set-returning functions
275           are not allowed). Its data type must match the data type of the
276           corresponding partition key column. The expression is evaluated
277           once at table creation time, so it can even contain volatile
278           expressions such as CURRENT_TIMESTAMP.
279
280           When creating a list partition, NULL can be specified to signify
281           that the partition allows the partition key column to be null.
282           However, there cannot be more than one such list partition for a
283           given parent table.  NULL cannot be specified for range partitions.
284
285           When creating a range partition, the lower bound specified with
286           FROM is an inclusive bound, whereas the upper bound specified with
287           TO is an exclusive bound. That is, the values specified in the FROM
288           list are valid values of the corresponding partition key columns
289           for this partition, whereas those in the TO list are not. Note that
290           this statement must be understood according to the rules of
291           row-wise comparison (Section 9.24.5). For example, given PARTITION
292           BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1
293           with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
294
295           The special values MINVALUE and MAXVALUE may be used when creating
296           a range partition to indicate that there is no lower or upper bound
297           on the column's value. For example, a partition defined using FROM
298           (MINVALUE) TO (10) allows any values less than 10, and a partition
299           defined using FROM (10) TO (MAXVALUE) allows any values greater
300           than or equal to 10.
301
302           When creating a range partition involving more than one column, it
303           can also make sense to use MAXVALUE as part of the lower bound, and
304           MINVALUE as part of the upper bound. For example, a partition
305           defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows
306           where the first partition key column is greater than 0 and less
307           than or equal to 10. Similarly, a partition defined using FROM
308           ('a', MINVALUE) TO ('b', MINVALUE) allows any rows where the first
309           partition key column starts with "a".
310
311           Note that if MINVALUE or MAXVALUE is used for one column of a
312           partitioning bound, the same value must be used for all subsequent
313           columns. For example, (10, MINVALUE, 0) is not a valid bound; you
314           should write (10, MINVALUE, MINVALUE).
315
316           Also note that some element types, such as timestamp, have a notion
317           of "infinity", which is just another value that can be stored. This
318           is different from MINVALUE and MAXVALUE, which are not real values
319           that can be stored, but rather they are ways of saying that the
320           value is unbounded.  MAXVALUE can be thought of as being greater
321           than any other value, including "infinity" and MINVALUE as being
322           less than any other value, including "minus infinity". Thus the
323           range FROM ('infinity') TO (MAXVALUE) is not an empty range; it
324           allows precisely one value to be stored — "infinity".
325
326           If DEFAULT is specified, the table will be created as the default
327           partition of the parent table. This option is not available for
328           hash-partitioned tables. A partition key value not fitting into any
329           other partition of the given parent will be routed to the default
330           partition.
331
332           When a table has an existing DEFAULT partition and a new partition
333           is added to it, the default partition must be scanned to verify
334           that it does not contain any rows which properly belong in the new
335           partition. If the default partition contains a large number of
336           rows, this may be slow. The scan will be skipped if the default
337           partition is a foreign table or if it has a constraint which proves
338           that it cannot contain rows which should be placed in the new
339           partition.
340
341           When creating a hash partition, a modulus and remainder must be
342           specified. The modulus must be a positive integer, and the
343           remainder must be a non-negative integer less than the modulus.
344           Typically, when initially setting up a hash-partitioned table, you
345           should choose a modulus equal to the number of partitions and
346           assign every table the same modulus and a different remainder (see
347           examples, below). However, it is not required that every partition
348           have the same modulus, only that every modulus which occurs among
349           the partitions of a hash-partitioned table is a factor of the next
350           larger modulus. This allows the number of partitions to be
351           increased incrementally without needing to move all the data at
352           once. For example, suppose you have a hash-partitioned table with 8
353           partitions, each of which has modulus 8, but find it necessary to
354           increase the number of partitions to 16. You can detach one of the
355           modulus-8 partitions, create two new modulus-16 partitions covering
356           the same portion of the key space (one with a remainder equal to
357           the remainder of the detached partition, and the other with a
358           remainder equal to that value plus 8), and repopulate them with
359           data. You can then repeat this -- perhaps at a later time -- for
360           each modulus-8 partition until none remain. While this may still
361           involve a large amount of data movement at each step, it is still
362           better than having to create a whole new table and move all the
363           data at once.
364
365           A partition must have the same column names and types as the
366           partitioned table to which it belongs. Modifications to the column
367           names or types of a partitioned table will automatically propagate
368           to all partitions.  CHECK constraints will be inherited
369           automatically by every partition, but an individual partition may
370           specify additional CHECK constraints; additional constraints with
371           the same name and condition as in the parent will be merged with
372           the parent constraint. Defaults may be specified separately for
373           each partition. But note that a partition's default value is not
374           applied when inserting a tuple through a partitioned table.
375
376           Rows inserted into a partitioned table will be automatically routed
377           to the correct partition. If no suitable partition exists, an error
378           will occur.
379
380           Operations such as TRUNCATE which normally affect a table and all
381           of its inheritance children will cascade to all partitions, but may
382           also be performed on an individual partition. Note that dropping a
383           partition with DROP TABLE requires taking an ACCESS EXCLUSIVE lock
384           on the parent table.
385
386       LIKE source_table [ like_option ... ]
387           The LIKE clause specifies a table from which the new table
388           automatically copies all column names, their data types, and their
389           not-null constraints.
390
391           Unlike INHERITS, the new table and original table are completely
392           decoupled after creation is complete. Changes to the original table
393           will not be applied to the new table, and it is not possible to
394           include data of the new table in scans of the original table.
395
396           Also unlike INHERITS, columns and constraints copied by LIKE are
397           not merged with similarly named columns and constraints. If the
398           same name is specified explicitly or in another LIKE clause, an
399           error is signaled.
400
401           The optional like_option clauses specify which additional
402           properties of the original table to copy. Specifying INCLUDING
403           copies the property, specifying EXCLUDING omits the property.
404           EXCLUDING is the default. If multiple specifications are made for
405           the same kind of object, the last one is used. The available
406           options are:
407
408           INCLUDING COMMENTS
409               Comments for the copied columns, constraints, and indexes will
410               be copied. The default behavior is to exclude comments,
411               resulting in the copied columns and constraints in the new
412               table having no comments.
413
414           INCLUDING CONSTRAINTS
415               CHECK constraints will be copied. No distinction is made
416               between column constraints and table constraints. Not-null
417               constraints are always copied to the new table.
418
419           INCLUDING DEFAULTS
420               Default expressions for the copied column definitions will be
421               copied. Otherwise, default expressions are not copied,
422               resulting in the copied columns in the new table having null
423               defaults. Note that copying defaults that call
424               database-modification functions, such as nextval, may create a
425               functional linkage between the original and new tables.
426
427           INCLUDING GENERATED
428               Any generation expressions of copied column definitions will be
429               copied. By default, new columns will be regular base columns.
430
431           INCLUDING IDENTITY
432               Any identity specifications of copied column definitions will
433               be copied. A new sequence is created for each identity column
434               of the new table, separate from the sequences associated with
435               the old table.
436
437           INCLUDING INDEXES
438               Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
439               original table will be created on the new table. Names for the
440               new indexes and constraints are chosen according to the default
441               rules, regardless of how the originals were named. (This
442               behavior avoids possible duplicate-name failures for the new
443               indexes.)
444
445           INCLUDING STATISTICS
446               Extended statistics are copied to the new table.
447
448           INCLUDING STORAGE
449               STORAGE settings for the copied column definitions will be
450               copied. The default behavior is to exclude STORAGE settings,
451               resulting in the copied columns in the new table having
452               type-specific default settings. For more on STORAGE settings,
453               see Section 69.2.
454
455           INCLUDING ALL
456               INCLUDING ALL is an abbreviated form selecting all the
457               available individual options. (It could be useful to write
458               individual EXCLUDING clauses after INCLUDING ALL to select all
459               but some specific options.)
460
461           The LIKE clause can also be used to copy column definitions from
462           views, foreign tables, or composite types. Inapplicable options
463           (e.g., INCLUDING INDEXES from a view) are ignored.
464
465       CONSTRAINT constraint_name
466           An optional name for a column or table constraint. If the
467           constraint is violated, the constraint name is present in error
468           messages, so constraint names like col must be positive can be used
469           to communicate helpful constraint information to client
470           applications. (Double-quotes are needed to specify constraint names
471           that contain spaces.) If a constraint name is not specified, the
472           system generates a name.
473
474       NOT NULL
475           The column is not allowed to contain null values.
476
477       NULL
478           The column is allowed to contain null values. This is the default.
479
480           This clause is only provided for compatibility with non-standard
481           SQL databases. Its use is discouraged in new applications.
482
483       CHECK ( expression ) [ NO INHERIT ]
484           The CHECK clause specifies an expression producing a Boolean result
485           which new or updated rows must satisfy for an insert or update
486           operation to succeed. Expressions evaluating to TRUE or UNKNOWN
487           succeed. Should any row of an insert or update operation produce a
488           FALSE result, an error exception is raised and the insert or update
489           does not alter the database. A check constraint specified as a
490           column constraint should reference that column's value only, while
491           an expression appearing in a table constraint can reference
492           multiple columns.
493
494           Currently, CHECK expressions cannot contain subqueries nor refer to
495           variables other than columns of the current row (see
496           Section 5.4.1). The system column tableoid may be referenced, but
497           not any other system column.
498
499           A constraint marked with NO INHERIT will not propagate to child
500           tables.
501
502           When a table has multiple CHECK constraints, they will be tested
503           for each row in alphabetical order by name, after checking NOT NULL
504           constraints. (PostgreSQL versions before 9.5 did not honor any
505           particular firing order for CHECK constraints.)
506
507       DEFAULT default_expr
508           The DEFAULT clause assigns a default data value for the column
509           whose column definition it appears within. The value is any
510           variable-free expression (in particular, cross-references to other
511           columns in the current table are not allowed). Subqueries are not
512           allowed either. The data type of the default expression must match
513           the data type of the column.
514
515           The default expression will be used in any insert operation that
516           does not specify a value for the column. If there is no default for
517           a column, then the default is null.
518
519       GENERATED ALWAYS AS ( generation_expr ) STORED
520           This clause creates the column as a generated column. The column
521           cannot be written to, and when read the result of the specified
522           expression will be returned.
523
524           The keyword STORED is required to signify that the column will be
525           computed on write and will be stored on disk.
526
527           The generation expression can refer to other columns in the table,
528           but not other generated columns. Any functions and operators used
529           must be immutable. References to other tables are not allowed.
530
531       GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
532           This clause creates the column as an identity column. It will have
533           an implicit sequence attached to it and the column in new rows will
534           automatically have values from the sequence assigned to it. Such a
535           column is implicitly NOT NULL.
536
537           The clauses ALWAYS and BY DEFAULT determine how explicitly
538           user-specified values are handled in INSERT and UPDATE commands.
539
540           In an INSERT command, if ALWAYS is selected, a user-specified value
541           is only accepted if the INSERT statement specifies OVERRIDING
542           SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified
543           value takes precedence. See INSERT(7) for details. (In the COPY
544           command, user-specified values are always used regardless of this
545           setting.)
546
547           In an UPDATE command, if ALWAYS is selected, any update of the
548           column to any value other than DEFAULT will be rejected. If BY
549           DEFAULT is selected, the column can be updated normally. (There is
550           no OVERRIDING clause for the UPDATE command.)
551
552           The optional sequence_options clause can be used to override the
553           options of the sequence. See CREATE SEQUENCE (CREATE_SEQUENCE(7))
554           for details.
555
556       UNIQUE (column constraint)
557       UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
558       (table constraint)
559           The UNIQUE constraint specifies that a group of one or more columns
560           of a table can contain only unique values. The behavior of a unique
561           table constraint is the same as that of a unique column constraint,
562           with the additional capability to span multiple columns. The
563           constraint therefore enforces that any two rows must differ in at
564           least one of these columns.
565
566           For the purpose of a unique constraint, null values are not
567           considered equal.
568
569           Each unique constraint should name a set of columns that is
570           different from the set of columns named by any other unique or
571           primary key constraint defined for the table. (Otherwise, redundant
572           unique constraints will be discarded.)
573
574           When establishing a unique constraint for a multi-level partition
575           hierarchy, all the columns in the partition key of the target
576           partitioned table, as well as those of all its descendant
577           partitioned tables, must be included in the constraint definition.
578
579           Adding a unique constraint will automatically create a unique btree
580           index on the column or group of columns used in the constraint.
581
582           The optional INCLUDE clause adds to that index one or more columns
583           that are simply “payload”: uniqueness is not enforced on them, and
584           the index cannot be searched on the basis of those columns. However
585           they can be retrieved by an index-only scan. Note that although the
586           constraint is not enforced on included columns, it still depends on
587           them. Consequently, some operations on such columns (e.g., DROP
588           COLUMN) can cause cascaded constraint and index deletion.
589
590       PRIMARY KEY (column constraint)
591       PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
592       (table constraint)
593           The PRIMARY KEY constraint specifies that a column or columns of a
594           table can contain only unique (non-duplicate), nonnull values. Only
595           one primary key can be specified for a table, whether as a column
596           constraint or a table constraint.
597
598           The primary key constraint should name a set of columns that is
599           different from the set of columns named by any unique constraint
600           defined for the same table. (Otherwise, the unique constraint is
601           redundant and will be discarded.)
602
603           PRIMARY KEY enforces the same data constraints as a combination of
604           UNIQUE and NOT NULL. However, identifying a set of columns as the
605           primary key also provides metadata about the design of the schema,
606           since a primary key implies that other tables can rely on this set
607           of columns as a unique identifier for rows.
608
609           When placed on a partitioned table, PRIMARY KEY constraints share
610           the restrictions previously described for UNIQUE constraints.
611
612           Adding a PRIMARY KEY constraint will automatically create a unique
613           btree index on the column or group of columns used in the
614           constraint.
615
616           The optional INCLUDE clause adds to that index one or more columns
617           that are simply “payload”: uniqueness is not enforced on them, and
618           the index cannot be searched on the basis of those columns. However
619           they can be retrieved by an index-only scan. Note that although the
620           constraint is not enforced on included columns, it still depends on
621           them. Consequently, some operations on such columns (e.g., DROP
622           COLUMN) can cause cascaded constraint and index deletion.
623
624       EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
625       ) index_parameters [ WHERE ( predicate ) ]
626           The EXCLUDE clause defines an exclusion constraint, which
627           guarantees that if any two rows are compared on the specified
628           column(s) or expression(s) using the specified operator(s), not all
629           of these comparisons will return TRUE. If all of the specified
630           operators test for equality, this is equivalent to a UNIQUE
631           constraint, although an ordinary unique constraint will be faster.
632           However, exclusion constraints can specify constraints that are
633           more general than simple equality. For example, you can specify a
634           constraint that no two rows in the table contain overlapping
635           circles (see Section 8.8) by using the && operator.
636
637           Exclusion constraints are implemented using an index, so each
638           specified operator must be associated with an appropriate operator
639           class (see Section 11.10) for the index access method index_method.
640           The operators are required to be commutative. Each exclude_element
641           can optionally specify an operator class and/or ordering options;
642           these are described fully under CREATE INDEX (CREATE_INDEX(7)).
643
644           The access method must support amgettuple (see Chapter 61); at
645           present this means GIN cannot be used. Although it's allowed, there
646           is little point in using B-tree or hash indexes with an exclusion
647           constraint, because this does nothing that an ordinary unique
648           constraint doesn't do better. So in practice the access method will
649           always be GiST or SP-GiST.
650
651           The predicate allows you to specify an exclusion constraint on a
652           subset of the table; internally this creates a partial index. Note
653           that parentheses are required around the predicate.
654
655       REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
656       referential_action ] [ ON UPDATE referential_action ] (column
657       constraint)
658       FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
659       [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON
660       UPDATE referential_action ] (table constraint)
661           These clauses specify a foreign key constraint, which requires that
662           a group of one or more columns of the new table must only contain
663           values that match values in the referenced column(s) of some row of
664           the referenced table. If the refcolumn list is omitted, the primary
665           key of the reftable is used. The referenced columns must be the
666           columns of a non-deferrable unique or primary key constraint in the
667           referenced table. The user must have REFERENCES permission on the
668           referenced table (either the whole table, or the specific
669           referenced columns). The addition of a foreign key constraint
670           requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note
671           that foreign key constraints cannot be defined between temporary
672           tables and permanent tables.
673
674           A value inserted into the referencing column(s) is matched against
675           the values of the referenced table and referenced columns using the
676           given match type. There are three match types: MATCH FULL, MATCH
677           PARTIAL, and MATCH SIMPLE (which is the default).  MATCH FULL will
678           not allow one column of a multicolumn foreign key to be null unless
679           all foreign key columns are null; if they are all null, the row is
680           not required to have a match in the referenced table.  MATCH SIMPLE
681           allows any of the foreign key columns to be null; if any of them
682           are null, the row is not required to have a match in the referenced
683           table.  MATCH PARTIAL is not yet implemented. (Of course, NOT NULL
684           constraints can be applied to the referencing column(s) to prevent
685           these cases from arising.)
686
687           In addition, when the data in the referenced columns is changed,
688           certain actions are performed on the data in this table's columns.
689           The ON DELETE clause specifies the action to perform when a
690           referenced row in the referenced table is being deleted. Likewise,
691           the ON UPDATE clause specifies the action to perform when a
692           referenced column in the referenced table is being updated to a new
693           value. If the row is updated, but the referenced column is not
694           actually changed, no action is done. Referential actions other than
695           the NO ACTION check cannot be deferred, even if the constraint is
696           declared deferrable. There are the following possible actions for
697           each clause:
698
699           NO ACTION
700               Produce an error indicating that the deletion or update would
701               create a foreign key constraint violation. If the constraint is
702               deferred, this error will be produced at constraint check time
703               if there still exist any referencing rows. This is the default
704               action.
705
706           RESTRICT
707               Produce an error indicating that the deletion or update would
708               create a foreign key constraint violation. This is the same as
709               NO ACTION except that the check is not deferrable.
710
711           CASCADE
712               Delete any rows referencing the deleted row, or update the
713               values of the referencing column(s) to the new values of the
714               referenced columns, respectively.
715
716           SET NULL
717               Set the referencing column(s) to null.
718
719           SET DEFAULT
720               Set the referencing column(s) to their default values. (There
721               must be a row in the referenced table matching the default
722               values, if they are not null, or the operation will fail.)
723
724           If the referenced column(s) are changed frequently, it might be
725           wise to add an index to the referencing column(s) so that
726           referential actions associated with the foreign key constraint can
727           be performed more efficiently.
728
729       DEFERRABLE
730       NOT DEFERRABLE
731           This controls whether the constraint can be deferred. A constraint
732           that is not deferrable will be checked immediately after every
733           command. Checking of constraints that are deferrable can be
734           postponed until the end of the transaction (using the SET
735           CONSTRAINTS (SET_CONSTRAINTS(7)) command).  NOT DEFERRABLE is the
736           default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and
737           REFERENCES (foreign key) constraints accept this clause.  NOT NULL
738           and CHECK constraints are not deferrable. Note that deferrable
739           constraints cannot be used as conflict arbitrators in an INSERT
740           statement that includes an ON CONFLICT DO UPDATE clause.
741
742       INITIALLY IMMEDIATE
743       INITIALLY DEFERRED
744           If a constraint is deferrable, this clause specifies the default
745           time to check the constraint. If the constraint is INITIALLY
746           IMMEDIATE, it is checked after each statement. This is the default.
747           If the constraint is INITIALLY DEFERRED, it is checked only at the
748           end of the transaction. The constraint check time can be altered
749           with the SET CONSTRAINTS (SET_CONSTRAINTS(7)) command.
750
751       USING method
752           This optional clause specifies the table access method to use to
753           store the contents for the new table; the method needs be an access
754           method of type TABLE. See Chapter 60 for more information. If this
755           option is not specified, the default table access method is chosen
756           for the new table. See default_table_access_method for more
757           information.
758
759       WITH ( storage_parameter [= value] [, ... ] )
760           This clause specifies optional storage parameters for a table or
761           index; see Storage Parameters below for more information. For
762           backward-compatibility the WITH clause for a table can also include
763           OIDS=FALSE to specify that rows of the new table should not contain
764           OIDs (object identifiers), OIDS=TRUE is not supported anymore.
765
766       WITHOUT OIDS
767           This is backward-compatible syntax for declaring a table WITHOUT
768           OIDS, creating a table WITH OIDS is not supported anymore.
769
770       ON COMMIT
771           The behavior of temporary tables at the end of a transaction block
772           can be controlled using ON COMMIT. The three options are:
773
774           PRESERVE ROWS
775               No special action is taken at the ends of transactions. This is
776               the default behavior.
777
778           DELETE ROWS
779               All rows in the temporary table will be deleted at the end of
780               each transaction block. Essentially, an automatic TRUNCATE(7)
781               is done at each commit. When used on a partitioned table, this
782               is not cascaded to its partitions.
783
784           DROP
785               The temporary table will be dropped at the end of the current
786               transaction block. When used on a partitioned table, this
787               action drops its partitions and when used on tables with
788               inheritance children, it drops the dependent children.
789
790       TABLESPACE tablespace_name
791           The tablespace_name is the name of the tablespace in which the new
792           table is to be created. If not specified, default_tablespace is
793           consulted, or temp_tablespaces if the table is temporary. For
794           partitioned tables, since no storage is required for the table
795           itself, the tablespace specified overrides default_tablespace as
796           the default tablespace to use for any newly created partitions when
797           no other tablespace is explicitly specified.
798
799       USING INDEX TABLESPACE tablespace_name
800           This clause allows selection of the tablespace in which the index
801           associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
802           be created. If not specified, default_tablespace is consulted, or
803           temp_tablespaces if the table is temporary.
804
805   Storage Parameters
806       The WITH clause can specify storage parameters for tables, and for
807       indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
808       Storage parameters for indexes are documented in CREATE INDEX
809       (CREATE_INDEX(7)). The storage parameters currently available for
810       tables are listed below. For many of these parameters, as shown, there
811       is an additional parameter with the same name prefixed with toast.,
812       which controls the behavior of the table's secondary TOAST table, if
813       any (see Section 69.2 for more information about TOAST). If a table
814       parameter value is set and the equivalent toast.  parameter is not, the
815       TOAST table will use the table's parameter value. Specifying these
816       parameters for partitioned tables is not supported, but you may specify
817       them for individual leaf partitions.
818
819       fillfactor (integer)
820           The fillfactor for a table is a percentage between 10 and 100. 100
821           (complete packing) is the default. When a smaller fillfactor is
822           specified, INSERT operations pack table pages only to the indicated
823           percentage; the remaining space on each page is reserved for
824           updating rows on that page. This gives UPDATE a chance to place the
825           updated copy of a row on the same page as the original, which is
826           more efficient than placing it on a different page. For a table
827           whose entries are never updated, complete packing is the best
828           choice, but in heavily updated tables smaller fillfactors are
829           appropriate. This parameter cannot be set for TOAST tables.
830
831       toast_tuple_target (integer)
832           The toast_tuple_target specifies the minimum tuple length required
833           before we try to compress and/or move long column values into TOAST
834           tables, and is also the target length we try to reduce the length
835           below once toasting begins. This affects columns marked as External
836           (for move), Main (for compression), or Extended (for both) and
837           applies only to new tuples. There is no effect on existing rows. By
838           default this parameter is set to allow at least 4 tuples per block,
839           which with the default block size will be 2040 bytes. Valid values
840           are between 128 bytes and the (block size - header), by default
841           8160 bytes. Changing this value may not be useful for very short or
842           very long rows. Note that the default setting is often close to
843           optimal, and it is possible that setting this parameter could have
844           negative effects in some cases. This parameter cannot be set for
845           TOAST tables.
846
847       parallel_workers (integer)
848           This sets the number of workers that should be used to assist a
849           parallel scan of this table. If not set, the system will determine
850           a value based on the relation size. The actual number of workers
851           chosen by the planner or by utility statements that use parallel
852           scans may be less, for example due to the setting of
853           max_worker_processes.
854
855       autovacuum_enabled, toast.autovacuum_enabled (boolean)
856           Enables or disables the autovacuum daemon for a particular table.
857           If true, the autovacuum daemon will perform automatic VACUUM and/or
858           ANALYZE operations on this table following the rules discussed in
859           Section 24.1.6. If false, this table will not be autovacuumed,
860           except to prevent transaction ID wraparound. See Section 24.1.5 for
861           more about wraparound prevention. Note that the autovacuum daemon
862           does not run at all (except to prevent transaction ID wraparound)
863           if the autovacuum parameter is false; setting individual tables'
864           storage parameters does not override that. Therefore there is
865           seldom much point in explicitly setting this storage parameter to
866           true, only to false.
867
868       vacuum_index_cleanup, toast.vacuum_index_cleanup (boolean)
869           Enables or disables index cleanup when VACUUM is run on this table.
870           The default value is true. Disabling index cleanup can speed up
871           VACUUM very significantly, but may also lead to severely bloated
872           indexes if table modifications are frequent. The INDEX_CLEANUP
873           parameter of VACUUM(7), if specified, overrides the value of this
874           option.
875
876       vacuum_truncate, toast.vacuum_truncate (boolean)
877           Enables or disables vacuum to try to truncate off any empty pages
878           at the end of this table. The default value is true. If true,
879           VACUUM and autovacuum do the truncation and the disk space for the
880           truncated pages is returned to the operating system. Note that the
881           truncation requires ACCESS EXCLUSIVE lock on the table. The
882           TRUNCATE parameter of VACUUM(7), if specified, overrides the value
883           of this option.
884
885       autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
886       (integer)
887           Per-table value for autovacuum_vacuum_threshold parameter.
888
889       autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
890       (floating point)
891           Per-table value for autovacuum_vacuum_scale_factor parameter.
892
893       autovacuum_vacuum_insert_threshold,
894       toast.autovacuum_vacuum_insert_threshold (integer)
895           Per-table value for autovacuum_vacuum_insert_threshold parameter.
896           The special value of -1 may be used to disable insert vacuums on
897           the table.
898
899       autovacuum_vacuum_insert_scale_factor,
900       toast.autovacuum_vacuum_insert_scale_factor (floating point)
901           Per-table value for autovacuum_vacuum_insert_scale_factor
902           parameter.
903
904       autovacuum_analyze_threshold (integer)
905           Per-table value for autovacuum_analyze_threshold parameter.
906
907       autovacuum_analyze_scale_factor (floating point)
908           Per-table value for autovacuum_analyze_scale_factor parameter.
909
910       autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
911       (floating point)
912           Per-table value for autovacuum_vacuum_cost_delay parameter.
913
914       autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
915       (integer)
916           Per-table value for autovacuum_vacuum_cost_limit parameter.
917
918       autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
919           Per-table value for vacuum_freeze_min_age parameter. Note that
920           autovacuum will ignore per-table autovacuum_freeze_min_age
921           parameters that are larger than half the system-wide
922           autovacuum_freeze_max_age setting.
923
924       autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
925           Per-table value for autovacuum_freeze_max_age parameter. Note that
926           autovacuum will ignore per-table autovacuum_freeze_max_age
927           parameters that are larger than the system-wide setting (it can
928           only be set smaller).
929
930       autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
931       (integer)
932           Per-table value for vacuum_freeze_table_age parameter.
933
934       autovacuum_multixact_freeze_min_age,
935       toast.autovacuum_multixact_freeze_min_age (integer)
936           Per-table value for vacuum_multixact_freeze_min_age parameter. Note
937           that autovacuum will ignore per-table
938           autovacuum_multixact_freeze_min_age parameters that are larger than
939           half the system-wide autovacuum_multixact_freeze_max_age setting.
940
941       autovacuum_multixact_freeze_max_age,
942       toast.autovacuum_multixact_freeze_max_age (integer)
943           Per-table value for autovacuum_multixact_freeze_max_age parameter.
944           Note that autovacuum will ignore per-table
945           autovacuum_multixact_freeze_max_age parameters that are larger than
946           the system-wide setting (it can only be set smaller).
947
948       autovacuum_multixact_freeze_table_age,
949       toast.autovacuum_multixact_freeze_table_age (integer)
950           Per-table value for vacuum_multixact_freeze_table_age parameter.
951
952       log_autovacuum_min_duration, toast.log_autovacuum_min_duration
953       (integer)
954           Per-table value for log_autovacuum_min_duration parameter.
955
956       user_catalog_table (boolean)
957           Declare the table as an additional catalog table for purposes of
958           logical replication. See Section 48.6.2 for details. This parameter
959           cannot be set for TOAST tables.
960

NOTES

962       PostgreSQL automatically creates an index for each unique constraint
963       and primary key constraint to enforce uniqueness. Thus, it is not
964       necessary to create an index explicitly for primary key columns. (See
965       CREATE INDEX (CREATE_INDEX(7)) for more information.)
966
967       Unique constraints and primary keys are not inherited in the current
968       implementation. This makes the combination of inheritance and unique
969       constraints rather dysfunctional.
970
971       A table cannot have more than 1600 columns. (In practice, the effective
972       limit is usually lower because of tuple-length constraints.)
973

EXAMPLES

975       Create table films and table distributors:
976
977           CREATE TABLE films (
978               code        char(5) CONSTRAINT firstkey PRIMARY KEY,
979               title       varchar(40) NOT NULL,
980               did         integer NOT NULL,
981               date_prod   date,
982               kind        varchar(10),
983               len         interval hour to minute
984           );
985
986           CREATE TABLE distributors (
987                did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
988                name   varchar(40) NOT NULL CHECK (name <> '')
989           );
990
991       Create a table with a 2-dimensional array:
992
993           CREATE TABLE array_int (
994               vector  int[][]
995           );
996
997       Define a unique table constraint for the table films. Unique table
998       constraints can be defined on one or more columns of the table:
999
1000           CREATE TABLE films (
1001               code        char(5),
1002               title       varchar(40),
1003               did         integer,
1004               date_prod   date,
1005               kind        varchar(10),
1006               len         interval hour to minute,
1007               CONSTRAINT production UNIQUE(date_prod)
1008           );
1009
1010       Define a check column constraint:
1011
1012           CREATE TABLE distributors (
1013               did     integer CHECK (did > 100),
1014               name    varchar(40)
1015           );
1016
1017       Define a check table constraint:
1018
1019           CREATE TABLE distributors (
1020               did     integer,
1021               name    varchar(40),
1022               CONSTRAINT con1 CHECK (did > 100 AND name <> '')
1023           );
1024
1025       Define a primary key table constraint for the table films:
1026
1027           CREATE TABLE films (
1028               code        char(5),
1029               title       varchar(40),
1030               did         integer,
1031               date_prod   date,
1032               kind        varchar(10),
1033               len         interval hour to minute,
1034               CONSTRAINT code_title PRIMARY KEY(code,title)
1035           );
1036
1037       Define a primary key constraint for table distributors. The following
1038       two examples are equivalent, the first using the table constraint
1039       syntax, the second the column constraint syntax:
1040
1041           CREATE TABLE distributors (
1042               did     integer,
1043               name    varchar(40),
1044               PRIMARY KEY(did)
1045           );
1046
1047           CREATE TABLE distributors (
1048               did     integer PRIMARY KEY,
1049               name    varchar(40)
1050           );
1051
1052       Assign a literal constant default value for the column name, arrange
1053       for the default value of column did to be generated by selecting the
1054       next value of a sequence object, and make the default value of modtime
1055       be the time at which the row is inserted:
1056
1057           CREATE TABLE distributors (
1058               name      varchar(40) DEFAULT 'Luso Films',
1059               did       integer DEFAULT nextval('distributors_serial'),
1060               modtime   timestamp DEFAULT current_timestamp
1061           );
1062
1063       Define two NOT NULL column constraints on the table distributors, one
1064       of which is explicitly given a name:
1065
1066           CREATE TABLE distributors (
1067               did     integer CONSTRAINT no_null NOT NULL,
1068               name    varchar(40) NOT NULL
1069           );
1070
1071       Define a unique constraint for the name column:
1072
1073           CREATE TABLE distributors (
1074               did     integer,
1075               name    varchar(40) UNIQUE
1076           );
1077
1078       The same, specified as a table constraint:
1079
1080           CREATE TABLE distributors (
1081               did     integer,
1082               name    varchar(40),
1083               UNIQUE(name)
1084           );
1085
1086       Create the same table, specifying 70% fill factor for both the table
1087       and its unique index:
1088
1089           CREATE TABLE distributors (
1090               did     integer,
1091               name    varchar(40),
1092               UNIQUE(name) WITH (fillfactor=70)
1093           )
1094           WITH (fillfactor=70);
1095
1096       Create table circles with an exclusion constraint that prevents any two
1097       circles from overlapping:
1098
1099           CREATE TABLE circles (
1100               c circle,
1101               EXCLUDE USING gist (c WITH &&)
1102           );
1103
1104       Create table cinemas in tablespace diskvol1:
1105
1106           CREATE TABLE cinemas (
1107                   id serial,
1108                   name text,
1109                   location text
1110           ) TABLESPACE diskvol1;
1111
1112       Create a composite type and a typed table:
1113
1114           CREATE TYPE employee_type AS (name text, salary numeric);
1115
1116           CREATE TABLE employees OF employee_type (
1117               PRIMARY KEY (name),
1118               salary WITH OPTIONS DEFAULT 1000
1119           );
1120
1121       Create a range partitioned table:
1122
1123           CREATE TABLE measurement (
1124               logdate         date not null,
1125               peaktemp        int,
1126               unitsales       int
1127           ) PARTITION BY RANGE (logdate);
1128
1129       Create a range partitioned table with multiple columns in the partition
1130       key:
1131
1132           CREATE TABLE measurement_year_month (
1133               logdate         date not null,
1134               peaktemp        int,
1135               unitsales       int
1136           ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1137
1138       Create a list partitioned table:
1139
1140           CREATE TABLE cities (
1141               city_id      bigserial not null,
1142               name         text not null,
1143               population   bigint
1144           ) PARTITION BY LIST (left(lower(name), 1));
1145
1146       Create a hash partitioned table:
1147
1148           CREATE TABLE orders (
1149               order_id     bigint not null,
1150               cust_id      bigint not null,
1151               status       text
1152           ) PARTITION BY HASH (order_id);
1153
1154       Create partition of a range partitioned table:
1155
1156           CREATE TABLE measurement_y2016m07
1157               PARTITION OF measurement (
1158               unitsales DEFAULT 0
1159           ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1160
1161       Create a few partitions of a range partitioned table with multiple
1162       columns in the partition key:
1163
1164           CREATE TABLE measurement_ym_older
1165               PARTITION OF measurement_year_month
1166               FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1167
1168           CREATE TABLE measurement_ym_y2016m11
1169               PARTITION OF measurement_year_month
1170               FOR VALUES FROM (2016, 11) TO (2016, 12);
1171
1172           CREATE TABLE measurement_ym_y2016m12
1173               PARTITION OF measurement_year_month
1174               FOR VALUES FROM (2016, 12) TO (2017, 01);
1175
1176           CREATE TABLE measurement_ym_y2017m01
1177               PARTITION OF measurement_year_month
1178               FOR VALUES FROM (2017, 01) TO (2017, 02);
1179
1180       Create partition of a list partitioned table:
1181
1182           CREATE TABLE cities_ab
1183               PARTITION OF cities (
1184               CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1185           ) FOR VALUES IN ('a', 'b');
1186
1187       Create partition of a list partitioned table that is itself further
1188       partitioned and then add a partition to it:
1189
1190           CREATE TABLE cities_ab
1191               PARTITION OF cities (
1192               CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1193           ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1194
1195           CREATE TABLE cities_ab_10000_to_100000
1196               PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1197
1198       Create partitions of a hash partitioned table:
1199
1200           CREATE TABLE orders_p1 PARTITION OF orders
1201               FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1202           CREATE TABLE orders_p2 PARTITION OF orders
1203               FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1204           CREATE TABLE orders_p3 PARTITION OF orders
1205               FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1206           CREATE TABLE orders_p4 PARTITION OF orders
1207               FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1208
1209       Create a default partition:
1210
1211           CREATE TABLE cities_partdef
1212               PARTITION OF cities DEFAULT;
1213

COMPATIBILITY

1215       The CREATE TABLE command conforms to the SQL standard, with exceptions
1216       listed below.
1217
1218   Temporary Tables
1219       Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1220       standard, the effect is not the same. In the standard, temporary tables
1221       are defined just once and automatically exist (starting with empty
1222       contents) in every session that needs them.  PostgreSQL instead
1223       requires each session to issue its own CREATE TEMPORARY TABLE command
1224       for each temporary table to be used. This allows different sessions to
1225       use the same temporary table name for different purposes, whereas the
1226       standard's approach constrains all instances of a given temporary table
1227       name to have the same table structure.
1228
1229       The standard's definition of the behavior of temporary tables is widely
1230       ignored.  PostgreSQL's behavior on this point is similar to that of
1231       several other SQL databases.
1232
1233       The SQL standard also distinguishes between global and local temporary
1234       tables, where a local temporary table has a separate set of contents
1235       for each SQL module within each session, though its definition is still
1236       shared across sessions. Since PostgreSQL does not support SQL modules,
1237       this distinction is not relevant in PostgreSQL.
1238
1239       For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1240       keywords in a temporary table declaration, but they currently have no
1241       effect. Use of these keywords is discouraged, since future versions of
1242       PostgreSQL might adopt a more standard-compliant interpretation of
1243       their meaning.
1244
1245       The ON COMMIT clause for temporary tables also resembles the SQL
1246       standard, but has some differences. If the ON COMMIT clause is omitted,
1247       SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1248       However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1249       The ON COMMIT DROP option does not exist in SQL.
1250
1251   Non-Deferred Uniqueness Constraints
1252       When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1253       checks for uniqueness immediately whenever a row is inserted or
1254       modified. The SQL standard says that uniqueness should be enforced only
1255       at the end of the statement; this makes a difference when, for example,
1256       a single command updates multiple key values. To obtain
1257       standard-compliant behavior, declare the constraint as DEFERRABLE but
1258       not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1259       significantly slower than immediate uniqueness checking.
1260
1261   Column Check Constraints
1262       The SQL standard says that CHECK column constraints can only refer to
1263       the column they apply to; only CHECK table constraints can refer to
1264       multiple columns.  PostgreSQL does not enforce this restriction; it
1265       treats column and table check constraints alike.
1266
1267   EXCLUDE Constraint
1268       The EXCLUDE constraint type is a PostgreSQL extension.
1269
1270   NULL “Constraint”
1271       The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1272       extension to the SQL standard that is included for compatibility with
1273       some other database systems (and for symmetry with the NOT NULL
1274       constraint). Since it is the default for any column, its presence is
1275       simply noise.
1276
1277   Constraint Naming
1278       The SQL standard says that table and domain constraints must have names
1279       that are unique across the schema containing the table or domain.
1280       PostgreSQL is laxer: it only requires constraint names to be unique
1281       across the constraints attached to a particular table or domain.
1282       However, this extra freedom does not exist for index-based constraints
1283       (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1284       index is named the same as the constraint, and index names must be
1285       unique across all relations within the same schema.
1286
1287       Currently, PostgreSQL does not record names for NOT NULL constraints at
1288       all, so they are not subject to the uniqueness restriction. This might
1289       change in a future release.
1290
1291   Inheritance
1292       Multiple inheritance via the INHERITS clause is a PostgreSQL language
1293       extension. SQL:1999 and later define single inheritance using a
1294       different syntax and different semantics. SQL:1999-style inheritance is
1295       not yet supported by PostgreSQL.
1296
1297   Zero-Column Tables
1298       PostgreSQL allows a table of no columns to be created (for example,
1299       CREATE TABLE foo();). This is an extension from the SQL standard, which
1300       does not allow zero-column tables. Zero-column tables are not in
1301       themselves very useful, but disallowing them creates odd special cases
1302       for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1303       restriction.
1304
1305   Multiple Identity Columns
1306       PostgreSQL allows a table to have more than one identity column. The
1307       standard specifies that a table can have at most one identity column.
1308       This is relaxed mainly to give more flexibility for doing schema
1309       changes or migrations. Note that the INSERT command supports only one
1310       override clause that applies to the entire statement, so having
1311       multiple identity columns with different behaviors is not well
1312       supported.
1313
1314   Generated Columns
1315       The option STORED is not standard but is also used by other SQL
1316       implementations. The SQL standard does not specify the storage of
1317       generated columns.
1318
1319   LIKE Clause
1320       While a LIKE clause exists in the SQL standard, many of the options
1321       that PostgreSQL accepts for it are not in the standard, and some of the
1322       standard's options are not implemented by PostgreSQL.
1323
1324   WITH Clause
1325       The WITH clause is a PostgreSQL extension; storage parameters are not
1326       in the standard.
1327
1328   Tablespaces
1329       The PostgreSQL concept of tablespaces is not part of the standard.
1330       Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1331       extensions.
1332
1333   Typed Tables
1334       Typed tables implement a subset of the SQL standard. According to the
1335       standard, a typed table has columns corresponding to the underlying
1336       composite type as well as one other column that is the
1337       “self-referencing column”.  PostgreSQL does not support
1338       self-referencing columns explicitly.
1339
1340   PARTITION BY Clause
1341       The PARTITION BY clause is a PostgreSQL extension.
1342
1343   PARTITION OF Clause
1344       The PARTITION OF clause is a PostgreSQL extension.
1345

SEE ALSO

1347       ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1348       AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1349       CREATE TYPE (CREATE_TYPE(7))
1350
1351
1352
1353PostgreSQL 13.4                      2021                      CREATE TABLE(7)
Impressum