1CREATE TABLE(7) PostgreSQL 11.3 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. This
263 option is not available for hash-partitioned tables.
264 The partition_bound_spec must correspond to the partitioning method
266 and partition key of the parent table, and must not overlap with
267 any existing partition of that parent. The form with IN is used for
268 list partitioning, the form with FROM and TO is used for range
269 partitioning, and the form with WITH is used for hash partitioning.
270 Each of the values specified in the partition_bound_spec is a
272 literal, NULL, MINVALUE, or MAXVALUE. Each literal value must be
273 either a numeric constant that is coercible to the corresponding
274 partition key column's type, or a string literal that is valid
275 input for that type.
276 When creating a list partition, NULL can be specified to signify
278 that the partition allows the partition key column to be null.
279 However, there cannot be more than one such list partition for a
280 given parent table. NULL cannot be specified for range partitions.
281 When creating a range partition, the lower bound specified with
283 FROM is an inclusive bound, whereas the upper bound specified with
284 TO is an exclusive bound. That is, the values specified in the FROM
285 list are valid values of the corresponding partition key columns
286 for this partition, whereas those in the TO list are not. Note that
287 this statement must be understood according to the rules of
288 row-wise comparison (Section 9.23.5). For example, given PARTITION
289 BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1
290 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
291 The special values MINVALUE and MAXVALUE may be used when creating
293 a range partition to indicate that there is no lower or upper bound
294 on the column's value. For example, a partition defined using FROM
295 (MINVALUE) TO (10) allows any values less than 10, and a partition
296 defined using FROM (10) TO (MAXVALUE) allows any values greater
297 than or equal to 10.
298 When creating a range partition involving more than one column, it
300 can also make sense to use MAXVALUE as part of the lower bound, and
301 MINVALUE as part of the upper bound. For example, a partition
302 defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows
303 where the first partition key column is greater than 0 and less
304 than or equal to 10. Similarly, a partition defined using FROM
305 ('a', MINVALUE) TO ('b', MINVALUE) allows any rows where the first
306 partition key column starts with "a".
307 Note that if MINVALUE or MAXVALUE is used for one column of a
309 partitioning bound, the same value must be used for all subsequent
310 columns. For example, (10, MINVALUE, 0) is not a valid bound; you
311 should write (10, MINVALUE, MINVALUE).
312 Also note that some element types, such as timestamp, have a notion
314 of "infinity", which is just another value that can be stored. This
315 is different from MINVALUE and MAXVALUE, which are not real values
316 that can be stored, but rather they are ways of saying that the
317 value is unbounded. MAXVALUE can be thought of as being greater
318 than any other value, including "infinity" and MINVALUE as being
319 less than any other value, including "minus infinity". Thus the
320 range FROM ('infinity') TO (MAXVALUE) is not an empty range; it
321 allows precisely one value to be stored — "infinity".
322 If DEFAULT is specified, the table will be created as a default
324 partition of the parent table. The parent can either be a list or
325 range partitioned table. A partition key value not fitting into any
326 other partition of the given parent will be routed to the default
327 partition. There can be only one default partition for a given
328 parent table.
329 When a table has an existing DEFAULT partition and a new partition
331 is added to it, the existing default partition must be scanned to
332 verify that it does not contain any rows which properly belong in
333 the new partition. If the default partition contains a large number
334 of rows, this may be slow. The scan will be skipped if the default
335 partition is a foreign table or if it has a constraint which proves
336 that it cannot contain rows which should be placed in the new
337 partition.
338 When creating a hash partition, a modulus and remainder must be
340 specified. The modulus must be a positive integer, and the
341 remainder must be a non-negative integer less than the modulus.
342 Typically, when initially setting up a hash-partitioned table, you
343 should choose a modulus equal to the number of partitions and
344 assign every table the same modulus and a different remainder (see
345 examples, below). However, it is not required that every partition
346 have the same modulus, only that every modulus which occurs among
347 the partitions of a hash-partitioned table is a factor of the next
348 larger modulus. This allows the number of partitions to be
349 increased incrementally without needing to move all the data at
350 once. For example, suppose you have a hash-partitioned table with 8
351 partitions, each of which has modulus 8, but find it necessary to
352 increase the number of partitions to 16. You can detach one of the
353 modulus-8 partitions, create two new modulus-16 partitions covering
354 the same portion of the key space (one with a remainder equal to
355 the remainder of the detached partition, and the other with a
356 remainder equal to that value plus 8), and repopulate them with
357 data. You can then repeat this -- perhaps at a later time -- for
358 each modulus-8 partition until none remain. While this may still
359 involve a large amount of data movement at each step, it is still
360 better than having to create a whole new table and move all the
361 data at once.
362 A partition must have the same column names and types as the
364 partitioned table to which it belongs. If the parent is specified
365 WITH OIDS then all partitions must have OIDs; the parent's OID
366 column will be inherited by all partitions just like any other
367 column. Modifications to the column names or types of a partitioned
368 table, or the addition or removal of an OID column, will
369 automatically propagate to all partitions. CHECK constraints will
370 be inherited automatically by every partition, but an individual
371 partition may specify additional CHECK constraints; additional
372 constraints with the same name and condition as in the parent will
373 be merged with the parent constraint. Defaults may be specified
374 separately for each partition.
375 Rows inserted into a partitioned table will be automatically routed
377 to the correct partition. If no suitable partition exists, an error
378 will occur.
379 Operations such as TRUNCATE which normally affect a table and all
381 of its inheritance children will cascade to all partitions, but may
382 also be performed on an individual partition. Note that dropping a
383 partition with DROP TABLE requires taking an ACCESS EXCLUSIVE lock
384 on the parent table.
385 LIKE source_table [ like_option ... ]
387 The LIKE clause specifies a table from which the new table
388 automatically copies all column names, their data types, and their
389 not-null constraints.
390 Unlike INHERITS, the new table and original table are completely
392 decoupled after creation is complete. Changes to the original table
393 will not be applied to the new table, and it is not possible to
394 include data of the new table in scans of the original table.
395 Default expressions for the copied column definitions will be
397 copied only if INCLUDING DEFAULTS is specified. The default
398 behavior is to exclude default expressions, resulting in the copied
399 columns in the new table having null defaults. Note that copying
400 defaults that call database-modification functions, such as
401 nextval, may create a functional linkage between the original and
402 new tables.
403 Any identity specifications of copied column definitions will only
405 be copied if INCLUDING IDENTITY is specified. A new sequence is
406 created for each identity column of the new table, separate from
407 the sequences associated with the old table.
408 Not-null constraints are always copied to the new table. CHECK
410 constraints will be copied only if INCLUDING CONSTRAINTS is
411 specified. No distinction is made between column constraints and
412 table constraints.
413 Extended statistics are copied to the new table if INCLUDING
415 STATISTICS is specified.
416 Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
418 original table will be created on the new table only if INCLUDING
419 INDEXES is specified. Names for the new indexes and constraints are
420 chosen according to the default rules, regardless of how the
421 originals were named. (This behavior avoids possible duplicate-name
422 failures for the new indexes.)
423 STORAGE settings for the copied column definitions will be copied
425 only if INCLUDING STORAGE is specified. The default behavior is to
426 exclude STORAGE settings, resulting in the copied columns in the
427 new table having type-specific default settings. For more on
428 STORAGE settings, see Section 68.2.
429 Comments for the copied columns, constraints, and indexes will be
431 copied only if INCLUDING COMMENTS is specified. The default
432 behavior is to exclude comments, resulting in the copied columns
433 and constraints in the new table having no comments.
434 INCLUDING ALL is an abbreviated form of INCLUDING COMMENTS
436 INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING IDENTITY
437 INCLUDING INDEXES INCLUDING STATISTICS INCLUDING STORAGE.
438 Note that unlike INHERITS, columns and constraints copied by LIKE
440 are not merged with similarly named columns and constraints. If the
441 same name is specified explicitly or in another LIKE clause, an
442 error is signaled.
443 The LIKE clause can also be used to copy column definitions from
445 views, foreign tables, or composite types. Inapplicable options
446 (e.g., INCLUDING INDEXES from a view) are ignored.
447 CONSTRAINT constraint_name
449 An optional name for a column or table constraint. If the
450 constraint is violated, the constraint name is present in error
451 messages, so constraint names like col must be positive can be used
452 to communicate helpful constraint information to client
453 applications. (Double-quotes are needed to specify constraint names
454 that contain spaces.) If a constraint name is not specified, the
455 system generates a name.
456 NOT NULL
458 The column is not allowed to contain null values.
459 NULL
461 The column is allowed to contain null values. This is the default.
462 This clause is only provided for compatibility with non-standard
464 SQL databases. Its use is discouraged in new applications.
465 CHECK ( expression ) [ NO INHERIT ]
467 The CHECK clause specifies an expression producing a Boolean result
468 which new or updated rows must satisfy for an insert or update
469 operation to succeed. Expressions evaluating to TRUE or UNKNOWN
470 succeed. Should any row of an insert or update operation produce a
471 FALSE result, an error exception is raised and the insert or update
472 does not alter the database. A check constraint specified as a
473 column constraint should reference that column's value only, while
474 an expression appearing in a table constraint can reference
475 multiple columns.
476 Currently, CHECK expressions cannot contain subqueries nor refer to
478 variables other than columns of the current row. The system column
479 tableoid may be referenced, but not any other system column.
480 A constraint marked with NO INHERIT will not propagate to child
482 tables.
483 When a table has multiple CHECK constraints, they will be tested
485 for each row in alphabetical order by name, after checking NOT NULL
486 constraints. (PostgreSQL versions before 9.5 did not honor any
487 particular firing order for CHECK constraints.)
488 DEFAULT default_expr
490 The DEFAULT clause assigns a default data value for the column
491 whose column definition it appears within. The value is any
492 variable-free expression (subqueries and cross-references to other
493 columns in the current table are not allowed). The data type of the
494 default expression must match the data type of the column.
495 The default expression will be used in any insert operation that
497 does not specify a value for the column. If there is no default for
498 a column, then the default is null.
499 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
501 This clause creates the column as an identity column. It will have
502 an implicit sequence attached to it and the column in new rows will
503 automatically have values from the sequence assigned to it.
504 The clauses ALWAYS and BY DEFAULT determine how the sequence value
506 is given precedence over a user-specified value in an INSERT
507 statement. If ALWAYS is specified, a user-specified value is only
508 accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE.
509 If BY DEFAULT is specified, then the user-specified value takes
510 precedence. See INSERT(7) for details. (In the COPY command,
511 user-specified values are always used regardless of this setting.)
512 The optional sequence_options clause can be used to override the
514 options of the sequence. See CREATE SEQUENCE (CREATE_SEQUENCE(7))
515 for details.
516 UNIQUE (column constraint)
518 UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
519 (table constraint)
520 The UNIQUE constraint specifies that a group of one or more columns
521 of a table can contain only unique values. The behavior of the
522 unique table constraint is the same as that for column constraints,
523 with the additional capability to span multiple columns.
524 For the purpose of a unique constraint, null values are not
526 considered equal.
527 Each unique table constraint must name a set of columns that is
529 different from the set of columns named by any other unique or
530 primary key constraint defined for the table. (Otherwise it would
531 just be the same constraint listed twice.)
532 When establishing a unique constraint for a multi-level partition
534 hierarchy, all the columns in the partition key of the target
535 partitioned table, as well as those of all its descendant
536 partitioned tables, must be included in the constraint definition.
537 Adding a unique constraint will automatically create a unique btree
539 index on the column or group of columns used in the constraint. The
540 optional clause INCLUDE adds to that index one or more columns on
541 which the uniqueness is not enforced. Note that although the
542 constraint is not enforced on the included columns, it still
543 depends on them. Consequently, some operations on these columns
544 (e.g. DROP COLUMN) can cause cascaded constraint and index
545 deletion.
546 PRIMARY KEY (column constraint)
548 PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
549 (table constraint)
550 The PRIMARY KEY constraint specifies that a column or columns of a
551 table can contain only unique (non-duplicate), nonnull values. Only
552 one primary key can be specified for a table, whether as a column
553 constraint or a table constraint.
554 The primary key constraint should name a set of columns that is
556 different from the set of columns named by any unique constraint
557 defined for the same table. (Otherwise, the unique constraint is
558 redundant and will be discarded.)
559 PRIMARY KEY enforces the same data constraints as a combination of
561 UNIQUE and NOT NULL, but identifying a set of columns as the
562 primary key also provides metadata about the design of the schema,
563 since a primary key implies that other tables can rely on this set
564 of columns as a unique identifier for rows.
565 PRIMARY KEY constraints share the restrictions that UNIQUE
567 constraints have when placed on partitioned tables.
568 Adding a PRIMARY KEY constraint will automatically create a unique
570 btree index on the column or group of columns used in the
571 constraint. The optional INCLUDE clause allows a list of columns to
572 be specified which will be included in the non-key portion of the
573 index. Although uniqueness is not enforced on the included columns,
574 the constraint still depends on them. Consequently, some operations
575 on the included columns (e.g. DROP COLUMN) can cause cascaded
576 constraint and index deletion.
577 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
579 ) index_parameters [ WHERE ( predicate ) ]
580 The EXCLUDE clause defines an exclusion constraint, which
581 guarantees that if any two rows are compared on the specified
582 column(s) or expression(s) using the specified operator(s), not all
583 of these comparisons will return TRUE. If all of the specified
584 operators test for equality, this is equivalent to a UNIQUE
585 constraint, although an ordinary unique constraint will be faster.
586 However, exclusion constraints can specify constraints that are
587 more general than simple equality. For example, you can specify a
588 constraint that no two rows in the table contain overlapping
589 circles (see Section 8.8) by using the && operator.
590 Exclusion constraints are implemented using an index, so each
592 specified operator must be associated with an appropriate operator
593 class (see Section 11.10) for the index access method index_method.
594 The operators are required to be commutative. Each exclude_element
595 can optionally specify an operator class and/or ordering options;
596 these are described fully under CREATE INDEX (CREATE_INDEX(7)).
597 The access method must support amgettuple (see Chapter 61); at
599 present this means GIN cannot be used. Although it's allowed, there
600 is little point in using B-tree or hash indexes with an exclusion
601 constraint, because this does nothing that an ordinary unique
602 constraint doesn't do better. So in practice the access method will
603 always be GiST or SP-GiST.
604 The predicate allows you to specify an exclusion constraint on a
606 subset of the table; internally this creates a partial index. Note
607 that parentheses are required around the predicate.
608 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
610 action ] [ ON UPDATE action ] (column constraint)
611 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
612 [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE
613 action ] (table constraint)
614 These clauses specify a foreign key constraint, which requires that
615 a group of one or more columns of the new table must only contain
616 values that match values in the referenced column(s) of some row of
617 the referenced table. If the refcolumn list is omitted, the primary
618 key of the reftable is used. The referenced columns must be the
619 columns of a non-deferrable unique or primary key constraint in the
620 referenced table. The user must have REFERENCES permission on the
621 referenced table (either the whole table, or the specific
622 referenced columns). The addition of a foreign key constraint
623 requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note
624 that foreign key constraints cannot be defined between temporary
625 tables and permanent tables. Also note that while it is possible to
626 define a foreign key on a partitioned table, it is not possible to
627 declare a foreign key that references a partitioned table.
628 A value inserted into the referencing column(s) is matched against
630 the values of the referenced table and referenced columns using the
631 given match type. There are three match types: MATCH FULL, MATCH
632 PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will
633 not allow one column of a multicolumn foreign key to be null unless
634 all foreign key columns are null; if they are all null, the row is
635 not required to have a match in the referenced table. MATCH SIMPLE
636 allows any of the foreign key columns to be null; if any of them
637 are null, the row is not required to have a match in the referenced
638 table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL
639 constraints can be applied to the referencing column(s) to prevent
640 these cases from arising.)
641 In addition, when the data in the referenced columns is changed,
643 certain actions are performed on the data in this table's columns.
644 The ON DELETE clause specifies the action to perform when a
645 referenced row in the referenced table is being deleted. Likewise,
646 the ON UPDATE clause specifies the action to perform when a
647 referenced column in the referenced table is being updated to a new
648 value. If the row is updated, but the referenced column is not
649 actually changed, no action is done. Referential actions other than
650 the NO ACTION check cannot be deferred, even if the constraint is
651 declared deferrable. There are the following possible actions for
652 each clause:
653 NO ACTION
655 Produce an error indicating that the deletion or update would
656 create a foreign key constraint violation. If the constraint is
657 deferred, this error will be produced at constraint check time
658 if there still exist any referencing rows. This is the default
659 action.
660 RESTRICT
662 Produce an error indicating that the deletion or update would
663 create a foreign key constraint violation. This is the same as
664 NO ACTION except that the check is not deferrable.
665 CASCADE
667 Delete any rows referencing the deleted row, or update the
668 values of the referencing column(s) to the new values of the
669 referenced columns, respectively.
670 SET NULL
672 Set the referencing column(s) to null.
673 SET DEFAULT
675 Set the referencing column(s) to their default values. (There
676 must be a row in the referenced table matching the default
677 values, if they are not null, or the operation will fail.)
678 If the referenced column(s) are changed frequently, it might be
680 wise to add an index to the referencing column(s) so that
681 referential actions associated with the foreign key constraint can
682 be performed more efficiently.
683 DEFERRABLE
685 NOT DEFERRABLE
686 This controls whether the constraint can be deferred. A constraint
687 that is not deferrable will be checked immediately after every
688 command. Checking of constraints that are deferrable can be
689 postponed until the end of the transaction (using the SET
690 CONSTRAINTS (SET_CONSTRAINTS(7)) command). NOT DEFERRABLE is the
691 default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and
692 REFERENCES (foreign key) constraints accept this clause. NOT NULL
693 and CHECK constraints are not deferrable. Note that deferrable
694 constraints cannot be used as conflict arbitrators in an INSERT
695 statement that includes an ON CONFLICT DO UPDATE clause.
696 INITIALLY IMMEDIATE
698 INITIALLY DEFERRED
699 If a constraint is deferrable, this clause specifies the default
700 time to check the constraint. If the constraint is INITIALLY
701 IMMEDIATE, it is checked after each statement. This is the default.
702 If the constraint is INITIALLY DEFERRED, it is checked only at the
703 end of the transaction. The constraint check time can be altered
704 with the SET CONSTRAINTS (SET_CONSTRAINTS(7)) command.
705 WITH ( storage_parameter [= value] [, ... ] )
707 This clause specifies optional storage parameters for a table or
708 index; see Storage Parameters for more information. The WITH clause
709 for a table can also include OIDS=TRUE (or just OIDS) to specify
710 that rows of the new table should have OIDs (object identifiers)
711 assigned to them, or OIDS=FALSE to specify that the rows should not
712 have OIDs. If OIDS is not specified, the default setting depends
713 upon the default_with_oids configuration parameter. (If the new
714 table inherits from any tables that have OIDs, then OIDS=TRUE is
715 forced even if the command says OIDS=FALSE.)
716 If OIDS=FALSE is specified or implied, the new table does not store
718 OIDs and no OID will be assigned for a row inserted into it. This
719 is generally considered worthwhile, since it will reduce OID
720 consumption and thereby postpone the wraparound of the 32-bit OID
721 counter. Once the counter wraps around, OIDs can no longer be
722 assumed to be unique, which makes them considerably less useful. In
723 addition, excluding OIDs from a table reduces the space required to
724 store the table on disk by 4 bytes per row (on most machines),
725 slightly improving performance.
726 To remove OIDs from a table after it has been created, use ALTER
728 TABLE (ALTER_TABLE(7)).
729 WITH OIDS
731 WITHOUT OIDS
732 These are obsolescent syntaxes equivalent to WITH (OIDS) and WITH
733 (OIDS=FALSE), respectively. If you wish to give both an OIDS
734 setting and storage parameters, you must use the WITH ( ... )
735 syntax; see above.
736 ON COMMIT
738 The behavior of temporary tables at the end of a transaction block
739 can be controlled using ON COMMIT. The three options are:
740 PRESERVE ROWS
742 No special action is taken at the ends of transactions. This is
743 the default behavior.
744 DELETE ROWS
746 All rows in the temporary table will be deleted at the end of
747 each transaction block. Essentially, an automatic TRUNCATE(7)
748 is done at each commit. When used on a partitioned table, this
749 is not cascaded to its partitions.
750 DROP
752 The temporary table will be dropped at the end of the current
753 transaction block. When used on a partitioned table, this
754 action drops its partitions and when used on tables with
755 inheritance children, it drops the dependent children.
756 TABLESPACE tablespace_name
758 The tablespace_name is the name of the tablespace in which the new
759 table is to be created. If not specified, default_tablespace is
760 consulted, or temp_tablespaces if the table is temporary.
761 USING INDEX TABLESPACE tablespace_name
763 This clause allows selection of the tablespace in which the index
764 associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
765 be created. If not specified, default_tablespace is consulted, or
766 temp_tablespaces if the table is temporary.
767 Storage Parameters
769 The WITH clause can specify storage parameters for tables, and for
770 indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
771 Storage parameters for indexes are documented in CREATE INDEX
772 (CREATE_INDEX(7)). The storage parameters currently available for
773 tables are listed below. For many of these parameters, as shown, there
774 is an additional parameter with the same name prefixed with toast.,
775 which controls the behavior of the table's secondary TOAST table, if
776 any (see Section 68.2 for more information about TOAST). If a table
777 parameter value is set and the equivalent toast. parameter is not, the
778 TOAST table will use the table's parameter value. Specifying these
779 parameters for partitioned tables is not supported, but you may specify
780 them for individual leaf partitions.
781 fillfactor (integer)
783 The fillfactor for a table is a percentage between 10 and 100. 100
784 (complete packing) is the default. When a smaller fillfactor is
785 specified, INSERT operations pack table pages only to the indicated
786 percentage; the remaining space on each page is reserved for
787 updating rows on that page. This gives UPDATE a chance to place the
788 updated copy of a row on the same page as the original, which is
789 more efficient than placing it on a different page. For a table
790 whose entries are never updated, complete packing is the best
791 choice, but in heavily updated tables smaller fillfactors are
792 appropriate. This parameter cannot be set for TOAST tables.
793 toast_tuple_target (integer)
795 The toast_tuple_target specifies the minimum tuple length required
796 before we try to move long column values into TOAST tables, and is
797 also the target length we try to reduce the length below once
798 toasting begins. This only affects columns marked as either
799 External or Extended and applies only to new tuples - there is no
800 effect on existing rows. By default this parameter is set to allow
801 at least 4 tuples per block, which with the default blocksize will
802 be 2040 bytes. Valid values are between 128 bytes and the
803 (blocksize - header), by default 8160 bytes. Changing this value
804 may not be useful for very short or very long rows. Note that the
805 default setting is often close to optimal, and it is possible that
806 setting this parameter could have negative effects in some cases.
807 This parameter cannot be set for TOAST tables.
808 parallel_workers (integer)
810 This sets the number of workers that should be used to assist a
811 parallel scan of this table. If not set, the system will determine
812 a value based on the relation size. The actual number of workers
813 chosen by the planner or by utility statements that use parallel
814 scans may be less, for example due to the setting of
815 max_worker_processes.
816 autovacuum_enabled, toast.autovacuum_enabled (boolean)
818 Enables or disables the autovacuum daemon for a particular table.
819 If true, the autovacuum daemon will perform automatic VACUUM and/or
820 ANALYZE operations on this table following the rules discussed in
821 Section 24.1.6. If false, this table will not be autovacuumed,
822 except to prevent transaction ID wraparound. See Section 24.1.5 for
823 more about wraparound prevention. Note that the autovacuum daemon
824 does not run at all (except to prevent transaction ID wraparound)
825 if the autovacuum parameter is false; setting individual tables'
826 storage parameters does not override that. Therefore there is
827 seldom much point in explicitly setting this storage parameter to
828 true, only to false.
829 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
831 (integer)
832 Per-table value for autovacuum_vacuum_threshold parameter.
833 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
835 (float4)
836 Per-table value for autovacuum_vacuum_scale_factor parameter.
837 autovacuum_analyze_threshold (integer)
839 Per-table value for autovacuum_analyze_threshold parameter.
840 autovacuum_analyze_scale_factor (float4)
842 Per-table value for autovacuum_analyze_scale_factor parameter.
843 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
845 (integer)
846 Per-table value for autovacuum_vacuum_cost_delay parameter.
847 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
849 (integer)
850 Per-table value for autovacuum_vacuum_cost_limit parameter.
851 autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
853 Per-table value for vacuum_freeze_min_age parameter. Note that
854 autovacuum will ignore per-table autovacuum_freeze_min_age
855 parameters that are larger than half the system-wide
856 autovacuum_freeze_max_age setting.
857 autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
859 Per-table value for autovacuum_freeze_max_age parameter. Note that
860 autovacuum will ignore per-table autovacuum_freeze_max_age
861 parameters that are larger than the system-wide setting (it can
862 only be set smaller).
863 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
865 (integer)
866 Per-table value for vacuum_freeze_table_age parameter.
867 autovacuum_multixact_freeze_min_age,
869 toast.autovacuum_multixact_freeze_min_age (integer)
870 Per-table value for vacuum_multixact_freeze_min_age parameter. Note
871 that autovacuum will ignore per-table
872 autovacuum_multixact_freeze_min_age parameters that are larger than
873 half the system-wide autovacuum_multixact_freeze_max_age setting.
874 autovacuum_multixact_freeze_max_age,
876 toast.autovacuum_multixact_freeze_max_age (integer)
877 Per-table value for autovacuum_multixact_freeze_max_age parameter.
878 Note that autovacuum will ignore per-table
879 autovacuum_multixact_freeze_max_age parameters that are larger than
880 the system-wide setting (it can only be set smaller).
881 autovacuum_multixact_freeze_table_age,
883 toast.autovacuum_multixact_freeze_table_age (integer)
884 Per-table value for vacuum_multixact_freeze_table_age parameter.
885 log_autovacuum_min_duration, toast.log_autovacuum_min_duration
887 (integer)
888 Per-table value for log_autovacuum_min_duration parameter.
889 user_catalog_table (boolean)
891 Declare the table as an additional catalog table for purposes of
892 logical replication. See Section 49.6.2 for details. This parameter
893 cannot be set for TOAST tables.
894
896 Using OIDs in new applications is not recommended: where possible,
897 using an identity column or other sequence generator as the table's
898 primary key is preferred. However, if your application does make use of
899 OIDs to identify specific rows of a table, it is recommended to create
900 a unique constraint on the oid column of that table, to ensure that
901 OIDs in the table will indeed uniquely identify rows even after counter
902 wraparound. Avoid assuming that OIDs are unique across tables; if you
903 need a database-wide unique identifier, use the combination of tableoid
904 and row OID for the purpose.
905 Tip
907 The use of OIDS=FALSE is not recommended for tables with no primary
908 key, since without either an OID or a unique data key, it is
909 difficult to identify specific rows.
910 PostgreSQL automatically creates an index for each unique constraint
912 and primary key constraint to enforce uniqueness. Thus, it is not
913 necessary to create an index explicitly for primary key columns. (See
914 CREATE INDEX (CREATE_INDEX(7)) for more information.)
915 Unique constraints and primary keys are not inherited in the current
917 implementation. This makes the combination of inheritance and unique
918 constraints rather dysfunctional.
919 A table cannot have more than 1600 columns. (In practice, the effective
921 limit is usually lower because of tuple-length constraints.)
922
924 Create table films and table distributors:
925 CREATE TABLE films (
927 code char(5) CONSTRAINT firstkey PRIMARY KEY,
928 title varchar(40) NOT NULL,
929 did integer NOT NULL,
930 date_prod date,
931 kind varchar(10),
932 len interval hour to minute
933 );
934 CREATE TABLE distributors (
936 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
937 name varchar(40) NOT NULL CHECK (name <> '')
938 );
939 Create a table with a 2-dimensional array:
941 CREATE TABLE array_int (
943 vector int[][]
944 );
945 Define a unique table constraint for the table films. Unique table
947 constraints can be defined on one or more columns of the table:
948 CREATE TABLE films (
950 code char(5),
951 title varchar(40),
952 did integer,
953 date_prod date,
954 kind varchar(10),
955 len interval hour to minute,
956 CONSTRAINT production UNIQUE(date_prod)
957 );
958 Define a check column constraint:
960 CREATE TABLE distributors (
962 did integer CHECK (did > 100),
963 name varchar(40)
964 );
965 Define a check table constraint:
967 CREATE TABLE distributors (
969 did integer,
970 name varchar(40),
971 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
972 );
973 Define a primary key table constraint for the table films:
975 CREATE TABLE films (
977 code char(5),
978 title varchar(40),
979 did integer,
980 date_prod date,
981 kind varchar(10),
982 len interval hour to minute,
983 CONSTRAINT code_title PRIMARY KEY(code,title)
984 );
985 Define a primary key constraint for table distributors. The following
987 two examples are equivalent, the first using the table constraint
988 syntax, the second the column constraint syntax:
989 CREATE TABLE distributors (
991 did integer,
992 name varchar(40),
993 PRIMARY KEY(did)
994 );
995 CREATE TABLE distributors (
997 did integer PRIMARY KEY,
998 name varchar(40)
999 );
1000 Assign a literal constant default value for the column name, arrange
1002 for the default value of column did to be generated by selecting the
1003 next value of a sequence object, and make the default value of modtime
1004 be the time at which the row is inserted:
1005 CREATE TABLE distributors (
1007 name varchar(40) DEFAULT 'Luso Films',
1008 did integer DEFAULT nextval('distributors_serial'),
1009 modtime timestamp DEFAULT current_timestamp
1010 );
1011 Define two NOT NULL column constraints on the table distributors, one
1013 of which is explicitly given a name:
1014 CREATE TABLE distributors (
1016 did integer CONSTRAINT no_null NOT NULL,
1017 name varchar(40) NOT NULL
1018 );
1019 Define a unique constraint for the name column:
1021 CREATE TABLE distributors (
1023 did integer,
1024 name varchar(40) UNIQUE
1025 );
1026 The same, specified as a table constraint:
1028 CREATE TABLE distributors (
1030 did integer,
1031 name varchar(40),
1032 UNIQUE(name)
1033 );
1034 Create the same table, specifying 70% fill factor for both the table
1036 and its unique index:
1037 CREATE TABLE distributors (
1039 did integer,
1040 name varchar(40),
1041 UNIQUE(name) WITH (fillfactor=70)
1042 )
1043 WITH (fillfactor=70);
1044 Create table circles with an exclusion constraint that prevents any two
1046 circles from overlapping:
1047 CREATE TABLE circles (
1049 c circle,
1050 EXCLUDE USING gist (c WITH &&)
1051 );
1052 Create table cinemas in tablespace diskvol1:
1054 CREATE TABLE cinemas (
1056 id serial,
1057 name text,
1058 location text
1059 ) TABLESPACE diskvol1;
1060 Create a composite type and a typed table:
1062 CREATE TYPE employee_type AS (name text, salary numeric);
1064 CREATE TABLE employees OF employee_type (
1066 PRIMARY KEY (name),
1067 salary WITH OPTIONS DEFAULT 1000
1068 );
1069 Create a range partitioned table:
1071 CREATE TABLE measurement (
1073 logdate date not null,
1074 peaktemp int,
1075 unitsales int
1076 ) PARTITION BY RANGE (logdate);
1077 Create a range partitioned table with multiple columns in the partition
1079 key:
1080 CREATE TABLE measurement_year_month (
1082 logdate date not null,
1083 peaktemp int,
1084 unitsales int
1085 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1086 Create a list partitioned table:
1088 CREATE TABLE cities (
1090 city_id bigserial not null,
1091 name text not null,
1092 population bigint
1093 ) PARTITION BY LIST (left(lower(name), 1));
1094 Create a hash partitioned table:
1096 CREATE TABLE orders (
1098 order_id bigint not null,
1099 cust_id bigint not null,
1100 status text
1101 ) PARTITION BY HASH (order_id);
1102 Create partition of a range partitioned table:
1104 CREATE TABLE measurement_y2016m07
1106 PARTITION OF measurement (
1107 unitsales DEFAULT 0
1108 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1109 Create a few partitions of a range partitioned table with multiple
1111 columns in the partition key:
1112 CREATE TABLE measurement_ym_older
1114 PARTITION OF measurement_year_month
1115 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1116 CREATE TABLE measurement_ym_y2016m11
1118 PARTITION OF measurement_year_month
1119 FOR VALUES FROM (2016, 11) TO (2016, 12);
1120 CREATE TABLE measurement_ym_y2016m12
1122 PARTITION OF measurement_year_month
1123 FOR VALUES FROM (2016, 12) TO (2017, 01);
1124 CREATE TABLE measurement_ym_y2017m01
1126 PARTITION OF measurement_year_month
1127 FOR VALUES FROM (2017, 01) TO (2017, 02);
1128 Create partition of a list partitioned table:
1130 CREATE TABLE cities_ab
1132 PARTITION OF cities (
1133 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1134 ) FOR VALUES IN ('a', 'b');
1135 Create partition of a list partitioned table that is itself further
1137 partitioned and then add a partition to it:
1138 CREATE TABLE cities_ab
1140 PARTITION OF cities (
1141 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1142 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1143 CREATE TABLE cities_ab_10000_to_100000
1145 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1146 Create partitions of a hash partitioned table:
1148 CREATE TABLE orders_p1 PARTITION OF orders
1150 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1151 CREATE TABLE orders_p2 PARTITION OF orders
1152 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1153 CREATE TABLE orders_p3 PARTITION OF orders
1154 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1155 CREATE TABLE orders_p4 PARTITION OF orders
1156 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1157 Create a default partition:
1159 CREATE TABLE cities_partdef
1161 PARTITION OF cities DEFAULT;
1162
1164 The CREATE TABLE command conforms to the SQL standard, with exceptions
1165 listed below.
1166 Temporary Tables
1168 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1169 standard, the effect is not the same. In the standard, temporary tables
1170 are defined just once and automatically exist (starting with empty
1171 contents) in every session that needs them. PostgreSQL instead
1172 requires each session to issue its own CREATE TEMPORARY TABLE command
1173 for each temporary table to be used. This allows different sessions to
1174 use the same temporary table name for different purposes, whereas the
1175 standard's approach constrains all instances of a given temporary table
1176 name to have the same table structure.
1177 The standard's definition of the behavior of temporary tables is widely
1179 ignored. PostgreSQL's behavior on this point is similar to that of
1180 several other SQL databases.
1181 The SQL standard also distinguishes between global and local temporary
1183 tables, where a local temporary table has a separate set of contents
1184 for each SQL module within each session, though its definition is still
1185 shared across sessions. Since PostgreSQL does not support SQL modules,
1186 this distinction is not relevant in PostgreSQL.
1187 For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1189 keywords in a temporary table declaration, but they currently have no
1190 effect. Use of these keywords is discouraged, since future versions of
1191 PostgreSQL might adopt a more standard-compliant interpretation of
1192 their meaning.
1193 The ON COMMIT clause for temporary tables also resembles the SQL
1195 standard, but has some differences. If the ON COMMIT clause is omitted,
1196 SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1197 However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1198 The ON COMMIT DROP option does not exist in SQL.
1199 Non-deferred Uniqueness Constraints
1201 When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1202 checks for uniqueness immediately whenever a row is inserted or
1203 modified. The SQL standard says that uniqueness should be enforced only
1204 at the end of the statement; this makes a difference when, for example,
1205 a single command updates multiple key values. To obtain
1206 standard-compliant behavior, declare the constraint as DEFERRABLE but
1207 not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1208 significantly slower than immediate uniqueness checking.
1209 Column Check Constraints
1211 The SQL standard says that CHECK column constraints can only refer to
1212 the column they apply to; only CHECK table constraints can refer to
1213 multiple columns. PostgreSQL does not enforce this restriction; it
1214 treats column and table check constraints alike.
1215 EXCLUDE Constraint
1217 The EXCLUDE constraint type is a PostgreSQL extension.
1218 NULL “Constraint”
1220 The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1221 extension to the SQL standard that is included for compatibility with
1222 some other database systems (and for symmetry with the NOT NULL
1223 constraint). Since it is the default for any column, its presence is
1224 simply noise.
1225 Constraint Naming
1227 The SQL standard says that table and domain constraints must have names
1228 that are unique across the schema containing the table or domain.
1229 PostgreSQL is laxer: it only requires constraint names to be unique
1230 across the constraints attached to a particular table or domain.
1231 However, this extra freedom does not exist for index-based constraints
1232 (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1233 index is named the same as the constraint, and index names must be
1234 unique across all relations within the same schema.
1235 Currently, PostgreSQL does not record names for NOT NULL constraints at
1237 all, so they are not subject to the uniqueness restriction. This might
1238 change in a future release.
1239 Inheritance
1241 Multiple inheritance via the INHERITS clause is a PostgreSQL language
1242 extension. SQL:1999 and later define single inheritance using a
1243 different syntax and different semantics. SQL:1999-style inheritance is
1244 not yet supported by PostgreSQL.
1245 Zero-column Tables
1247 PostgreSQL allows a table of no columns to be created (for example,
1248 CREATE TABLE foo();). This is an extension from the SQL standard, which
1249 does not allow zero-column tables. Zero-column tables are not in
1250 themselves very useful, but disallowing them creates odd special cases
1251 for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1252 restriction.
1253 Multiple Identity Columns
1255 PostgreSQL allows a table to have more than one identity column. The
1256 standard specifies that a table can have at most one identity column.
1257 This is relaxed mainly to give more flexibility for doing schema
1258 changes or migrations. Note that the INSERT command supports only one
1259 override clause that applies to the entire statement, so having
1260 multiple identity columns with different behaviors is not well
1261 supported.
1262 LIKE Clause
1264 While a LIKE clause exists in the SQL standard, many of the options
1265 that PostgreSQL accepts for it are not in the standard, and some of the
1266 standard's options are not implemented by PostgreSQL.
1267 WITH Clause
1269 The WITH clause is a PostgreSQL extension; neither storage parameters
1270 nor OIDs are in the standard.
1271 Tablespaces
1273 The PostgreSQL concept of tablespaces is not part of the standard.
1274 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1275 extensions.
1276 Typed Tables
1278 Typed tables implement a subset of the SQL standard. According to the
1279 standard, a typed table has columns corresponding to the underlying
1280 composite type as well as one other column that is the
1281 “self-referencing column”. PostgreSQL does not support these
1282 self-referencing columns explicitly, but the same effect can be had
1283 using the OID feature.
1284 PARTITION BY Clause
1286 The PARTITION BY clause is a PostgreSQL extension.
1287 PARTITION OF Clause
1289 The PARTITION OF clause is a PostgreSQL extension.
1290
1292 ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1293 AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1294 CREATE TYPE (CREATE_TYPE(7))
1295PostgreSQL 11.3 2019 CREATE TABLE(7)