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

DESCRIPTION

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

PARAMETERS

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

NOTES

894       Using OIDs in new applications is not recommended: where possible,
895       using an identity column or other sequence generator as the table's
896       primary key is preferred. However, if your application does make use of
897       OIDs to identify specific rows of a table, it is recommended to create
898       a unique constraint on the oid column of that table, to ensure that
899       OIDs in the table will indeed uniquely identify rows even after counter
900       wraparound. Avoid assuming that OIDs are unique across tables; if you
901       need a database-wide unique identifier, use the combination of tableoid
902       and row OID for the purpose.
903
904           Tip
905           The use of OIDS=FALSE is not recommended for tables with no primary
906           key, since without either an OID or a unique data key, it is
907           difficult to identify specific rows.
908
909       PostgreSQL automatically creates an index for each unique constraint
910       and primary key constraint to enforce uniqueness. Thus, it is not
911       necessary to create an index explicitly for primary key columns. (See
912       CREATE INDEX (CREATE_INDEX(7)) for more information.)
913
914       Unique constraints and primary keys are not inherited in the current
915       implementation. This makes the combination of inheritance and unique
916       constraints rather dysfunctional.
917
918       A table cannot have more than 1600 columns. (In practice, the effective
919       limit is usually lower because of tuple-length constraints.)
920

EXAMPLES

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

COMPATIBILITY

1162       The CREATE TABLE command conforms to the SQL standard, with exceptions
1163       listed below.
1164
1165   Temporary Tables
1166       Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1167       standard, the effect is not the same. In the standard, temporary tables
1168       are defined just once and automatically exist (starting with empty
1169       contents) in every session that needs them.  PostgreSQL instead
1170       requires each session to issue its own CREATE TEMPORARY TABLE command
1171       for each temporary table to be used. This allows different sessions to
1172       use the same temporary table name for different purposes, whereas the
1173       standard's approach constrains all instances of a given temporary table
1174       name to have the same table structure.
1175
1176       The standard's definition of the behavior of temporary tables is widely
1177       ignored.  PostgreSQL's behavior on this point is similar to that of
1178       several other SQL databases.
1179
1180       The SQL standard also distinguishes between global and local temporary
1181       tables, where a local temporary table has a separate set of contents
1182       for each SQL module within each session, though its definition is still
1183       shared across sessions. Since PostgreSQL does not support SQL modules,
1184       this distinction is not relevant in PostgreSQL.
1185
1186       For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1187       keywords in a temporary table declaration, but they currently have no
1188       effect. Use of these keywords is discouraged, since future versions of
1189       PostgreSQL might adopt a more standard-compliant interpretation of
1190       their meaning.
1191
1192       The ON COMMIT clause for temporary tables also resembles the SQL
1193       standard, but has some differences. If the ON COMMIT clause is omitted,
1194       SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1195       However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1196       The ON COMMIT DROP option does not exist in SQL.
1197
1198   Non-deferred Uniqueness Constraints
1199       When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1200       checks for uniqueness immediately whenever a row is inserted or
1201       modified. The SQL standard says that uniqueness should be enforced only
1202       at the end of the statement; this makes a difference when, for example,
1203       a single command updates multiple key values. To obtain
1204       standard-compliant behavior, declare the constraint as DEFERRABLE but
1205       not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1206       significantly slower than immediate uniqueness checking.
1207
1208   Column Check Constraints
1209       The SQL standard says that CHECK column constraints can only refer to
1210       the column they apply to; only CHECK table constraints can refer to
1211       multiple columns.  PostgreSQL does not enforce this restriction; it
1212       treats column and table check constraints alike.
1213
1214   EXCLUDE Constraint
1215       The EXCLUDE constraint type is a PostgreSQL extension.
1216
1217   NULL “Constraint”
1218       The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1219       extension to the SQL standard that is included for compatibility with
1220       some other database systems (and for symmetry with the NOT NULL
1221       constraint). Since it is the default for any column, its presence is
1222       simply noise.
1223
1224   Constraint Naming
1225       The SQL standard says that table and domain constraints must have names
1226       that are unique across the schema containing the table or domain.
1227       PostgreSQL is laxer: it only requires constraint names to be unique
1228       across the constraints attached to a particular table or domain.
1229       However, this extra freedom does not exist for index-based constraints
1230       (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1231       index is named the same as the constraint, and index names must be
1232       unique across all relations within the same schema.
1233
1234       Currently, PostgreSQL does not record names for NOT NULL constraints at
1235       all, so they are not subject to the uniqueness restriction. This might
1236       change in a future release.
1237
1238   Inheritance
1239       Multiple inheritance via the INHERITS clause is a PostgreSQL language
1240       extension. SQL:1999 and later define single inheritance using a
1241       different syntax and different semantics. SQL:1999-style inheritance is
1242       not yet supported by PostgreSQL.
1243
1244   Zero-column Tables
1245       PostgreSQL allows a table of no columns to be created (for example,
1246       CREATE TABLE foo();). This is an extension from the SQL standard, which
1247       does not allow zero-column tables. Zero-column tables are not in
1248       themselves very useful, but disallowing them creates odd special cases
1249       for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1250       restriction.
1251
1252   Multiple Identity Columns
1253       PostgreSQL allows a table to have more than one identity column. The
1254       standard specifies that a table can have at most one identity column.
1255       This is relaxed mainly to give more flexibility for doing schema
1256       changes or migrations. Note that the INSERT command supports only one
1257       override clause that applies to the entire statement, so having
1258       multiple identity columns with different behaviors is not well
1259       supported.
1260
1261   LIKE Clause
1262       While a LIKE clause exists in the SQL standard, many of the options
1263       that PostgreSQL accepts for it are not in the standard, and some of the
1264       standard's options are not implemented by PostgreSQL.
1265
1266   WITH Clause
1267       The WITH clause is a PostgreSQL extension; neither storage parameters
1268       nor OIDs are in the standard.
1269
1270   Tablespaces
1271       The PostgreSQL concept of tablespaces is not part of the standard.
1272       Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1273       extensions.
1274
1275   Typed Tables
1276       Typed tables implement a subset of the SQL standard. According to the
1277       standard, a typed table has columns corresponding to the underlying
1278       composite type as well as one other column that is the
1279       “self-referencing column”. PostgreSQL does not support these
1280       self-referencing columns explicitly, but the same effect can be had
1281       using the OID feature.
1282
1283   PARTITION BY Clause
1284       The PARTITION BY clause is a PostgreSQL extension.
1285
1286   PARTITION OF Clause
1287       The PARTITION OF clause is a PostgreSQL extension.
1288

SEE ALSO

1290       ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1291       AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1292       CREATE TYPE (CREATE_TYPE(7))
1293
1294
1295
1296PostgreSQL 11.6                      2019                      CREATE TABLE(7)
Impressum