1CREATE TABLE(7) PostgreSQL 11.6 Documentation CREATE TABLE(7)
2
6 CREATE_TABLE - define a new table
7
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 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 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 where column_constraint is:
44 [ 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 and table_constraint is:
58 [ 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 and like_option is:
69 { INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
71 and partition_bound_spec is:
73 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 index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
80 [ INCLUDE ( column_name [, ... ] ) ]
82 [ WITH ( storage_parameter [= value] [, ... ] ) ]
83 [ USING INDEX TABLESPACE tablespace_name ]
84 exclude_element in an EXCLUDE constraint is:
86 { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
88
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 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 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 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 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 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
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 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 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 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 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 table_name
159 The name (optionally schema-qualified) of the table to be created.
160 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 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 column_name
174 The name of a column to be created in the new table.
175 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 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 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 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 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 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 Column STORAGE settings are also copied from parent tables.
220 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 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 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 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 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 See Section 5.10 for more discussion on table partitioning.
258 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Extended statistics are copied to the new table if INCLUDING
413 STATISTICS is specified.
414 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 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 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 INCLUDING ALL is an abbreviated form of INCLUDING COMMENTS
434 INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING IDENTITY
435 INCLUDING INDEXES INCLUDING STATISTICS INCLUDING STORAGE.
436 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 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 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 NOT NULL
456 The column is not allowed to contain null values.
457 NULL
459 The column is allowed to contain null values. This is the default.
460 This clause is only provided for compatibility with non-standard
462 SQL databases. Its use is discouraged in new applications.
463 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 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 A constraint marked with NO INHERIT will not propagate to child
480 tables.
481 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 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 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 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 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 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 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 For the purpose of a unique constraint, null values are not
524 considered equal.
525 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 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 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 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 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 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 PRIMARY KEY constraints share the restrictions that UNIQUE
565 constraints have when placed on partitioned tables.
566 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 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 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 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 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 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 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 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 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 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 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 SET NULL
670 Set the referencing column(s) to null.
671 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 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 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 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 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 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 To remove OIDs from a table after it has been created, use ALTER
726 TABLE (ALTER_TABLE(7)).
727 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 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 PRESERVE ROWS
740 No special action is taken at the ends of transactions. This is
741 the default behavior.
742 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 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 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 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 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 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 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 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 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 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
829 (integer)
830 Per-table value for autovacuum_vacuum_threshold parameter.
831 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
833 (float4)
834 Per-table value for autovacuum_vacuum_scale_factor parameter.
835 autovacuum_analyze_threshold (integer)
837 Per-table value for autovacuum_analyze_threshold parameter.
838 autovacuum_analyze_scale_factor (float4)
840 Per-table value for autovacuum_analyze_scale_factor parameter.
841 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
843 (integer)
844 Per-table value for autovacuum_vacuum_cost_delay parameter.
845 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
847 (integer)
848 Per-table value for autovacuum_vacuum_cost_limit parameter.
849 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 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 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
863 (integer)
864 Per-table value for vacuum_freeze_table_age parameter.
865 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 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 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 log_autovacuum_min_duration, toast.log_autovacuum_min_duration
885 (integer)
886 Per-table value for log_autovacuum_min_duration parameter.
887 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
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 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 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 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 A table cannot have more than 1600 columns. (In practice, the effective
919 limit is usually lower because of tuple-length constraints.)
920
922 Create table films and table distributors:
923 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 CREATE TABLE distributors (
934 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
935 name varchar(40) NOT NULL CHECK (name <> '')
936 );
937 Create a table with a 2-dimensional array:
939 CREATE TABLE array_int (
941 vector int[][]
942 );
943 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 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 Define a check column constraint:
958 CREATE TABLE distributors (
960 did integer CHECK (did > 100),
961 name varchar(40)
962 );
963 Define a check table constraint:
965 CREATE TABLE distributors (
967 did integer,
968 name varchar(40),
969 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
970 );
971 Define a primary key table constraint for the table films:
973 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 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 CREATE TABLE distributors (
989 did integer,
990 name varchar(40),
991 PRIMARY KEY(did)
992 );
993 CREATE TABLE distributors (
995 did integer PRIMARY KEY,
996 name varchar(40)
997 );
998 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 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 Define two NOT NULL column constraints on the table distributors, one
1011 of which is explicitly given a name:
1012 CREATE TABLE distributors (
1014 did integer CONSTRAINT no_null NOT NULL,
1015 name varchar(40) NOT NULL
1016 );
1017 Define a unique constraint for the name column:
1019 CREATE TABLE distributors (
1021 did integer,
1022 name varchar(40) UNIQUE
1023 );
1024 The same, specified as a table constraint:
1026 CREATE TABLE distributors (
1028 did integer,
1029 name varchar(40),
1030 UNIQUE(name)
1031 );
1032 Create the same table, specifying 70% fill factor for both the table
1034 and its unique index:
1035 CREATE TABLE distributors (
1037 did integer,
1038 name varchar(40),
1039 UNIQUE(name) WITH (fillfactor=70)
1040 )
1041 WITH (fillfactor=70);
1042 Create table circles with an exclusion constraint that prevents any two
1044 circles from overlapping:
1045 CREATE TABLE circles (
1047 c circle,
1048 EXCLUDE USING gist (c WITH &&)
1049 );
1050 Create table cinemas in tablespace diskvol1:
1052 CREATE TABLE cinemas (
1054 id serial,
1055 name text,
1056 location text
1057 ) TABLESPACE diskvol1;
1058 Create a composite type and a typed table:
1060 CREATE TYPE employee_type AS (name text, salary numeric);
1062 CREATE TABLE employees OF employee_type (
1064 PRIMARY KEY (name),
1065 salary WITH OPTIONS DEFAULT 1000
1066 );
1067 Create a range partitioned table:
1069 CREATE TABLE measurement (
1071 logdate date not null,
1072 peaktemp int,
1073 unitsales int
1074 ) PARTITION BY RANGE (logdate);
1075 Create a range partitioned table with multiple columns in the partition
1077 key:
1078 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 Create a list partitioned table:
1086 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 Create a hash partitioned table:
1094 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 Create partition of a range partitioned table:
1102 CREATE TABLE measurement_y2016m07
1104 PARTITION OF measurement (
1105 unitsales DEFAULT 0
1106 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1107 Create a few partitions of a range partitioned table with multiple
1109 columns in the partition key:
1110 CREATE TABLE measurement_ym_older
1112 PARTITION OF measurement_year_month
1113 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1114 CREATE TABLE measurement_ym_y2016m11
1116 PARTITION OF measurement_year_month
1117 FOR VALUES FROM (2016, 11) TO (2016, 12);
1118 CREATE TABLE measurement_ym_y2016m12
1120 PARTITION OF measurement_year_month
1121 FOR VALUES FROM (2016, 12) TO (2017, 01);
1122 CREATE TABLE measurement_ym_y2017m01
1124 PARTITION OF measurement_year_month
1125 FOR VALUES FROM (2017, 01) TO (2017, 02);
1126 Create partition of a list partitioned table:
1128 CREATE TABLE cities_ab
1130 PARTITION OF cities (
1131 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1132 ) FOR VALUES IN ('a', 'b');
1133 Create partition of a list partitioned table that is itself further
1135 partitioned and then add a partition to it:
1136 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 CREATE TABLE cities_ab_10000_to_100000
1143 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1144 Create partitions of a hash partitioned table:
1146 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 Create a default partition:
1157 CREATE TABLE cities_partdef
1159 PARTITION OF cities DEFAULT;
1160
1162 The CREATE TABLE command conforms to the SQL standard, with exceptions
1163 listed below.
1164 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 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 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 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 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 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 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 EXCLUDE Constraint
1215 The EXCLUDE constraint type is a PostgreSQL extension.
1216 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 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 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 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 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 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 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 WITH Clause
1267 The WITH clause is a PostgreSQL extension; neither storage parameters
1268 nor OIDs are in the standard.
1269 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 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 PARTITION BY Clause
1284 The PARTITION BY clause is a PostgreSQL extension.
1285 PARTITION OF Clause
1287 The PARTITION OF clause is a PostgreSQL extension.
1288
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))
1293PostgreSQL 11.6 2019 CREATE TABLE(7)