1CREATE TABLE(7) PostgreSQL 12.2 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.
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 partition_bound_expr is any variable-free expression (subqueries,
272 window functions, aggregate functions, and set-returning functions
273 are not allowed). Its data type must match the data type of the
274 corresponding partition key column. The expression is evaluated
275 once at table creation time, so it can even contain volatile
276 expressions such as CURRENT_TIMESTAMP.
277 When creating a list partition, NULL can be specified to signify
279 that the partition allows the partition key column to be null.
280 However, there cannot be more than one such list partition for a
281 given parent table. NULL cannot be specified for range partitions.
282 When creating a range partition, the lower bound specified with
284 FROM is an inclusive bound, whereas the upper bound specified with
285 TO is an exclusive bound. That is, the values specified in the FROM
286 list are valid values of the corresponding partition key columns
287 for this partition, whereas those in the TO list are not. Note that
288 this statement must be understood according to the rules of
289 row-wise comparison (Section 9.23.5). For example, given PARTITION
290 BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1
291 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
292 The special values MINVALUE and MAXVALUE may be used when creating
294 a range partition to indicate that there is no lower or upper bound
295 on the column's value. For example, a partition defined using FROM
296 (MINVALUE) TO (10) allows any values less than 10, and a partition
297 defined using FROM (10) TO (MAXVALUE) allows any values greater
298 than or equal to 10.
299 When creating a range partition involving more than one column, it
301 can also make sense to use MAXVALUE as part of the lower bound, and
302 MINVALUE as part of the upper bound. For example, a partition
303 defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows
304 where the first partition key column is greater than 0 and less
305 than or equal to 10. Similarly, a partition defined using FROM
306 ('a', MINVALUE) TO ('b', MINVALUE) allows any rows where the first
307 partition key column starts with "a".
308 Note that if MINVALUE or MAXVALUE is used for one column of a
310 partitioning bound, the same value must be used for all subsequent
311 columns. For example, (10, MINVALUE, 0) is not a valid bound; you
312 should write (10, MINVALUE, MINVALUE).
313 Also note that some element types, such as timestamp, have a notion
315 of "infinity", which is just another value that can be stored. This
316 is different from MINVALUE and MAXVALUE, which are not real values
317 that can be stored, but rather they are ways of saying that the
318 value is unbounded. MAXVALUE can be thought of as being greater
319 than any other value, including "infinity" and MINVALUE as being
320 less than any other value, including "minus infinity". Thus the
321 range FROM ('infinity') TO (MAXVALUE) is not an empty range; it
322 allows precisely one value to be stored — "infinity".
323 If DEFAULT is specified, the table will be created as the default
325 partition of the parent table. This option is not available for
326 hash-partitioned tables. A partition key value not fitting into any
327 other partition of the given parent will be routed to the default
328 partition.
329 When a table has an existing DEFAULT partition and a new partition
331 is added to it, the default partition must be scanned to verify
332 that it does not contain any rows which properly belong in the new
333 partition. If the default partition contains a large number of
334 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. Modifications to the column
365 names or types of a partitioned table will automatically propagate
366 to all partitions. CHECK constraints will be inherited
367 automatically by every partition, but an individual partition may
368 specify additional CHECK constraints; additional constraints with
369 the same name and condition as in the parent will be merged with
370 the parent constraint. Defaults may be specified separately for
371 each partition. But note that a partition's default value is not
372 applied when inserting a tuple through a partitioned table.
373 Rows inserted into a partitioned table will be automatically routed
375 to the correct partition. If no suitable partition exists, an error
376 will occur.
377 Operations such as TRUNCATE which normally affect a table and all
379 of its inheritance children will cascade to all partitions, but may
380 also be performed on an individual partition. Note that dropping a
381 partition with DROP TABLE requires taking an ACCESS EXCLUSIVE lock
382 on the parent table.
383 LIKE source_table [ like_option ... ]
385 The LIKE clause specifies a table from which the new table
386 automatically copies all column names, their data types, and their
387 not-null constraints.
388 Unlike INHERITS, the new table and original table are completely
390 decoupled after creation is complete. Changes to the original table
391 will not be applied to the new table, and it is not possible to
392 include data of the new table in scans of the original table.
393 Also unlike INHERITS, columns and constraints copied by LIKE are
395 not merged with similarly named columns and constraints. If the
396 same name is specified explicitly or in another LIKE clause, an
397 error is signaled.
398 The optional like_option clauses specify which additional
400 properties of the original table to copy. Specifying INCLUDING
401 copies the property, specifying EXCLUDING omits the property.
402 EXCLUDING is the default. If multiple specifications are made for
403 the same kind of object, the last one is used. The available
404 options are:
405 INCLUDING COMMENTS
407 Comments for the copied columns, constraints, and indexes will
408 be copied. The default behavior is to exclude comments,
409 resulting in the copied columns and constraints in the new
410 table having no comments.
411 INCLUDING CONSTRAINTS
413 CHECK constraints will be copied. No distinction is made
414 between column constraints and table constraints. Not-null
415 constraints are always copied to the new table.
416 INCLUDING DEFAULTS
418 Default expressions for the copied column definitions will be
419 copied. Otherwise, default expressions are not copied,
420 resulting in the copied columns in the new table having null
421 defaults. Note that copying defaults that call
422 database-modification functions, such as nextval, may create a
423 functional linkage between the original and new tables.
424 INCLUDING GENERATED
426 Any generation expressions of copied column definitions will be
427 copied. By default, new columns will be regular base columns.
428 INCLUDING IDENTITY
430 Any identity specifications of copied column definitions will
431 be copied. A new sequence is created for each identity column
432 of the new table, separate from the sequences associated with
433 the old table.
434 INCLUDING INDEXES
436 Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
437 original table will be created on the new table. Names for the
438 new indexes and constraints are chosen according to the default
439 rules, regardless of how the originals were named. (This
440 behavior avoids possible duplicate-name failures for the new
441 indexes.)
442 INCLUDING STATISTICS
444 Extended statistics are copied to the new table.
445 INCLUDING STORAGE
447 STORAGE settings for the copied column definitions will be
448 copied. The default behavior is to exclude STORAGE settings,
449 resulting in the copied columns in the new table having
450 type-specific default settings. For more on STORAGE settings,
451 see Section 68.2.
452 INCLUDING ALL
454 INCLUDING ALL is an abbreviated form selecting all the
455 available individual options. (It could be useful to write
456 individual EXCLUDING clauses after INCLUDING ALL to select all
457 but some specific options.)
458 The LIKE clause can also be used to copy column definitions from
460 views, foreign tables, or composite types. Inapplicable options
461 (e.g., INCLUDING INDEXES from a view) are ignored.
462 CONSTRAINT constraint_name
464 An optional name for a column or table constraint. If the
465 constraint is violated, the constraint name is present in error
466 messages, so constraint names like col must be positive can be used
467 to communicate helpful constraint information to client
468 applications. (Double-quotes are needed to specify constraint names
469 that contain spaces.) If a constraint name is not specified, the
470 system generates a name.
471 NOT NULL
473 The column is not allowed to contain null values.
474 NULL
476 The column is allowed to contain null values. This is the default.
477 This clause is only provided for compatibility with non-standard
479 SQL databases. Its use is discouraged in new applications.
480 CHECK ( expression ) [ NO INHERIT ]
482 The CHECK clause specifies an expression producing a Boolean result
483 which new or updated rows must satisfy for an insert or update
484 operation to succeed. Expressions evaluating to TRUE or UNKNOWN
485 succeed. Should any row of an insert or update operation produce a
486 FALSE result, an error exception is raised and the insert or update
487 does not alter the database. A check constraint specified as a
488 column constraint should reference that column's value only, while
489 an expression appearing in a table constraint can reference
490 multiple columns.
491 Currently, CHECK expressions cannot contain subqueries nor refer to
493 variables other than columns of the current row (see
494 Section 5.4.1). The system column tableoid may be referenced, but
495 not any other system column.
496 A constraint marked with NO INHERIT will not propagate to child
498 tables.
499 When a table has multiple CHECK constraints, they will be tested
501 for each row in alphabetical order by name, after checking NOT NULL
502 constraints. (PostgreSQL versions before 9.5 did not honor any
503 particular firing order for CHECK constraints.)
504 DEFAULT default_expr
506 The DEFAULT clause assigns a default data value for the column
507 whose column definition it appears within. The value is any
508 variable-free expression (in particular, cross-references to other
509 columns in the current table are not allowed). Subqueries are not
510 allowed either. The data type of the default expression must match
511 the data type of the column.
512 The default expression will be used in any insert operation that
514 does not specify a value for the column. If there is no default for
515 a column, then the default is null.
516 GENERATED ALWAYS AS ( generation_expr ) STORED
518 This clause creates the column as a generated column. The column
519 cannot be written to, and when read the result of the specified
520 expression will be returned.
521 The keyword STORED is required to signify that the column will be
523 computed on write and will be stored on disk.
524 The generation expression can refer to other columns in the table,
526 but not other generated columns. Any functions and operators used
527 must be immutable. References to other tables are not allowed.
528 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
530 This clause creates the column as an identity column. It will have
531 an implicit sequence attached to it and the column in new rows will
532 automatically have values from the sequence assigned to it.
533 The clauses ALWAYS and BY DEFAULT determine how the sequence value
535 is given precedence over a user-specified value in an INSERT
536 statement. If ALWAYS is specified, a user-specified value is only
537 accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE.
538 If BY DEFAULT is specified, then the user-specified value takes
539 precedence. See INSERT(7) for details. (In the COPY command,
540 user-specified values are always used regardless of this setting.)
541 The optional sequence_options clause can be used to override the
543 options of the sequence. See CREATE SEQUENCE (CREATE_SEQUENCE(7))
544 for details.
545 UNIQUE (column constraint)
547 UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
548 (table constraint)
549 The UNIQUE constraint specifies that a group of one or more columns
550 of a table can contain only unique values. The behavior of the
551 unique table constraint is the same as that for column constraints,
552 with the additional capability to span multiple columns.
553 For the purpose of a unique constraint, null values are not
555 considered equal.
556 Each unique table constraint must name a set of columns that is
558 different from the set of columns named by any other unique or
559 primary key constraint defined for the table. (Otherwise it would
560 just be the same constraint listed twice.)
561 When establishing a unique constraint for a multi-level partition
563 hierarchy, all the columns in the partition key of the target
564 partitioned table, as well as those of all its descendant
565 partitioned tables, must be included in the constraint definition.
566 Adding a unique constraint will automatically create a unique btree
568 index on the column or group of columns used in the constraint. The
569 optional clause INCLUDE adds to that index one or more columns on
570 which the uniqueness is not enforced. Note that although the
571 constraint is not enforced on the included columns, it still
572 depends on them. Consequently, some operations on these columns
573 (e.g. DROP COLUMN) can cause cascaded constraint and index
574 deletion.
575 PRIMARY KEY (column constraint)
577 PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
578 (table constraint)
579 The PRIMARY KEY constraint specifies that a column or columns of a
580 table can contain only unique (non-duplicate), nonnull values. Only
581 one primary key can be specified for a table, whether as a column
582 constraint or a table constraint.
583 The primary key constraint should name a set of columns that is
585 different from the set of columns named by any unique constraint
586 defined for the same table. (Otherwise, the unique constraint is
587 redundant and will be discarded.)
588 PRIMARY KEY enforces the same data constraints as a combination of
590 UNIQUE and NOT NULL, but identifying a set of columns as the
591 primary key also provides metadata about the design of the schema,
592 since a primary key implies that other tables can rely on this set
593 of columns as a unique identifier for rows.
594 PRIMARY KEY constraints share the restrictions that UNIQUE
596 constraints have when placed on partitioned tables.
597 Adding a PRIMARY KEY constraint will automatically create a unique
599 btree index on the column or group of columns used in the
600 constraint. The optional INCLUDE clause allows a list of columns to
601 be specified which will be included in the non-key portion of the
602 index. Although uniqueness is not enforced on the included columns,
603 the constraint still depends on them. Consequently, some operations
604 on the included columns (e.g. DROP COLUMN) can cause cascaded
605 constraint and index deletion.
606 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
608 ) index_parameters [ WHERE ( predicate ) ]
609 The EXCLUDE clause defines an exclusion constraint, which
610 guarantees that if any two rows are compared on the specified
611 column(s) or expression(s) using the specified operator(s), not all
612 of these comparisons will return TRUE. If all of the specified
613 operators test for equality, this is equivalent to a UNIQUE
614 constraint, although an ordinary unique constraint will be faster.
615 However, exclusion constraints can specify constraints that are
616 more general than simple equality. For example, you can specify a
617 constraint that no two rows in the table contain overlapping
618 circles (see Section 8.8) by using the && operator.
619 Exclusion constraints are implemented using an index, so each
621 specified operator must be associated with an appropriate operator
622 class (see Section 11.10) for the index access method index_method.
623 The operators are required to be commutative. Each exclude_element
624 can optionally specify an operator class and/or ordering options;
625 these are described fully under CREATE INDEX (CREATE_INDEX(7)).
626 The access method must support amgettuple (see Chapter 61); at
628 present this means GIN cannot be used. Although it's allowed, there
629 is little point in using B-tree or hash indexes with an exclusion
630 constraint, because this does nothing that an ordinary unique
631 constraint doesn't do better. So in practice the access method will
632 always be GiST or SP-GiST.
633 The predicate allows you to specify an exclusion constraint on a
635 subset of the table; internally this creates a partial index. Note
636 that parentheses are required around the predicate.
637 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
639 referential_action ] [ ON UPDATE referential_action ] (column
640 constraint)
641 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
642 [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON
643 UPDATE referential_action ] (table constraint)
644 These clauses specify a foreign key constraint, which requires that
645 a group of one or more columns of the new table must only contain
646 values that match values in the referenced column(s) of some row of
647 the referenced table. If the refcolumn list is omitted, the primary
648 key of the reftable is used. The referenced columns must be the
649 columns of a non-deferrable unique or primary key constraint in the
650 referenced table. The user must have REFERENCES permission on the
651 referenced table (either the whole table, or the specific
652 referenced columns). The addition of a foreign key constraint
653 requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note
654 that foreign key constraints cannot be defined between temporary
655 tables and permanent tables.
656 A value inserted into the referencing column(s) is matched against
658 the values of the referenced table and referenced columns using the
659 given match type. There are three match types: MATCH FULL, MATCH
660 PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will
661 not allow one column of a multicolumn foreign key to be null unless
662 all foreign key columns are null; if they are all null, the row is
663 not required to have a match in the referenced table. MATCH SIMPLE
664 allows any of the foreign key columns to be null; if any of them
665 are null, the row is not required to have a match in the referenced
666 table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL
667 constraints can be applied to the referencing column(s) to prevent
668 these cases from arising.)
669 In addition, when the data in the referenced columns is changed,
671 certain actions are performed on the data in this table's columns.
672 The ON DELETE clause specifies the action to perform when a
673 referenced row in the referenced table is being deleted. Likewise,
674 the ON UPDATE clause specifies the action to perform when a
675 referenced column in the referenced table is being updated to a new
676 value. If the row is updated, but the referenced column is not
677 actually changed, no action is done. Referential actions other than
678 the NO ACTION check cannot be deferred, even if the constraint is
679 declared deferrable. There are the following possible actions for
680 each clause:
681 NO ACTION
683 Produce an error indicating that the deletion or update would
684 create a foreign key constraint violation. If the constraint is
685 deferred, this error will be produced at constraint check time
686 if there still exist any referencing rows. This is the default
687 action.
688 RESTRICT
690 Produce an error indicating that the deletion or update would
691 create a foreign key constraint violation. This is the same as
692 NO ACTION except that the check is not deferrable.
693 CASCADE
695 Delete any rows referencing the deleted row, or update the
696 values of the referencing column(s) to the new values of the
697 referenced columns, respectively.
698 SET NULL
700 Set the referencing column(s) to null.
701 SET DEFAULT
703 Set the referencing column(s) to their default values. (There
704 must be a row in the referenced table matching the default
705 values, if they are not null, or the operation will fail.)
706 If the referenced column(s) are changed frequently, it might be
708 wise to add an index to the referencing column(s) so that
709 referential actions associated with the foreign key constraint can
710 be performed more efficiently.
711 DEFERRABLE
713 NOT DEFERRABLE
714 This controls whether the constraint can be deferred. A constraint
715 that is not deferrable will be checked immediately after every
716 command. Checking of constraints that are deferrable can be
717 postponed until the end of the transaction (using the SET
718 CONSTRAINTS (SET_CONSTRAINTS(7)) command). NOT DEFERRABLE is the
719 default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and
720 REFERENCES (foreign key) constraints accept this clause. NOT NULL
721 and CHECK constraints are not deferrable. Note that deferrable
722 constraints cannot be used as conflict arbitrators in an INSERT
723 statement that includes an ON CONFLICT DO UPDATE clause.
724 INITIALLY IMMEDIATE
726 INITIALLY DEFERRED
727 If a constraint is deferrable, this clause specifies the default
728 time to check the constraint. If the constraint is INITIALLY
729 IMMEDIATE, it is checked after each statement. This is the default.
730 If the constraint is INITIALLY DEFERRED, it is checked only at the
731 end of the transaction. The constraint check time can be altered
732 with the SET CONSTRAINTS (SET_CONSTRAINTS(7)) command.
733 USING method
735 This optional clause specifies the table access method to use to
736 store the contents for the new table; the method needs be an access
737 method of type TABLE. See Chapter 60 for more information. If this
738 option is not specified, the default table access method is chosen
739 for the new table. See default_table_access_method for more
740 information.
741 WITH ( storage_parameter [= value] [, ... ] )
743 This clause specifies optional storage parameters for a table or
744 index; see Storage Parameters for more information. For
745 backward-compatibility the WITH clause for a table can also include
746 OIDS=FALSE to specify that rows of the new table should not contain
747 OIDs (object identifiers), OIDS=TRUE is not supported anymore.
748 WITHOUT OIDS
750 This is backward-compatible syntax for declaring a table WITHOUT
751 OIDS, creating a table WITH OIDS is not supported anymore.
752 ON COMMIT
754 The behavior of temporary tables at the end of a transaction block
755 can be controlled using ON COMMIT. The three options are:
756 PRESERVE ROWS
758 No special action is taken at the ends of transactions. This is
759 the default behavior.
760 DELETE ROWS
762 All rows in the temporary table will be deleted at the end of
763 each transaction block. Essentially, an automatic TRUNCATE(7)
764 is done at each commit. When used on a partitioned table, this
765 is not cascaded to its partitions.
766 DROP
768 The temporary table will be dropped at the end of the current
769 transaction block. When used on a partitioned table, this
770 action drops its partitions and when used on tables with
771 inheritance children, it drops the dependent children.
772 TABLESPACE tablespace_name
774 The tablespace_name is the name of the tablespace in which the new
775 table is to be created. If not specified, default_tablespace is
776 consulted, or temp_tablespaces if the table is temporary. For
777 partitioned tables, since no storage is required for the table
778 itself, the tablespace specified overrides default_tablespace as
779 the default tablespace to use for any newly created partitions when
780 no other tablespace is explicitly specified.
781 USING INDEX TABLESPACE tablespace_name
783 This clause allows selection of the tablespace in which the index
784 associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
785 be created. If not specified, default_tablespace is consulted, or
786 temp_tablespaces if the table is temporary.
787 Storage Parameters
789 The WITH clause can specify storage parameters for tables, and for
790 indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
791 Storage parameters for indexes are documented in CREATE INDEX
792 (CREATE_INDEX(7)). The storage parameters currently available for
793 tables are listed below. For many of these parameters, as shown, there
794 is an additional parameter with the same name prefixed with toast.,
795 which controls the behavior of the table's secondary TOAST table, if
796 any (see Section 68.2 for more information about TOAST). If a table
797 parameter value is set and the equivalent toast. parameter is not, the
798 TOAST table will use the table's parameter value. Specifying these
799 parameters for partitioned tables is not supported, but you may specify
800 them for individual leaf partitions.
801 fillfactor (integer)
803 The fillfactor for a table is a percentage between 10 and 100. 100
804 (complete packing) is the default. When a smaller fillfactor is
805 specified, INSERT operations pack table pages only to the indicated
806 percentage; the remaining space on each page is reserved for
807 updating rows on that page. This gives UPDATE a chance to place the
808 updated copy of a row on the same page as the original, which is
809 more efficient than placing it on a different page. For a table
810 whose entries are never updated, complete packing is the best
811 choice, but in heavily updated tables smaller fillfactors are
812 appropriate. This parameter cannot be set for TOAST tables.
813 toast_tuple_target (integer)
815 The toast_tuple_target specifies the minimum tuple length required
816 before we try to move long column values into TOAST tables, and is
817 also the target length we try to reduce the length below once
818 toasting begins. This only affects columns marked as either
819 External or Extended and applies only to new tuples - there is no
820 effect on existing rows. By default this parameter is set to allow
821 at least 4 tuples per block, which with the default blocksize will
822 be 2040 bytes. Valid values are between 128 bytes and the
823 (blocksize - header), by default 8160 bytes. Changing this value
824 may not be useful for very short or very long rows. Note that the
825 default setting is often close to optimal, and it is possible that
826 setting this parameter could have negative effects in some cases.
827 This parameter cannot be set for TOAST tables.
828 parallel_workers (integer)
830 This sets the number of workers that should be used to assist a
831 parallel scan of this table. If not set, the system will determine
832 a value based on the relation size. The actual number of workers
833 chosen by the planner or by utility statements that use parallel
834 scans may be less, for example due to the setting of
835 max_worker_processes.
836 autovacuum_enabled, toast.autovacuum_enabled (boolean)
838 Enables or disables the autovacuum daemon for a particular table.
839 If true, the autovacuum daemon will perform automatic VACUUM and/or
840 ANALYZE operations on this table following the rules discussed in
841 Section 24.1.6. If false, this table will not be autovacuumed,
842 except to prevent transaction ID wraparound. See Section 24.1.5 for
843 more about wraparound prevention. Note that the autovacuum daemon
844 does not run at all (except to prevent transaction ID wraparound)
845 if the autovacuum parameter is false; setting individual tables'
846 storage parameters does not override that. Therefore there is
847 seldom much point in explicitly setting this storage parameter to
848 true, only to false.
849 vacuum_index_cleanup, toast.vacuum_index_cleanup (boolean)
851 Enables or disables index cleanup when VACUUM is run on this table.
852 The default value is true. Disabling index cleanup can speed up
853 VACUUM very significantly, but may also lead to severely bloated
854 indexes if table modifications are frequent. The INDEX_CLEANUP
855 parameter of VACUUM(7), if specified, overrides the value of this
856 option.
857 vacuum_truncate, toast.vacuum_truncate (boolean)
859 Enables or disables vacuum to try to truncate off any empty pages
860 at the end of this table. The default value is true. If true,
861 VACUUM and autovacuum do the truncation and the disk space for the
862 truncated pages is returned to the operating system. Note that the
863 truncation requires ACCESS EXCLUSIVE lock on the table. The
864 TRUNCATE parameter of VACUUM(7), if specified, overrides the value
865 of this option.
866 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
868 (integer)
869 Per-table value for autovacuum_vacuum_threshold parameter.
870 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
872 (float4)
873 Per-table value for autovacuum_vacuum_scale_factor parameter.
874 autovacuum_analyze_threshold (integer)
876 Per-table value for autovacuum_analyze_threshold parameter.
877 autovacuum_analyze_scale_factor (float4)
879 Per-table value for autovacuum_analyze_scale_factor parameter.
880 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
882 (floating point)
883 Per-table value for autovacuum_vacuum_cost_delay parameter.
884 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
886 (integer)
887 Per-table value for autovacuum_vacuum_cost_limit parameter.
888 autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
890 Per-table value for vacuum_freeze_min_age parameter. Note that
891 autovacuum will ignore per-table autovacuum_freeze_min_age
892 parameters that are larger than half the system-wide
893 autovacuum_freeze_max_age setting.
894 autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
896 Per-table value for autovacuum_freeze_max_age parameter. Note that
897 autovacuum will ignore per-table autovacuum_freeze_max_age
898 parameters that are larger than the system-wide setting (it can
899 only be set smaller).
900 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
902 (integer)
903 Per-table value for vacuum_freeze_table_age parameter.
904 autovacuum_multixact_freeze_min_age,
906 toast.autovacuum_multixact_freeze_min_age (integer)
907 Per-table value for vacuum_multixact_freeze_min_age parameter. Note
908 that autovacuum will ignore per-table
909 autovacuum_multixact_freeze_min_age parameters that are larger than
910 half the system-wide autovacuum_multixact_freeze_max_age setting.
911 autovacuum_multixact_freeze_max_age,
913 toast.autovacuum_multixact_freeze_max_age (integer)
914 Per-table value for autovacuum_multixact_freeze_max_age parameter.
915 Note that autovacuum will ignore per-table
916 autovacuum_multixact_freeze_max_age parameters that are larger than
917 the system-wide setting (it can only be set smaller).
918 autovacuum_multixact_freeze_table_age,
920 toast.autovacuum_multixact_freeze_table_age (integer)
921 Per-table value for vacuum_multixact_freeze_table_age parameter.
922 log_autovacuum_min_duration, toast.log_autovacuum_min_duration
924 (integer)
925 Per-table value for log_autovacuum_min_duration parameter.
926 user_catalog_table (boolean)
928 Declare the table as an additional catalog table for purposes of
929 logical replication. See Section 48.6.2 for details. This parameter
930 cannot be set for TOAST tables.
931
933 PostgreSQL automatically creates an index for each unique constraint
934 and primary key constraint to enforce uniqueness. Thus, it is not
935 necessary to create an index explicitly for primary key columns. (See
936 CREATE INDEX (CREATE_INDEX(7)) for more information.)
937 Unique constraints and primary keys are not inherited in the current
939 implementation. This makes the combination of inheritance and unique
940 constraints rather dysfunctional.
941 A table cannot have more than 1600 columns. (In practice, the effective
943 limit is usually lower because of tuple-length constraints.)
944
946 Create table films and table distributors:
947 CREATE TABLE films (
949 code char(5) CONSTRAINT firstkey PRIMARY KEY,
950 title varchar(40) NOT NULL,
951 did integer NOT NULL,
952 date_prod date,
953 kind varchar(10),
954 len interval hour to minute
955 );
956 CREATE TABLE distributors (
958 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
959 name varchar(40) NOT NULL CHECK (name <> '')
960 );
961 Create a table with a 2-dimensional array:
963 CREATE TABLE array_int (
965 vector int[][]
966 );
967 Define a unique table constraint for the table films. Unique table
969 constraints can be defined on one or more columns of the table:
970 CREATE TABLE films (
972 code char(5),
973 title varchar(40),
974 did integer,
975 date_prod date,
976 kind varchar(10),
977 len interval hour to minute,
978 CONSTRAINT production UNIQUE(date_prod)
979 );
980 Define a check column constraint:
982 CREATE TABLE distributors (
984 did integer CHECK (did > 100),
985 name varchar(40)
986 );
987 Define a check table constraint:
989 CREATE TABLE distributors (
991 did integer,
992 name varchar(40),
993 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
994 );
995 Define a primary key table constraint for the table films:
997 CREATE TABLE films (
999 code char(5),
1000 title varchar(40),
1001 did integer,
1002 date_prod date,
1003 kind varchar(10),
1004 len interval hour to minute,
1005 CONSTRAINT code_title PRIMARY KEY(code,title)
1006 );
1007 Define a primary key constraint for table distributors. The following
1009 two examples are equivalent, the first using the table constraint
1010 syntax, the second the column constraint syntax:
1011 CREATE TABLE distributors (
1013 did integer,
1014 name varchar(40),
1015 PRIMARY KEY(did)
1016 );
1017 CREATE TABLE distributors (
1019 did integer PRIMARY KEY,
1020 name varchar(40)
1021 );
1022 Assign a literal constant default value for the column name, arrange
1024 for the default value of column did to be generated by selecting the
1025 next value of a sequence object, and make the default value of modtime
1026 be the time at which the row is inserted:
1027 CREATE TABLE distributors (
1029 name varchar(40) DEFAULT 'Luso Films',
1030 did integer DEFAULT nextval('distributors_serial'),
1031 modtime timestamp DEFAULT current_timestamp
1032 );
1033 Define two NOT NULL column constraints on the table distributors, one
1035 of which is explicitly given a name:
1036 CREATE TABLE distributors (
1038 did integer CONSTRAINT no_null NOT NULL,
1039 name varchar(40) NOT NULL
1040 );
1041 Define a unique constraint for the name column:
1043 CREATE TABLE distributors (
1045 did integer,
1046 name varchar(40) UNIQUE
1047 );
1048 The same, specified as a table constraint:
1050 CREATE TABLE distributors (
1052 did integer,
1053 name varchar(40),
1054 UNIQUE(name)
1055 );
1056 Create the same table, specifying 70% fill factor for both the table
1058 and its unique index:
1059 CREATE TABLE distributors (
1061 did integer,
1062 name varchar(40),
1063 UNIQUE(name) WITH (fillfactor=70)
1064 )
1065 WITH (fillfactor=70);
1066 Create table circles with an exclusion constraint that prevents any two
1068 circles from overlapping:
1069 CREATE TABLE circles (
1071 c circle,
1072 EXCLUDE USING gist (c WITH &&)
1073 );
1074 Create table cinemas in tablespace diskvol1:
1076 CREATE TABLE cinemas (
1078 id serial,
1079 name text,
1080 location text
1081 ) TABLESPACE diskvol1;
1082 Create a composite type and a typed table:
1084 CREATE TYPE employee_type AS (name text, salary numeric);
1086 CREATE TABLE employees OF employee_type (
1088 PRIMARY KEY (name),
1089 salary WITH OPTIONS DEFAULT 1000
1090 );
1091 Create a range partitioned table:
1093 CREATE TABLE measurement (
1095 logdate date not null,
1096 peaktemp int,
1097 unitsales int
1098 ) PARTITION BY RANGE (logdate);
1099 Create a range partitioned table with multiple columns in the partition
1101 key:
1102 CREATE TABLE measurement_year_month (
1104 logdate date not null,
1105 peaktemp int,
1106 unitsales int
1107 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1108 Create a list partitioned table:
1110 CREATE TABLE cities (
1112 city_id bigserial not null,
1113 name text not null,
1114 population bigint
1115 ) PARTITION BY LIST (left(lower(name), 1));
1116 Create a hash partitioned table:
1118 CREATE TABLE orders (
1120 order_id bigint not null,
1121 cust_id bigint not null,
1122 status text
1123 ) PARTITION BY HASH (order_id);
1124 Create partition of a range partitioned table:
1126 CREATE TABLE measurement_y2016m07
1128 PARTITION OF measurement (
1129 unitsales DEFAULT 0
1130 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1131 Create a few partitions of a range partitioned table with multiple
1133 columns in the partition key:
1134 CREATE TABLE measurement_ym_older
1136 PARTITION OF measurement_year_month
1137 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1138 CREATE TABLE measurement_ym_y2016m11
1140 PARTITION OF measurement_year_month
1141 FOR VALUES FROM (2016, 11) TO (2016, 12);
1142 CREATE TABLE measurement_ym_y2016m12
1144 PARTITION OF measurement_year_month
1145 FOR VALUES FROM (2016, 12) TO (2017, 01);
1146 CREATE TABLE measurement_ym_y2017m01
1148 PARTITION OF measurement_year_month
1149 FOR VALUES FROM (2017, 01) TO (2017, 02);
1150 Create partition of a list partitioned table:
1152 CREATE TABLE cities_ab
1154 PARTITION OF cities (
1155 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1156 ) FOR VALUES IN ('a', 'b');
1157 Create partition of a list partitioned table that is itself further
1159 partitioned and then add a partition to it:
1160 CREATE TABLE cities_ab
1162 PARTITION OF cities (
1163 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1164 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1165 CREATE TABLE cities_ab_10000_to_100000
1167 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1168 Create partitions of a hash partitioned table:
1170 CREATE TABLE orders_p1 PARTITION OF orders
1172 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1173 CREATE TABLE orders_p2 PARTITION OF orders
1174 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1175 CREATE TABLE orders_p3 PARTITION OF orders
1176 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1177 CREATE TABLE orders_p4 PARTITION OF orders
1178 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1179 Create a default partition:
1181 CREATE TABLE cities_partdef
1183 PARTITION OF cities DEFAULT;
1184
1186 The CREATE TABLE command conforms to the SQL standard, with exceptions
1187 listed below.
1188 Temporary Tables
1190 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1191 standard, the effect is not the same. In the standard, temporary tables
1192 are defined just once and automatically exist (starting with empty
1193 contents) in every session that needs them. PostgreSQL instead
1194 requires each session to issue its own CREATE TEMPORARY TABLE command
1195 for each temporary table to be used. This allows different sessions to
1196 use the same temporary table name for different purposes, whereas the
1197 standard's approach constrains all instances of a given temporary table
1198 name to have the same table structure.
1199 The standard's definition of the behavior of temporary tables is widely
1201 ignored. PostgreSQL's behavior on this point is similar to that of
1202 several other SQL databases.
1203 The SQL standard also distinguishes between global and local temporary
1205 tables, where a local temporary table has a separate set of contents
1206 for each SQL module within each session, though its definition is still
1207 shared across sessions. Since PostgreSQL does not support SQL modules,
1208 this distinction is not relevant in PostgreSQL.
1209 For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1211 keywords in a temporary table declaration, but they currently have no
1212 effect. Use of these keywords is discouraged, since future versions of
1213 PostgreSQL might adopt a more standard-compliant interpretation of
1214 their meaning.
1215 The ON COMMIT clause for temporary tables also resembles the SQL
1217 standard, but has some differences. If the ON COMMIT clause is omitted,
1218 SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1219 However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1220 The ON COMMIT DROP option does not exist in SQL.
1221 Non-Deferred Uniqueness Constraints
1223 When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1224 checks for uniqueness immediately whenever a row is inserted or
1225 modified. The SQL standard says that uniqueness should be enforced only
1226 at the end of the statement; this makes a difference when, for example,
1227 a single command updates multiple key values. To obtain
1228 standard-compliant behavior, declare the constraint as DEFERRABLE but
1229 not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1230 significantly slower than immediate uniqueness checking.
1231 Column Check Constraints
1233 The SQL standard says that CHECK column constraints can only refer to
1234 the column they apply to; only CHECK table constraints can refer to
1235 multiple columns. PostgreSQL does not enforce this restriction; it
1236 treats column and table check constraints alike.
1237 EXCLUDE Constraint
1239 The EXCLUDE constraint type is a PostgreSQL extension.
1240 NULL “Constraint”
1242 The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1243 extension to the SQL standard that is included for compatibility with
1244 some other database systems (and for symmetry with the NOT NULL
1245 constraint). Since it is the default for any column, its presence is
1246 simply noise.
1247 Constraint Naming
1249 The SQL standard says that table and domain constraints must have names
1250 that are unique across the schema containing the table or domain.
1251 PostgreSQL is laxer: it only requires constraint names to be unique
1252 across the constraints attached to a particular table or domain.
1253 However, this extra freedom does not exist for index-based constraints
1254 (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1255 index is named the same as the constraint, and index names must be
1256 unique across all relations within the same schema.
1257 Currently, PostgreSQL does not record names for NOT NULL constraints at
1259 all, so they are not subject to the uniqueness restriction. This might
1260 change in a future release.
1261 Inheritance
1263 Multiple inheritance via the INHERITS clause is a PostgreSQL language
1264 extension. SQL:1999 and later define single inheritance using a
1265 different syntax and different semantics. SQL:1999-style inheritance is
1266 not yet supported by PostgreSQL.
1267 Zero-Column Tables
1269 PostgreSQL allows a table of no columns to be created (for example,
1270 CREATE TABLE foo();). This is an extension from the SQL standard, which
1271 does not allow zero-column tables. Zero-column tables are not in
1272 themselves very useful, but disallowing them creates odd special cases
1273 for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1274 restriction.
1275 Multiple Identity Columns
1277 PostgreSQL allows a table to have more than one identity column. The
1278 standard specifies that a table can have at most one identity column.
1279 This is relaxed mainly to give more flexibility for doing schema
1280 changes or migrations. Note that the INSERT command supports only one
1281 override clause that applies to the entire statement, so having
1282 multiple identity columns with different behaviors is not well
1283 supported.
1284 Generated Columns
1286 The option STORED is not standard but is also used by other SQL
1287 implementations. The SQL standard does not specify the storage of
1288 generated columns.
1289 LIKE Clause
1291 While a LIKE clause exists in the SQL standard, many of the options
1292 that PostgreSQL accepts for it are not in the standard, and some of the
1293 standard's options are not implemented by PostgreSQL.
1294 WITH Clause
1296 The WITH clause is a PostgreSQL extension; storage parameters are not
1297 in the standard.
1298 Tablespaces
1300 The PostgreSQL concept of tablespaces is not part of the standard.
1301 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1302 extensions.
1303 Typed Tables
1305 Typed tables implement a subset of the SQL standard. According to the
1306 standard, a typed table has columns corresponding to the underlying
1307 composite type as well as one other column that is the
1308 “self-referencing column”. PostgreSQL does not support
1309 self-referencing columns explicitly.
1310 PARTITION BY Clause
1312 The PARTITION BY clause is a PostgreSQL extension.
1313 PARTITION OF Clause
1315 The PARTITION OF clause is a PostgreSQL extension.
1316
1318 ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1319 AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1320 CREATE TYPE (CREATE_TYPE(7))
1321PostgreSQL 12.2 2020 CREATE TABLE(7)