1CREATE TABLE(7) PostgreSQL 12.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 [ USING method ]
18 [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
19 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
20 [ TABLESPACE tablespace_name ]
21 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
23 OF type_name [ (
24 { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
25 | table_constraint }
26 [, ... ]
27 ) ]
28 [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
29 [ USING method ]
30 [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
31 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
32 [ TABLESPACE tablespace_name ]
33 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
35 PARTITION OF parent_table [ (
36 { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
37 | table_constraint }
38 [, ... ]
39 ) ] { FOR VALUES partition_bound_spec | DEFAULT }
40 [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
41 [ USING method ]
42 [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
43 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
44 [ TABLESPACE tablespace_name ]
45 where column_constraint is:
47 [ CONSTRAINT constraint_name ]
49 { NOT NULL |
50 NULL |
51 CHECK ( expression ) [ NO INHERIT ] |
52 DEFAULT default_expr |
53 GENERATED ALWAYS AS ( generation_expr ) STORED |
54 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
55 UNIQUE index_parameters |
56 PRIMARY KEY index_parameters |
57 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
58 [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
59 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
60 and table_constraint is:
62 [ CONSTRAINT constraint_name ]
64 { CHECK ( expression ) [ NO INHERIT ] |
65 UNIQUE ( column_name [, ... ] ) index_parameters |
66 PRIMARY KEY ( column_name [, ... ] ) index_parameters |
67 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
68 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
69 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
70 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
71 and like_option is:
73 { INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
75 and partition_bound_spec is:
77 IN ( partition_bound_expr [, ...] ) |
79 FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
80 TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
81 WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
82 index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
84 [ INCLUDE ( column_name [, ... ] ) ]
86 [ WITH ( storage_parameter [= value] [, ... ] ) ]
87 [ USING INDEX TABLESPACE tablespace_name ]
88 exclude_element in an EXCLUDE constraint is:
90 { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
92
94 CREATE TABLE will create a new, initially empty table in the current
95 database. The table will be owned by the user issuing the command.
96 If a schema name is given (for example, CREATE TABLE myschema.mytable
98 ...) then the table is created in the specified schema. Otherwise it is
99 created in the current schema. Temporary tables exist in a special
100 schema, so a schema name cannot be given when creating a temporary
101 table. The name of the table must be distinct from the name of any
102 other table, sequence, index, view, or foreign table in the same
103 schema.
104 CREATE TABLE also automatically creates a data type that represents the
106 composite type corresponding to one row of the table. Therefore, tables
107 cannot have the same name as any existing data type in the same schema.
108 The optional constraint clauses specify constraints (tests) that new or
110 updated rows must satisfy for an insert or update operation to succeed.
111 A constraint is an SQL object that helps define the set of valid values
112 in the table in various ways.
113 There are two ways to define constraints: table constraints and column
115 constraints. A column constraint is defined as part of a column
116 definition. A table constraint definition is not tied to a particular
117 column, and it can encompass more than one column. Every column
118 constraint can also be written as a table constraint; a column
119 constraint is only a notational convenience for use when the constraint
120 only affects one column.
121 To be able to create a table, you must have USAGE privilege on all
123 column types or the type in the OF clause, respectively.
124
126 TEMPORARY or TEMP
127 If specified, the table is created as a temporary table. Temporary
128 tables are automatically dropped at the end of a session, or
129 optionally at the end of the current transaction (see ON COMMIT
130 below). Existing permanent tables with the same name are not
131 visible to the current session while the temporary table exists,
132 unless they are referenced with schema-qualified names. Any indexes
133 created on a temporary table are automatically temporary as well.
134 The autovacuum daemon cannot access and therefore cannot vacuum or
136 analyze temporary tables. For this reason, appropriate vacuum and
137 analyze operations should be performed via session SQL commands.
138 For example, if a temporary table is going to be used in complex
139 queries, it is wise to run ANALYZE on the temporary table after it
140 is populated.
141 Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
143 TEMP. This presently makes no difference in PostgreSQL and is
144 deprecated; see COMPATIBILITY.
145 UNLOGGED
147 If specified, the table is created as an unlogged table. Data
148 written to unlogged tables is not written to the write-ahead log
149 (see Chapter 29), which makes them considerably faster than
150 ordinary tables. However, they are not crash-safe: an unlogged
151 table is automatically truncated after a crash or unclean shutdown.
152 The contents of an unlogged table are also not replicated to
153 standby servers. Any indexes created on an unlogged table are
154 automatically unlogged as well.
155 IF NOT EXISTS
157 Do not throw an error if a relation with the same name already
158 exists. A notice is issued in this case. Note that there is no
159 guarantee that the existing relation is anything like the one that
160 would have been created.
161 table_name
163 The name (optionally schema-qualified) of the table to be created.
164 OF type_name
166 Creates a typed table, which takes its structure from the specified
167 composite type (name optionally schema-qualified). A typed table is
168 tied to its type; for example the table will be dropped if the type
169 is dropped (with DROP TYPE ... CASCADE).
170 When a typed table is created, then the data types of the columns
172 are determined by the underlying composite type and are not
173 specified by the CREATE TABLE command. But the CREATE TABLE command
174 can add defaults and constraints to the table and can specify
175 storage parameters.
176 column_name
178 The name of a column to be created in the new table.
179 data_type
181 The data type of the column. This can include array specifiers. For
182 more information on the data types supported by PostgreSQL, refer
183 to Chapter 8.
184 COLLATE collation
186 The COLLATE clause assigns a collation to the column (which must be
187 of a collatable data type). If not specified, the column data
188 type's default collation is used.
189 INHERITS ( parent_table [, ... ] )
191 The optional INHERITS clause specifies a list of tables from which
192 the new table automatically inherits all columns. Parent tables can
193 be plain tables or foreign tables.
194 Use of INHERITS creates a persistent relationship between the new
196 child table and its parent table(s). Schema modifications to the
197 parent(s) normally propagate to children as well, and by default
198 the data of the child table is included in scans of the parent(s).
199 If the same column name exists in more than one parent table, an
201 error is reported unless the data types of the columns match in
202 each of the parent tables. If there is no conflict, then the
203 duplicate columns are merged to form a single column in the new
204 table. If the column name list of the new table contains a column
205 name that is also inherited, the data type must likewise match the
206 inherited column(s), and the column definitions are merged into
207 one. If the new table explicitly specifies a default value for the
208 column, this default overrides any defaults from inherited
209 declarations of the column. Otherwise, any parents that specify
210 default values for the column must all specify the same default, or
211 an error will be reported.
212 CHECK constraints are merged in essentially the same way as
214 columns: if multiple parent tables and/or the new table definition
215 contain identically-named CHECK constraints, these constraints must
216 all have the same check expression, or an error will be reported.
217 Constraints having the same name and expression will be merged into
218 one copy. A constraint marked NO INHERIT in a parent will not be
219 considered. Notice that an unnamed CHECK constraint in the new
220 table will never be merged, since a unique name will always be
221 chosen for it.
222 Column STORAGE settings are also copied from parent tables.
224 If a column in the parent table is an identity column, that
226 property is not inherited. A column in the child table can be
227 declared identity column if desired.
228 PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
230 [ opclass ] [, ...] )
231 The optional PARTITION BY clause specifies a strategy of
232 partitioning the table. The table thus created is called a
233 partitioned table. The parenthesized list of columns or expressions
234 forms the partition key for the table. When using range or hash
235 partitioning, the partition key can include multiple columns or
236 expressions (up to 32, but this limit can be altered when building
237 PostgreSQL), but for list partitioning, the partition key must
238 consist of a single column or expression.
239 Range and list partitioning require a btree operator class, while
241 hash partitioning requires a hash operator class. If no operator
242 class is specified explicitly, the default operator class of the
243 appropriate type will be used; if no default operator class exists,
244 an error will be raised. When hash partitioning is used, the
245 operator class used must implement support function 2 (see
246 Section 37.16.3 for details).
247 A partitioned table is divided into sub-tables (called partitions),
249 which are created using separate CREATE TABLE commands. The
250 partitioned table is itself empty. A data row inserted into the
251 table is routed to a partition based on the value of columns or
252 expressions in the partition key. If no existing partition matches
253 the values in the new row, an error will be reported.
254 Partitioned tables do not support EXCLUDE constraints; however, you
256 can define these constraints on individual partitions.
257 See Section 5.11 for more discussion on table partitioning.
259 PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
261 Creates the table as a partition of the specified parent table. The
262 table can be created either as a partition for specific values
263 using FOR VALUES or as a default partition using DEFAULT. Any
264 indexes, constraints and user-defined row-level triggers that exist
265 in the parent table are cloned on the new partition.
266 The partition_bound_spec must correspond to the partitioning method
268 and partition key of the parent table, and must not overlap with
269 any existing partition of that parent. The form with IN is used for
270 list partitioning, the form with FROM and TO is used for range
271 partitioning, and the form with WITH is used for hash partitioning.
272 partition_bound_expr is any variable-free expression (subqueries,
274 window functions, aggregate functions, and set-returning functions
275 are not allowed). Its data type must match the data type of the
276 corresponding partition key column. The expression is evaluated
277 once at table creation time, so it can even contain volatile
278 expressions such as CURRENT_TIMESTAMP.
279 When creating a list partition, NULL can be specified to signify
281 that the partition allows the partition key column to be null.
282 However, there cannot be more than one such list partition for a
283 given parent table. NULL cannot be specified for range partitions.
284 When creating a range partition, the lower bound specified with
286 FROM is an inclusive bound, whereas the upper bound specified with
287 TO is an exclusive bound. That is, the values specified in the FROM
288 list are valid values of the corresponding partition key columns
289 for this partition, whereas those in the TO list are not. Note that
290 this statement must be understood according to the rules of
291 row-wise comparison (Section 9.23.5). For example, given PARTITION
292 BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1
293 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
294 The special values MINVALUE and MAXVALUE may be used when creating
296 a range partition to indicate that there is no lower or upper bound
297 on the column's value. For example, a partition defined using FROM
298 (MINVALUE) TO (10) allows any values less than 10, and a partition
299 defined using FROM (10) TO (MAXVALUE) allows any values greater
300 than or equal to 10.
301 When creating a range partition involving more than one column, it
303 can also make sense to use MAXVALUE as part of the lower bound, and
304 MINVALUE as part of the upper bound. For example, a partition
305 defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows
306 where the first partition key column is greater than 0 and less
307 than or equal to 10. Similarly, a partition defined using FROM
308 ('a', MINVALUE) TO ('b', MINVALUE) allows any rows where the first
309 partition key column starts with "a".
310 Note that if MINVALUE or MAXVALUE is used for one column of a
312 partitioning bound, the same value must be used for all subsequent
313 columns. For example, (10, MINVALUE, 0) is not a valid bound; you
314 should write (10, MINVALUE, MINVALUE).
315 Also note that some element types, such as timestamp, have a notion
317 of "infinity", which is just another value that can be stored. This
318 is different from MINVALUE and MAXVALUE, which are not real values
319 that can be stored, but rather they are ways of saying that the
320 value is unbounded. MAXVALUE can be thought of as being greater
321 than any other value, including "infinity" and MINVALUE as being
322 less than any other value, including "minus infinity". Thus the
323 range FROM ('infinity') TO (MAXVALUE) is not an empty range; it
324 allows precisely one value to be stored — "infinity".
325 If DEFAULT is specified, the table will be created as the default
327 partition of the parent table. This option is not available for
328 hash-partitioned tables. A partition key value not fitting into any
329 other partition of the given parent will be routed to the default
330 partition.
331 When a table has an existing DEFAULT partition and a new partition
333 is added to it, the default partition must be scanned to verify
334 that it does not contain any rows which properly belong in the new
335 partition. If the default partition contains a large number of
336 rows, this may be slow. The scan will be skipped if the default
337 partition is a foreign table or if it has a constraint which proves
338 that it cannot contain rows which should be placed in the new
339 partition.
340 When creating a hash partition, a modulus and remainder must be
342 specified. The modulus must be a positive integer, and the
343 remainder must be a non-negative integer less than the modulus.
344 Typically, when initially setting up a hash-partitioned table, you
345 should choose a modulus equal to the number of partitions and
346 assign every table the same modulus and a different remainder (see
347 examples, below). However, it is not required that every partition
348 have the same modulus, only that every modulus which occurs among
349 the partitions of a hash-partitioned table is a factor of the next
350 larger modulus. This allows the number of partitions to be
351 increased incrementally without needing to move all the data at
352 once. For example, suppose you have a hash-partitioned table with 8
353 partitions, each of which has modulus 8, but find it necessary to
354 increase the number of partitions to 16. You can detach one of the
355 modulus-8 partitions, create two new modulus-16 partitions covering
356 the same portion of the key space (one with a remainder equal to
357 the remainder of the detached partition, and the other with a
358 remainder equal to that value plus 8), and repopulate them with
359 data. You can then repeat this -- perhaps at a later time -- for
360 each modulus-8 partition until none remain. While this may still
361 involve a large amount of data movement at each step, it is still
362 better than having to create a whole new table and move all the
363 data at once.
364 A partition must have the same column names and types as the
366 partitioned table to which it belongs. Modifications to the column
367 names or types of a partitioned table will automatically propagate
368 to all partitions. CHECK constraints will be inherited
369 automatically by every partition, but an individual partition may
370 specify additional CHECK constraints; additional constraints with
371 the same name and condition as in the parent will be merged with
372 the parent constraint. Defaults may be specified separately for
373 each partition. But note that a partition's default value is not
374 applied when inserting a tuple through a partitioned table.
375 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 Also unlike INHERITS, columns and constraints copied by LIKE are
397 not merged with similarly named columns and constraints. If the
398 same name is specified explicitly or in another LIKE clause, an
399 error is signaled.
400 The optional like_option clauses specify which additional
402 properties of the original table to copy. Specifying INCLUDING
403 copies the property, specifying EXCLUDING omits the property.
404 EXCLUDING is the default. If multiple specifications are made for
405 the same kind of object, the last one is used. The available
406 options are:
407 INCLUDING COMMENTS
409 Comments for the copied columns, constraints, and indexes will
410 be copied. The default behavior is to exclude comments,
411 resulting in the copied columns and constraints in the new
412 table having no comments.
413 INCLUDING CONSTRAINTS
415 CHECK constraints will be copied. No distinction is made
416 between column constraints and table constraints. Not-null
417 constraints are always copied to the new table.
418 INCLUDING DEFAULTS
420 Default expressions for the copied column definitions will be
421 copied. Otherwise, default expressions are not copied,
422 resulting in the copied columns in the new table having null
423 defaults. Note that copying defaults that call
424 database-modification functions, such as nextval, may create a
425 functional linkage between the original and new tables.
426 INCLUDING GENERATED
428 Any generation expressions of copied column definitions will be
429 copied. By default, new columns will be regular base columns.
430 INCLUDING IDENTITY
432 Any identity specifications of copied column definitions will
433 be copied. A new sequence is created for each identity column
434 of the new table, separate from the sequences associated with
435 the old table.
436 INCLUDING INDEXES
438 Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
439 original table will be created on the new table. Names for the
440 new indexes and constraints are chosen according to the default
441 rules, regardless of how the originals were named. (This
442 behavior avoids possible duplicate-name failures for the new
443 indexes.)
444 INCLUDING STATISTICS
446 Extended statistics are copied to the new table.
447 INCLUDING STORAGE
449 STORAGE settings for the copied column definitions will be
450 copied. The default behavior is to exclude STORAGE settings,
451 resulting in the copied columns in the new table having
452 type-specific default settings. For more on STORAGE settings,
453 see Section 68.2.
454 INCLUDING ALL
456 INCLUDING ALL is an abbreviated form selecting all the
457 available individual options. (It could be useful to write
458 individual EXCLUDING clauses after INCLUDING ALL to select all
459 but some specific options.)
460 The LIKE clause can also be used to copy column definitions from
462 views, foreign tables, or composite types. Inapplicable options
463 (e.g., INCLUDING INDEXES from a view) are ignored.
464 CONSTRAINT constraint_name
466 An optional name for a column or table constraint. If the
467 constraint is violated, the constraint name is present in error
468 messages, so constraint names like col must be positive can be used
469 to communicate helpful constraint information to client
470 applications. (Double-quotes are needed to specify constraint names
471 that contain spaces.) If a constraint name is not specified, the
472 system generates a name.
473 NOT NULL
475 The column is not allowed to contain null values.
476 NULL
478 The column is allowed to contain null values. This is the default.
479 This clause is only provided for compatibility with non-standard
481 SQL databases. Its use is discouraged in new applications.
482 CHECK ( expression ) [ NO INHERIT ]
484 The CHECK clause specifies an expression producing a Boolean result
485 which new or updated rows must satisfy for an insert or update
486 operation to succeed. Expressions evaluating to TRUE or UNKNOWN
487 succeed. Should any row of an insert or update operation produce a
488 FALSE result, an error exception is raised and the insert or update
489 does not alter the database. A check constraint specified as a
490 column constraint should reference that column's value only, while
491 an expression appearing in a table constraint can reference
492 multiple columns.
493 Currently, CHECK expressions cannot contain subqueries nor refer to
495 variables other than columns of the current row (see
496 Section 5.4.1). The system column tableoid may be referenced, but
497 not any other system column.
498 A constraint marked with NO INHERIT will not propagate to child
500 tables.
501 When a table has multiple CHECK constraints, they will be tested
503 for each row in alphabetical order by name, after checking NOT NULL
504 constraints. (PostgreSQL versions before 9.5 did not honor any
505 particular firing order for CHECK constraints.)
506 DEFAULT default_expr
508 The DEFAULT clause assigns a default data value for the column
509 whose column definition it appears within. The value is any
510 variable-free expression (in particular, cross-references to other
511 columns in the current table are not allowed). Subqueries are not
512 allowed either. The data type of the default expression must match
513 the data type of the column.
514 The default expression will be used in any insert operation that
516 does not specify a value for the column. If there is no default for
517 a column, then the default is null.
518 GENERATED ALWAYS AS ( generation_expr ) STORED
520 This clause creates the column as a generated column. The column
521 cannot be written to, and when read the result of the specified
522 expression will be returned.
523 The keyword STORED is required to signify that the column will be
525 computed on write and will be stored on disk.
526 The generation expression can refer to other columns in the table,
528 but not other generated columns. Any functions and operators used
529 must be immutable. References to other tables are not allowed.
530 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
532 This clause creates the column as an identity column. It will have
533 an implicit sequence attached to it and the column in new rows will
534 automatically have values from the sequence assigned to it.
535 The clauses ALWAYS and BY DEFAULT determine how the sequence value
537 is given precedence over a user-specified value in an INSERT
538 statement. If ALWAYS is specified, a user-specified value is only
539 accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE.
540 If BY DEFAULT is specified, then the user-specified value takes
541 precedence. See INSERT(7) for details. (In the COPY command,
542 user-specified values are always used regardless of this setting.)
543 The optional sequence_options clause can be used to override the
545 options of the sequence. See CREATE SEQUENCE (CREATE_SEQUENCE(7))
546 for details.
547 UNIQUE (column constraint)
549 UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
550 (table constraint)
551 The UNIQUE constraint specifies that a group of one or more columns
552 of a table can contain only unique values. The behavior of a unique
553 table constraint is the same as that of a unique column constraint,
554 with the additional capability to span multiple columns. The
555 constraint therefore enforces that any two rows must differ in at
556 least one of these columns.
557 For the purpose of a unique constraint, null values are not
559 considered equal.
560 Each unique constraint should name a set of columns that is
562 different from the set of columns named by any other unique or
563 primary key constraint defined for the table. (Otherwise, redundant
564 unique constraints will be discarded.)
565 When establishing a unique constraint for a multi-level partition
567 hierarchy, all the columns in the partition key of the target
568 partitioned table, as well as those of all its descendant
569 partitioned tables, must be included in the constraint definition.
570 Adding a unique constraint will automatically create a unique btree
572 index on the column or group of columns used in the constraint.
573 The optional INCLUDE clause adds to that index one or more columns
575 that are simply “payload”: uniqueness is not enforced on them, and
576 the index cannot be searched on the basis of those columns. However
577 they can be retrieved by an index-only scan. Note that although the
578 constraint is not enforced on included columns, it still depends on
579 them. Consequently, some operations on such columns (e.g., DROP
580 COLUMN) can cause cascaded constraint and index deletion.
581 PRIMARY KEY (column constraint)
583 PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
584 (table constraint)
585 The PRIMARY KEY constraint specifies that a column or columns of a
586 table can contain only unique (non-duplicate), nonnull values. Only
587 one primary key can be specified for a table, whether as a column
588 constraint or a table constraint.
589 The primary key constraint should name a set of columns that is
591 different from the set of columns named by any unique constraint
592 defined for the same table. (Otherwise, the unique constraint is
593 redundant and will be discarded.)
594 PRIMARY KEY enforces the same data constraints as a combination of
596 UNIQUE and NOT NULL. However, identifying a set of columns as the
597 primary key also provides metadata about the design of the schema,
598 since a primary key implies that other tables can rely on this set
599 of columns as a unique identifier for rows.
600 When placed on a partitioned table, PRIMARY KEY constraints share
602 the restrictions previously decribed for UNIQUE constraints.
603 Adding a PRIMARY KEY constraint will automatically create a unique
605 btree index on the column or group of columns used in the
606 constraint.
607 The optional INCLUDE clause adds to that index one or more columns
609 that are simply “payload”: uniqueness is not enforced on them, and
610 the index cannot be searched on the basis of those columns. However
611 they can be retrieved by an index-only scan. Note that although the
612 constraint is not enforced on included columns, it still depends on
613 them. Consequently, some operations on such columns (e.g., DROP
614 COLUMN) can cause cascaded constraint and index deletion.
615 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
617 ) index_parameters [ WHERE ( predicate ) ]
618 The EXCLUDE clause defines an exclusion constraint, which
619 guarantees that if any two rows are compared on the specified
620 column(s) or expression(s) using the specified operator(s), not all
621 of these comparisons will return TRUE. If all of the specified
622 operators test for equality, this is equivalent to a UNIQUE
623 constraint, although an ordinary unique constraint will be faster.
624 However, exclusion constraints can specify constraints that are
625 more general than simple equality. For example, you can specify a
626 constraint that no two rows in the table contain overlapping
627 circles (see Section 8.8) by using the && operator.
628 Exclusion constraints are implemented using an index, so each
630 specified operator must be associated with an appropriate operator
631 class (see Section 11.10) for the index access method index_method.
632 The operators are required to be commutative. Each exclude_element
633 can optionally specify an operator class and/or ordering options;
634 these are described fully under CREATE INDEX (CREATE_INDEX(7)).
635 The access method must support amgettuple (see Chapter 61); at
637 present this means GIN cannot be used. Although it's allowed, there
638 is little point in using B-tree or hash indexes with an exclusion
639 constraint, because this does nothing that an ordinary unique
640 constraint doesn't do better. So in practice the access method will
641 always be GiST or SP-GiST.
642 The predicate allows you to specify an exclusion constraint on a
644 subset of the table; internally this creates a partial index. Note
645 that parentheses are required around the predicate.
646 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
648 referential_action ] [ ON UPDATE referential_action ] (column
649 constraint)
650 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
651 [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON
652 UPDATE referential_action ] (table constraint)
653 These clauses specify a foreign key constraint, which requires that
654 a group of one or more columns of the new table must only contain
655 values that match values in the referenced column(s) of some row of
656 the referenced table. If the refcolumn list is omitted, the primary
657 key of the reftable is used. The referenced columns must be the
658 columns of a non-deferrable unique or primary key constraint in the
659 referenced table. The user must have REFERENCES permission on the
660 referenced table (either the whole table, or the specific
661 referenced columns). The addition of a foreign key constraint
662 requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note
663 that foreign key constraints cannot be defined between temporary
664 tables and permanent tables.
665 A value inserted into the referencing column(s) is matched against
667 the values of the referenced table and referenced columns using the
668 given match type. There are three match types: MATCH FULL, MATCH
669 PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will
670 not allow one column of a multicolumn foreign key to be null unless
671 all foreign key columns are null; if they are all null, the row is
672 not required to have a match in the referenced table. MATCH SIMPLE
673 allows any of the foreign key columns to be null; if any of them
674 are null, the row is not required to have a match in the referenced
675 table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL
676 constraints can be applied to the referencing column(s) to prevent
677 these cases from arising.)
678 In addition, when the data in the referenced columns is changed,
680 certain actions are performed on the data in this table's columns.
681 The ON DELETE clause specifies the action to perform when a
682 referenced row in the referenced table is being deleted. Likewise,
683 the ON UPDATE clause specifies the action to perform when a
684 referenced column in the referenced table is being updated to a new
685 value. If the row is updated, but the referenced column is not
686 actually changed, no action is done. Referential actions other than
687 the NO ACTION check cannot be deferred, even if the constraint is
688 declared deferrable. There are the following possible actions for
689 each clause:
690 NO ACTION
692 Produce an error indicating that the deletion or update would
693 create a foreign key constraint violation. If the constraint is
694 deferred, this error will be produced at constraint check time
695 if there still exist any referencing rows. This is the default
696 action.
697 RESTRICT
699 Produce an error indicating that the deletion or update would
700 create a foreign key constraint violation. This is the same as
701 NO ACTION except that the check is not deferrable.
702 CASCADE
704 Delete any rows referencing the deleted row, or update the
705 values of the referencing column(s) to the new values of the
706 referenced columns, respectively.
707 SET NULL
709 Set the referencing column(s) to null.
710 SET DEFAULT
712 Set the referencing column(s) to their default values. (There
713 must be a row in the referenced table matching the default
714 values, if they are not null, or the operation will fail.)
715 If the referenced column(s) are changed frequently, it might be
717 wise to add an index to the referencing column(s) so that
718 referential actions associated with the foreign key constraint can
719 be performed more efficiently.
720 DEFERRABLE
722 NOT DEFERRABLE
723 This controls whether the constraint can be deferred. A constraint
724 that is not deferrable will be checked immediately after every
725 command. Checking of constraints that are deferrable can be
726 postponed until the end of the transaction (using the SET
727 CONSTRAINTS (SET_CONSTRAINTS(7)) command). NOT DEFERRABLE is the
728 default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and
729 REFERENCES (foreign key) constraints accept this clause. NOT NULL
730 and CHECK constraints are not deferrable. Note that deferrable
731 constraints cannot be used as conflict arbitrators in an INSERT
732 statement that includes an ON CONFLICT DO UPDATE clause.
733 INITIALLY IMMEDIATE
735 INITIALLY DEFERRED
736 If a constraint is deferrable, this clause specifies the default
737 time to check the constraint. If the constraint is INITIALLY
738 IMMEDIATE, it is checked after each statement. This is the default.
739 If the constraint is INITIALLY DEFERRED, it is checked only at the
740 end of the transaction. The constraint check time can be altered
741 with the SET CONSTRAINTS (SET_CONSTRAINTS(7)) command.
742 USING method
744 This optional clause specifies the table access method to use to
745 store the contents for the new table; the method needs be an access
746 method of type TABLE. See Chapter 60 for more information. If this
747 option is not specified, the default table access method is chosen
748 for the new table. See default_table_access_method for more
749 information.
750 WITH ( storage_parameter [= value] [, ... ] )
752 This clause specifies optional storage parameters for a table or
753 index; see Storage Parameters for more information. For
754 backward-compatibility the WITH clause for a table can also include
755 OIDS=FALSE to specify that rows of the new table should not contain
756 OIDs (object identifiers), OIDS=TRUE is not supported anymore.
757 WITHOUT OIDS
759 This is backward-compatible syntax for declaring a table WITHOUT
760 OIDS, creating a table WITH OIDS is not supported anymore.
761 ON COMMIT
763 The behavior of temporary tables at the end of a transaction block
764 can be controlled using ON COMMIT. The three options are:
765 PRESERVE ROWS
767 No special action is taken at the ends of transactions. This is
768 the default behavior.
769 DELETE ROWS
771 All rows in the temporary table will be deleted at the end of
772 each transaction block. Essentially, an automatic TRUNCATE(7)
773 is done at each commit. When used on a partitioned table, this
774 is not cascaded to its partitions.
775 DROP
777 The temporary table will be dropped at the end of the current
778 transaction block. When used on a partitioned table, this
779 action drops its partitions and when used on tables with
780 inheritance children, it drops the dependent children.
781 TABLESPACE tablespace_name
783 The tablespace_name is the name of the tablespace in which the new
784 table is to be created. If not specified, default_tablespace is
785 consulted, or temp_tablespaces if the table is temporary. For
786 partitioned tables, since no storage is required for the table
787 itself, the tablespace specified overrides default_tablespace as
788 the default tablespace to use for any newly created partitions when
789 no other tablespace is explicitly specified.
790 USING INDEX TABLESPACE tablespace_name
792 This clause allows selection of the tablespace in which the index
793 associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
794 be created. If not specified, default_tablespace is consulted, or
795 temp_tablespaces if the table is temporary.
796 Storage Parameters
798 The WITH clause can specify storage parameters for tables, and for
799 indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
800 Storage parameters for indexes are documented in CREATE INDEX
801 (CREATE_INDEX(7)). The storage parameters currently available for
802 tables are listed below. For many of these parameters, as shown, there
803 is an additional parameter with the same name prefixed with toast.,
804 which controls the behavior of the table's secondary TOAST table, if
805 any (see Section 68.2 for more information about TOAST). If a table
806 parameter value is set and the equivalent toast. parameter is not, the
807 TOAST table will use the table's parameter value. Specifying these
808 parameters for partitioned tables is not supported, but you may specify
809 them for individual leaf partitions.
810 fillfactor (integer)
812 The fillfactor for a table is a percentage between 10 and 100. 100
813 (complete packing) is the default. When a smaller fillfactor is
814 specified, INSERT operations pack table pages only to the indicated
815 percentage; the remaining space on each page is reserved for
816 updating rows on that page. This gives UPDATE a chance to place the
817 updated copy of a row on the same page as the original, which is
818 more efficient than placing it on a different page. For a table
819 whose entries are never updated, complete packing is the best
820 choice, but in heavily updated tables smaller fillfactors are
821 appropriate. This parameter cannot be set for TOAST tables.
822 toast_tuple_target (integer)
824 The toast_tuple_target specifies the minimum tuple length required
825 before we try to compress and/or move long column values into TOAST
826 tables, and is also the target length we try to reduce the length
827 below once toasting begins. This affects columns marked as External
828 (for move), Main (for compression), or Extended (for both) and
829 applies only to new tuples. There is no effect on existing rows. By
830 default this parameter is set to allow at least 4 tuples per block,
831 which with the default blocksize will be 2040 bytes. Valid values
832 are between 128 bytes and the (blocksize - header), by default 8160
833 bytes. Changing this value may not be useful for very short or very
834 long rows. Note that the default setting is often close to optimal,
835 and it is possible that setting this parameter could have negative
836 effects in some cases. This parameter cannot be set for TOAST
837 tables.
838 parallel_workers (integer)
840 This sets the number of workers that should be used to assist a
841 parallel scan of this table. If not set, the system will determine
842 a value based on the relation size. The actual number of workers
843 chosen by the planner or by utility statements that use parallel
844 scans may be less, for example due to the setting of
845 max_worker_processes.
846 autovacuum_enabled, toast.autovacuum_enabled (boolean)
848 Enables or disables the autovacuum daemon for a particular table.
849 If true, the autovacuum daemon will perform automatic VACUUM and/or
850 ANALYZE operations on this table following the rules discussed in
851 Section 24.1.6. If false, this table will not be autovacuumed,
852 except to prevent transaction ID wraparound. See Section 24.1.5 for
853 more about wraparound prevention. Note that the autovacuum daemon
854 does not run at all (except to prevent transaction ID wraparound)
855 if the autovacuum parameter is false; setting individual tables'
856 storage parameters does not override that. Therefore there is
857 seldom much point in explicitly setting this storage parameter to
858 true, only to false.
859 vacuum_index_cleanup, toast.vacuum_index_cleanup (boolean)
861 Enables or disables index cleanup when VACUUM is run on this table.
862 The default value is true. Disabling index cleanup can speed up
863 VACUUM very significantly, but may also lead to severely bloated
864 indexes if table modifications are frequent. The INDEX_CLEANUP
865 parameter of VACUUM(7), if specified, overrides the value of this
866 option.
867 vacuum_truncate, toast.vacuum_truncate (boolean)
869 Enables or disables vacuum to try to truncate off any empty pages
870 at the end of this table. The default value is true. If true,
871 VACUUM and autovacuum do the truncation and the disk space for the
872 truncated pages is returned to the operating system. Note that the
873 truncation requires ACCESS EXCLUSIVE lock on the table. The
874 TRUNCATE parameter of VACUUM(7), if specified, overrides the value
875 of this option.
876 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
878 (integer)
879 Per-table value for autovacuum_vacuum_threshold parameter.
880 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
882 (floating point)
883 Per-table value for autovacuum_vacuum_scale_factor parameter.
884 autovacuum_analyze_threshold (integer)
886 Per-table value for autovacuum_analyze_threshold parameter.
887 autovacuum_analyze_scale_factor (floating point)
889 Per-table value for autovacuum_analyze_scale_factor parameter.
890 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
892 (floating point)
893 Per-table value for autovacuum_vacuum_cost_delay parameter.
894 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
896 (integer)
897 Per-table value for autovacuum_vacuum_cost_limit parameter.
898 autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
900 Per-table value for vacuum_freeze_min_age parameter. Note that
901 autovacuum will ignore per-table autovacuum_freeze_min_age
902 parameters that are larger than half the system-wide
903 autovacuum_freeze_max_age setting.
904 autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
906 Per-table value for autovacuum_freeze_max_age parameter. Note that
907 autovacuum will ignore per-table autovacuum_freeze_max_age
908 parameters that are larger than the system-wide setting (it can
909 only be set smaller).
910 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
912 (integer)
913 Per-table value for vacuum_freeze_table_age parameter.
914 autovacuum_multixact_freeze_min_age,
916 toast.autovacuum_multixact_freeze_min_age (integer)
917 Per-table value for vacuum_multixact_freeze_min_age parameter. Note
918 that autovacuum will ignore per-table
919 autovacuum_multixact_freeze_min_age parameters that are larger than
920 half the system-wide autovacuum_multixact_freeze_max_age setting.
921 autovacuum_multixact_freeze_max_age,
923 toast.autovacuum_multixact_freeze_max_age (integer)
924 Per-table value for autovacuum_multixact_freeze_max_age parameter.
925 Note that autovacuum will ignore per-table
926 autovacuum_multixact_freeze_max_age parameters that are larger than
927 the system-wide setting (it can only be set smaller).
928 autovacuum_multixact_freeze_table_age,
930 toast.autovacuum_multixact_freeze_table_age (integer)
931 Per-table value for vacuum_multixact_freeze_table_age parameter.
932 log_autovacuum_min_duration, toast.log_autovacuum_min_duration
934 (integer)
935 Per-table value for log_autovacuum_min_duration parameter.
936 user_catalog_table (boolean)
938 Declare the table as an additional catalog table for purposes of
939 logical replication. See Section 48.6.2 for details. This parameter
940 cannot be set for TOAST tables.
941
943 PostgreSQL automatically creates an index for each unique constraint
944 and primary key constraint to enforce uniqueness. Thus, it is not
945 necessary to create an index explicitly for primary key columns. (See
946 CREATE INDEX (CREATE_INDEX(7)) for more information.)
947 Unique constraints and primary keys are not inherited in the current
949 implementation. This makes the combination of inheritance and unique
950 constraints rather dysfunctional.
951 A table cannot have more than 1600 columns. (In practice, the effective
953 limit is usually lower because of tuple-length constraints.)
954
956 Create table films and table distributors:
957 CREATE TABLE films (
959 code char(5) CONSTRAINT firstkey PRIMARY KEY,
960 title varchar(40) NOT NULL,
961 did integer NOT NULL,
962 date_prod date,
963 kind varchar(10),
964 len interval hour to minute
965 );
966 CREATE TABLE distributors (
968 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
969 name varchar(40) NOT NULL CHECK (name <> '')
970 );
971 Create a table with a 2-dimensional array:
973 CREATE TABLE array_int (
975 vector int[][]
976 );
977 Define a unique table constraint for the table films. Unique table
979 constraints can be defined on one or more columns of the table:
980 CREATE TABLE films (
982 code char(5),
983 title varchar(40),
984 did integer,
985 date_prod date,
986 kind varchar(10),
987 len interval hour to minute,
988 CONSTRAINT production UNIQUE(date_prod)
989 );
990 Define a check column constraint:
992 CREATE TABLE distributors (
994 did integer CHECK (did > 100),
995 name varchar(40)
996 );
997 Define a check table constraint:
999 CREATE TABLE distributors (
1001 did integer,
1002 name varchar(40),
1003 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
1004 );
1005 Define a primary key table constraint for the table films:
1007 CREATE TABLE films (
1009 code char(5),
1010 title varchar(40),
1011 did integer,
1012 date_prod date,
1013 kind varchar(10),
1014 len interval hour to minute,
1015 CONSTRAINT code_title PRIMARY KEY(code,title)
1016 );
1017 Define a primary key constraint for table distributors. The following
1019 two examples are equivalent, the first using the table constraint
1020 syntax, the second the column constraint syntax:
1021 CREATE TABLE distributors (
1023 did integer,
1024 name varchar(40),
1025 PRIMARY KEY(did)
1026 );
1027 CREATE TABLE distributors (
1029 did integer PRIMARY KEY,
1030 name varchar(40)
1031 );
1032 Assign a literal constant default value for the column name, arrange
1034 for the default value of column did to be generated by selecting the
1035 next value of a sequence object, and make the default value of modtime
1036 be the time at which the row is inserted:
1037 CREATE TABLE distributors (
1039 name varchar(40) DEFAULT 'Luso Films',
1040 did integer DEFAULT nextval('distributors_serial'),
1041 modtime timestamp DEFAULT current_timestamp
1042 );
1043 Define two NOT NULL column constraints on the table distributors, one
1045 of which is explicitly given a name:
1046 CREATE TABLE distributors (
1048 did integer CONSTRAINT no_null NOT NULL,
1049 name varchar(40) NOT NULL
1050 );
1051 Define a unique constraint for the name column:
1053 CREATE TABLE distributors (
1055 did integer,
1056 name varchar(40) UNIQUE
1057 );
1058 The same, specified as a table constraint:
1060 CREATE TABLE distributors (
1062 did integer,
1063 name varchar(40),
1064 UNIQUE(name)
1065 );
1066 Create the same table, specifying 70% fill factor for both the table
1068 and its unique index:
1069 CREATE TABLE distributors (
1071 did integer,
1072 name varchar(40),
1073 UNIQUE(name) WITH (fillfactor=70)
1074 )
1075 WITH (fillfactor=70);
1076 Create table circles with an exclusion constraint that prevents any two
1078 circles from overlapping:
1079 CREATE TABLE circles (
1081 c circle,
1082 EXCLUDE USING gist (c WITH &&)
1083 );
1084 Create table cinemas in tablespace diskvol1:
1086 CREATE TABLE cinemas (
1088 id serial,
1089 name text,
1090 location text
1091 ) TABLESPACE diskvol1;
1092 Create a composite type and a typed table:
1094 CREATE TYPE employee_type AS (name text, salary numeric);
1096 CREATE TABLE employees OF employee_type (
1098 PRIMARY KEY (name),
1099 salary WITH OPTIONS DEFAULT 1000
1100 );
1101 Create a range partitioned table:
1103 CREATE TABLE measurement (
1105 logdate date not null,
1106 peaktemp int,
1107 unitsales int
1108 ) PARTITION BY RANGE (logdate);
1109 Create a range partitioned table with multiple columns in the partition
1111 key:
1112 CREATE TABLE measurement_year_month (
1114 logdate date not null,
1115 peaktemp int,
1116 unitsales int
1117 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1118 Create a list partitioned table:
1120 CREATE TABLE cities (
1122 city_id bigserial not null,
1123 name text not null,
1124 population bigint
1125 ) PARTITION BY LIST (left(lower(name), 1));
1126 Create a hash partitioned table:
1128 CREATE TABLE orders (
1130 order_id bigint not null,
1131 cust_id bigint not null,
1132 status text
1133 ) PARTITION BY HASH (order_id);
1134 Create partition of a range partitioned table:
1136 CREATE TABLE measurement_y2016m07
1138 PARTITION OF measurement (
1139 unitsales DEFAULT 0
1140 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1141 Create a few partitions of a range partitioned table with multiple
1143 columns in the partition key:
1144 CREATE TABLE measurement_ym_older
1146 PARTITION OF measurement_year_month
1147 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1148 CREATE TABLE measurement_ym_y2016m11
1150 PARTITION OF measurement_year_month
1151 FOR VALUES FROM (2016, 11) TO (2016, 12);
1152 CREATE TABLE measurement_ym_y2016m12
1154 PARTITION OF measurement_year_month
1155 FOR VALUES FROM (2016, 12) TO (2017, 01);
1156 CREATE TABLE measurement_ym_y2017m01
1158 PARTITION OF measurement_year_month
1159 FOR VALUES FROM (2017, 01) TO (2017, 02);
1160 Create partition of a list partitioned table:
1162 CREATE TABLE cities_ab
1164 PARTITION OF cities (
1165 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1166 ) FOR VALUES IN ('a', 'b');
1167 Create partition of a list partitioned table that is itself further
1169 partitioned and then add a partition to it:
1170 CREATE TABLE cities_ab
1172 PARTITION OF cities (
1173 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1174 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1175 CREATE TABLE cities_ab_10000_to_100000
1177 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1178 Create partitions of a hash partitioned table:
1180 CREATE TABLE orders_p1 PARTITION OF orders
1182 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1183 CREATE TABLE orders_p2 PARTITION OF orders
1184 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1185 CREATE TABLE orders_p3 PARTITION OF orders
1186 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1187 CREATE TABLE orders_p4 PARTITION OF orders
1188 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1189 Create a default partition:
1191 CREATE TABLE cities_partdef
1193 PARTITION OF cities DEFAULT;
1194
1196 The CREATE TABLE command conforms to the SQL standard, with exceptions
1197 listed below.
1198 Temporary Tables
1200 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1201 standard, the effect is not the same. In the standard, temporary tables
1202 are defined just once and automatically exist (starting with empty
1203 contents) in every session that needs them. PostgreSQL instead
1204 requires each session to issue its own CREATE TEMPORARY TABLE command
1205 for each temporary table to be used. This allows different sessions to
1206 use the same temporary table name for different purposes, whereas the
1207 standard's approach constrains all instances of a given temporary table
1208 name to have the same table structure.
1209 The standard's definition of the behavior of temporary tables is widely
1211 ignored. PostgreSQL's behavior on this point is similar to that of
1212 several other SQL databases.
1213 The SQL standard also distinguishes between global and local temporary
1215 tables, where a local temporary table has a separate set of contents
1216 for each SQL module within each session, though its definition is still
1217 shared across sessions. Since PostgreSQL does not support SQL modules,
1218 this distinction is not relevant in PostgreSQL.
1219 For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1221 keywords in a temporary table declaration, but they currently have no
1222 effect. Use of these keywords is discouraged, since future versions of
1223 PostgreSQL might adopt a more standard-compliant interpretation of
1224 their meaning.
1225 The ON COMMIT clause for temporary tables also resembles the SQL
1227 standard, but has some differences. If the ON COMMIT clause is omitted,
1228 SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1229 However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1230 The ON COMMIT DROP option does not exist in SQL.
1231 Non-Deferred Uniqueness Constraints
1233 When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1234 checks for uniqueness immediately whenever a row is inserted or
1235 modified. The SQL standard says that uniqueness should be enforced only
1236 at the end of the statement; this makes a difference when, for example,
1237 a single command updates multiple key values. To obtain
1238 standard-compliant behavior, declare the constraint as DEFERRABLE but
1239 not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1240 significantly slower than immediate uniqueness checking.
1241 Column Check Constraints
1243 The SQL standard says that CHECK column constraints can only refer to
1244 the column they apply to; only CHECK table constraints can refer to
1245 multiple columns. PostgreSQL does not enforce this restriction; it
1246 treats column and table check constraints alike.
1247 EXCLUDE Constraint
1249 The EXCLUDE constraint type is a PostgreSQL extension.
1250 NULL “Constraint”
1252 The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1253 extension to the SQL standard that is included for compatibility with
1254 some other database systems (and for symmetry with the NOT NULL
1255 constraint). Since it is the default for any column, its presence is
1256 simply noise.
1257 Constraint Naming
1259 The SQL standard says that table and domain constraints must have names
1260 that are unique across the schema containing the table or domain.
1261 PostgreSQL is laxer: it only requires constraint names to be unique
1262 across the constraints attached to a particular table or domain.
1263 However, this extra freedom does not exist for index-based constraints
1264 (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1265 index is named the same as the constraint, and index names must be
1266 unique across all relations within the same schema.
1267 Currently, PostgreSQL does not record names for NOT NULL constraints at
1269 all, so they are not subject to the uniqueness restriction. This might
1270 change in a future release.
1271 Inheritance
1273 Multiple inheritance via the INHERITS clause is a PostgreSQL language
1274 extension. SQL:1999 and later define single inheritance using a
1275 different syntax and different semantics. SQL:1999-style inheritance is
1276 not yet supported by PostgreSQL.
1277 Zero-Column Tables
1279 PostgreSQL allows a table of no columns to be created (for example,
1280 CREATE TABLE foo();). This is an extension from the SQL standard, which
1281 does not allow zero-column tables. Zero-column tables are not in
1282 themselves very useful, but disallowing them creates odd special cases
1283 for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1284 restriction.
1285 Multiple Identity Columns
1287 PostgreSQL allows a table to have more than one identity column. The
1288 standard specifies that a table can have at most one identity column.
1289 This is relaxed mainly to give more flexibility for doing schema
1290 changes or migrations. Note that the INSERT command supports only one
1291 override clause that applies to the entire statement, so having
1292 multiple identity columns with different behaviors is not well
1293 supported.
1294 Generated Columns
1296 The option STORED is not standard but is also used by other SQL
1297 implementations. The SQL standard does not specify the storage of
1298 generated columns.
1299 LIKE Clause
1301 While a LIKE clause exists in the SQL standard, many of the options
1302 that PostgreSQL accepts for it are not in the standard, and some of the
1303 standard's options are not implemented by PostgreSQL.
1304 WITH Clause
1306 The WITH clause is a PostgreSQL extension; storage parameters are not
1307 in the standard.
1308 Tablespaces
1310 The PostgreSQL concept of tablespaces is not part of the standard.
1311 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1312 extensions.
1313 Typed Tables
1315 Typed tables implement a subset of the SQL standard. According to the
1316 standard, a typed table has columns corresponding to the underlying
1317 composite type as well as one other column that is the
1318 “self-referencing column”. PostgreSQL does not support
1319 self-referencing columns explicitly.
1320 PARTITION BY Clause
1322 The PARTITION BY clause is a PostgreSQL extension.
1323 PARTITION OF Clause
1325 The PARTITION OF clause is a PostgreSQL extension.
1326
1328 ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1329 AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1330 CREATE TYPE (CREATE_TYPE(7))
1331PostgreSQL 12.6 2021 CREATE TABLE(7)