1CREATE TABLE(7) PostgreSQL 13.4 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 below.
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.24.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 69.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. Such a
535 column is implicitly NOT NULL.
536 The clauses ALWAYS and BY DEFAULT determine how explicitly
538 user-specified values are handled in INSERT and UPDATE commands.
539 In an INSERT command, if ALWAYS is selected, a user-specified value
541 is only accepted if the INSERT statement specifies OVERRIDING
542 SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified
543 value takes precedence. See INSERT(7) for details. (In the COPY
544 command, user-specified values are always used regardless of this
545 setting.)
546 In an UPDATE command, if ALWAYS is selected, any update of the
548 column to any value other than DEFAULT will be rejected. If BY
549 DEFAULT is selected, the column can be updated normally. (There is
550 no OVERRIDING clause for the UPDATE command.)
551 The optional sequence_options clause can be used to override the
553 options of the sequence. See CREATE SEQUENCE (CREATE_SEQUENCE(7))
554 for details.
555 UNIQUE (column constraint)
557 UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
558 (table constraint)
559 The UNIQUE constraint specifies that a group of one or more columns
560 of a table can contain only unique values. The behavior of a unique
561 table constraint is the same as that of a unique column constraint,
562 with the additional capability to span multiple columns. The
563 constraint therefore enforces that any two rows must differ in at
564 least one of these columns.
565 For the purpose of a unique constraint, null values are not
567 considered equal.
568 Each unique constraint should name a set of columns that is
570 different from the set of columns named by any other unique or
571 primary key constraint defined for the table. (Otherwise, redundant
572 unique constraints will be discarded.)
573 When establishing a unique constraint for a multi-level partition
575 hierarchy, all the columns in the partition key of the target
576 partitioned table, as well as those of all its descendant
577 partitioned tables, must be included in the constraint definition.
578 Adding a unique constraint will automatically create a unique btree
580 index on the column or group of columns used in the constraint.
581 The optional INCLUDE clause adds to that index one or more columns
583 that are simply “payload”: uniqueness is not enforced on them, and
584 the index cannot be searched on the basis of those columns. However
585 they can be retrieved by an index-only scan. Note that although the
586 constraint is not enforced on included columns, it still depends on
587 them. Consequently, some operations on such columns (e.g., DROP
588 COLUMN) can cause cascaded constraint and index deletion.
589 PRIMARY KEY (column constraint)
591 PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
592 (table constraint)
593 The PRIMARY KEY constraint specifies that a column or columns of a
594 table can contain only unique (non-duplicate), nonnull values. Only
595 one primary key can be specified for a table, whether as a column
596 constraint or a table constraint.
597 The primary key constraint should name a set of columns that is
599 different from the set of columns named by any unique constraint
600 defined for the same table. (Otherwise, the unique constraint is
601 redundant and will be discarded.)
602 PRIMARY KEY enforces the same data constraints as a combination of
604 UNIQUE and NOT NULL. However, identifying a set of columns as the
605 primary key also provides metadata about the design of the schema,
606 since a primary key implies that other tables can rely on this set
607 of columns as a unique identifier for rows.
608 When placed on a partitioned table, PRIMARY KEY constraints share
610 the restrictions previously described for UNIQUE constraints.
611 Adding a PRIMARY KEY constraint will automatically create a unique
613 btree index on the column or group of columns used in the
614 constraint.
615 The optional INCLUDE clause adds to that index one or more columns
617 that are simply “payload”: uniqueness is not enforced on them, and
618 the index cannot be searched on the basis of those columns. However
619 they can be retrieved by an index-only scan. Note that although the
620 constraint is not enforced on included columns, it still depends on
621 them. Consequently, some operations on such columns (e.g., DROP
622 COLUMN) can cause cascaded constraint and index deletion.
623 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
625 ) index_parameters [ WHERE ( predicate ) ]
626 The EXCLUDE clause defines an exclusion constraint, which
627 guarantees that if any two rows are compared on the specified
628 column(s) or expression(s) using the specified operator(s), not all
629 of these comparisons will return TRUE. If all of the specified
630 operators test for equality, this is equivalent to a UNIQUE
631 constraint, although an ordinary unique constraint will be faster.
632 However, exclusion constraints can specify constraints that are
633 more general than simple equality. For example, you can specify a
634 constraint that no two rows in the table contain overlapping
635 circles (see Section 8.8) by using the && operator.
636 Exclusion constraints are implemented using an index, so each
638 specified operator must be associated with an appropriate operator
639 class (see Section 11.10) for the index access method index_method.
640 The operators are required to be commutative. Each exclude_element
641 can optionally specify an operator class and/or ordering options;
642 these are described fully under CREATE INDEX (CREATE_INDEX(7)).
643 The access method must support amgettuple (see Chapter 61); at
645 present this means GIN cannot be used. Although it's allowed, there
646 is little point in using B-tree or hash indexes with an exclusion
647 constraint, because this does nothing that an ordinary unique
648 constraint doesn't do better. So in practice the access method will
649 always be GiST or SP-GiST.
650 The predicate allows you to specify an exclusion constraint on a
652 subset of the table; internally this creates a partial index. Note
653 that parentheses are required around the predicate.
654 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
656 referential_action ] [ ON UPDATE referential_action ] (column
657 constraint)
658 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
659 [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON
660 UPDATE referential_action ] (table constraint)
661 These clauses specify a foreign key constraint, which requires that
662 a group of one or more columns of the new table must only contain
663 values that match values in the referenced column(s) of some row of
664 the referenced table. If the refcolumn list is omitted, the primary
665 key of the reftable is used. The referenced columns must be the
666 columns of a non-deferrable unique or primary key constraint in the
667 referenced table. The user must have REFERENCES permission on the
668 referenced table (either the whole table, or the specific
669 referenced columns). The addition of a foreign key constraint
670 requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note
671 that foreign key constraints cannot be defined between temporary
672 tables and permanent tables.
673 A value inserted into the referencing column(s) is matched against
675 the values of the referenced table and referenced columns using the
676 given match type. There are three match types: MATCH FULL, MATCH
677 PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will
678 not allow one column of a multicolumn foreign key to be null unless
679 all foreign key columns are null; if they are all null, the row is
680 not required to have a match in the referenced table. MATCH SIMPLE
681 allows any of the foreign key columns to be null; if any of them
682 are null, the row is not required to have a match in the referenced
683 table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL
684 constraints can be applied to the referencing column(s) to prevent
685 these cases from arising.)
686 In addition, when the data in the referenced columns is changed,
688 certain actions are performed on the data in this table's columns.
689 The ON DELETE clause specifies the action to perform when a
690 referenced row in the referenced table is being deleted. Likewise,
691 the ON UPDATE clause specifies the action to perform when a
692 referenced column in the referenced table is being updated to a new
693 value. If the row is updated, but the referenced column is not
694 actually changed, no action is done. Referential actions other than
695 the NO ACTION check cannot be deferred, even if the constraint is
696 declared deferrable. There are the following possible actions for
697 each clause:
698 NO ACTION
700 Produce an error indicating that the deletion or update would
701 create a foreign key constraint violation. If the constraint is
702 deferred, this error will be produced at constraint check time
703 if there still exist any referencing rows. This is the default
704 action.
705 RESTRICT
707 Produce an error indicating that the deletion or update would
708 create a foreign key constraint violation. This is the same as
709 NO ACTION except that the check is not deferrable.
710 CASCADE
712 Delete any rows referencing the deleted row, or update the
713 values of the referencing column(s) to the new values of the
714 referenced columns, respectively.
715 SET NULL
717 Set the referencing column(s) to null.
718 SET DEFAULT
720 Set the referencing column(s) to their default values. (There
721 must be a row in the referenced table matching the default
722 values, if they are not null, or the operation will fail.)
723 If the referenced column(s) are changed frequently, it might be
725 wise to add an index to the referencing column(s) so that
726 referential actions associated with the foreign key constraint can
727 be performed more efficiently.
728 DEFERRABLE
730 NOT DEFERRABLE
731 This controls whether the constraint can be deferred. A constraint
732 that is not deferrable will be checked immediately after every
733 command. Checking of constraints that are deferrable can be
734 postponed until the end of the transaction (using the SET
735 CONSTRAINTS (SET_CONSTRAINTS(7)) command). NOT DEFERRABLE is the
736 default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and
737 REFERENCES (foreign key) constraints accept this clause. NOT NULL
738 and CHECK constraints are not deferrable. Note that deferrable
739 constraints cannot be used as conflict arbitrators in an INSERT
740 statement that includes an ON CONFLICT DO UPDATE clause.
741 INITIALLY IMMEDIATE
743 INITIALLY DEFERRED
744 If a constraint is deferrable, this clause specifies the default
745 time to check the constraint. If the constraint is INITIALLY
746 IMMEDIATE, it is checked after each statement. This is the default.
747 If the constraint is INITIALLY DEFERRED, it is checked only at the
748 end of the transaction. The constraint check time can be altered
749 with the SET CONSTRAINTS (SET_CONSTRAINTS(7)) command.
750 USING method
752 This optional clause specifies the table access method to use to
753 store the contents for the new table; the method needs be an access
754 method of type TABLE. See Chapter 60 for more information. If this
755 option is not specified, the default table access method is chosen
756 for the new table. See default_table_access_method for more
757 information.
758 WITH ( storage_parameter [= value] [, ... ] )
760 This clause specifies optional storage parameters for a table or
761 index; see Storage Parameters below for more information. For
762 backward-compatibility the WITH clause for a table can also include
763 OIDS=FALSE to specify that rows of the new table should not contain
764 OIDs (object identifiers), OIDS=TRUE is not supported anymore.
765 WITHOUT OIDS
767 This is backward-compatible syntax for declaring a table WITHOUT
768 OIDS, creating a table WITH OIDS is not supported anymore.
769 ON COMMIT
771 The behavior of temporary tables at the end of a transaction block
772 can be controlled using ON COMMIT. The three options are:
773 PRESERVE ROWS
775 No special action is taken at the ends of transactions. This is
776 the default behavior.
777 DELETE ROWS
779 All rows in the temporary table will be deleted at the end of
780 each transaction block. Essentially, an automatic TRUNCATE(7)
781 is done at each commit. When used on a partitioned table, this
782 is not cascaded to its partitions.
783 DROP
785 The temporary table will be dropped at the end of the current
786 transaction block. When used on a partitioned table, this
787 action drops its partitions and when used on tables with
788 inheritance children, it drops the dependent children.
789 TABLESPACE tablespace_name
791 The tablespace_name is the name of the tablespace in which the new
792 table is to be created. If not specified, default_tablespace is
793 consulted, or temp_tablespaces if the table is temporary. For
794 partitioned tables, since no storage is required for the table
795 itself, the tablespace specified overrides default_tablespace as
796 the default tablespace to use for any newly created partitions when
797 no other tablespace is explicitly specified.
798 USING INDEX TABLESPACE tablespace_name
800 This clause allows selection of the tablespace in which the index
801 associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
802 be created. If not specified, default_tablespace is consulted, or
803 temp_tablespaces if the table is temporary.
804 Storage Parameters
806 The WITH clause can specify storage parameters for tables, and for
807 indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
808 Storage parameters for indexes are documented in CREATE INDEX
809 (CREATE_INDEX(7)). The storage parameters currently available for
810 tables are listed below. For many of these parameters, as shown, there
811 is an additional parameter with the same name prefixed with toast.,
812 which controls the behavior of the table's secondary TOAST table, if
813 any (see Section 69.2 for more information about TOAST). If a table
814 parameter value is set and the equivalent toast. parameter is not, the
815 TOAST table will use the table's parameter value. Specifying these
816 parameters for partitioned tables is not supported, but you may specify
817 them for individual leaf partitions.
818 fillfactor (integer)
820 The fillfactor for a table is a percentage between 10 and 100. 100
821 (complete packing) is the default. When a smaller fillfactor is
822 specified, INSERT operations pack table pages only to the indicated
823 percentage; the remaining space on each page is reserved for
824 updating rows on that page. This gives UPDATE a chance to place the
825 updated copy of a row on the same page as the original, which is
826 more efficient than placing it on a different page. For a table
827 whose entries are never updated, complete packing is the best
828 choice, but in heavily updated tables smaller fillfactors are
829 appropriate. This parameter cannot be set for TOAST tables.
830 toast_tuple_target (integer)
832 The toast_tuple_target specifies the minimum tuple length required
833 before we try to compress and/or move long column values into TOAST
834 tables, and is also the target length we try to reduce the length
835 below once toasting begins. This affects columns marked as External
836 (for move), Main (for compression), or Extended (for both) and
837 applies only to new tuples. There is no effect on existing rows. By
838 default this parameter is set to allow at least 4 tuples per block,
839 which with the default block size will be 2040 bytes. Valid values
840 are between 128 bytes and the (block size - header), by default
841 8160 bytes. Changing this value may not be useful for very short or
842 very long rows. Note that the default setting is often close to
843 optimal, and it is possible that setting this parameter could have
844 negative effects in some cases. This parameter cannot be set for
845 TOAST tables.
846 parallel_workers (integer)
848 This sets the number of workers that should be used to assist a
849 parallel scan of this table. If not set, the system will determine
850 a value based on the relation size. The actual number of workers
851 chosen by the planner or by utility statements that use parallel
852 scans may be less, for example due to the setting of
853 max_worker_processes.
854 autovacuum_enabled, toast.autovacuum_enabled (boolean)
856 Enables or disables the autovacuum daemon for a particular table.
857 If true, the autovacuum daemon will perform automatic VACUUM and/or
858 ANALYZE operations on this table following the rules discussed in
859 Section 24.1.6. If false, this table will not be autovacuumed,
860 except to prevent transaction ID wraparound. See Section 24.1.5 for
861 more about wraparound prevention. Note that the autovacuum daemon
862 does not run at all (except to prevent transaction ID wraparound)
863 if the autovacuum parameter is false; setting individual tables'
864 storage parameters does not override that. Therefore there is
865 seldom much point in explicitly setting this storage parameter to
866 true, only to false.
867 vacuum_index_cleanup, toast.vacuum_index_cleanup (boolean)
869 Enables or disables index cleanup when VACUUM is run on this table.
870 The default value is true. Disabling index cleanup can speed up
871 VACUUM very significantly, but may also lead to severely bloated
872 indexes if table modifications are frequent. The INDEX_CLEANUP
873 parameter of VACUUM(7), if specified, overrides the value of this
874 option.
875 vacuum_truncate, toast.vacuum_truncate (boolean)
877 Enables or disables vacuum to try to truncate off any empty pages
878 at the end of this table. The default value is true. If true,
879 VACUUM and autovacuum do the truncation and the disk space for the
880 truncated pages is returned to the operating system. Note that the
881 truncation requires ACCESS EXCLUSIVE lock on the table. The
882 TRUNCATE parameter of VACUUM(7), if specified, overrides the value
883 of this option.
884 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
886 (integer)
887 Per-table value for autovacuum_vacuum_threshold parameter.
888 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
890 (floating point)
891 Per-table value for autovacuum_vacuum_scale_factor parameter.
892 autovacuum_vacuum_insert_threshold,
894 toast.autovacuum_vacuum_insert_threshold (integer)
895 Per-table value for autovacuum_vacuum_insert_threshold parameter.
896 The special value of -1 may be used to disable insert vacuums on
897 the table.
898 autovacuum_vacuum_insert_scale_factor,
900 toast.autovacuum_vacuum_insert_scale_factor (floating point)
901 Per-table value for autovacuum_vacuum_insert_scale_factor
902 parameter.
903 autovacuum_analyze_threshold (integer)
905 Per-table value for autovacuum_analyze_threshold parameter.
906 autovacuum_analyze_scale_factor (floating point)
908 Per-table value for autovacuum_analyze_scale_factor parameter.
909 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
911 (floating point)
912 Per-table value for autovacuum_vacuum_cost_delay parameter.
913 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
915 (integer)
916 Per-table value for autovacuum_vacuum_cost_limit parameter.
917 autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
919 Per-table value for vacuum_freeze_min_age parameter. Note that
920 autovacuum will ignore per-table autovacuum_freeze_min_age
921 parameters that are larger than half the system-wide
922 autovacuum_freeze_max_age setting.
923 autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
925 Per-table value for autovacuum_freeze_max_age parameter. Note that
926 autovacuum will ignore per-table autovacuum_freeze_max_age
927 parameters that are larger than the system-wide setting (it can
928 only be set smaller).
929 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
931 (integer)
932 Per-table value for vacuum_freeze_table_age parameter.
933 autovacuum_multixact_freeze_min_age,
935 toast.autovacuum_multixact_freeze_min_age (integer)
936 Per-table value for vacuum_multixact_freeze_min_age parameter. Note
937 that autovacuum will ignore per-table
938 autovacuum_multixact_freeze_min_age parameters that are larger than
939 half the system-wide autovacuum_multixact_freeze_max_age setting.
940 autovacuum_multixact_freeze_max_age,
942 toast.autovacuum_multixact_freeze_max_age (integer)
943 Per-table value for autovacuum_multixact_freeze_max_age parameter.
944 Note that autovacuum will ignore per-table
945 autovacuum_multixact_freeze_max_age parameters that are larger than
946 the system-wide setting (it can only be set smaller).
947 autovacuum_multixact_freeze_table_age,
949 toast.autovacuum_multixact_freeze_table_age (integer)
950 Per-table value for vacuum_multixact_freeze_table_age parameter.
951 log_autovacuum_min_duration, toast.log_autovacuum_min_duration
953 (integer)
954 Per-table value for log_autovacuum_min_duration parameter.
955 user_catalog_table (boolean)
957 Declare the table as an additional catalog table for purposes of
958 logical replication. See Section 48.6.2 for details. This parameter
959 cannot be set for TOAST tables.
960
962 PostgreSQL automatically creates an index for each unique constraint
963 and primary key constraint to enforce uniqueness. Thus, it is not
964 necessary to create an index explicitly for primary key columns. (See
965 CREATE INDEX (CREATE_INDEX(7)) for more information.)
966 Unique constraints and primary keys are not inherited in the current
968 implementation. This makes the combination of inheritance and unique
969 constraints rather dysfunctional.
970 A table cannot have more than 1600 columns. (In practice, the effective
972 limit is usually lower because of tuple-length constraints.)
973
975 Create table films and table distributors:
976 CREATE TABLE films (
978 code char(5) CONSTRAINT firstkey PRIMARY KEY,
979 title varchar(40) NOT NULL,
980 did integer NOT NULL,
981 date_prod date,
982 kind varchar(10),
983 len interval hour to minute
984 );
985 CREATE TABLE distributors (
987 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
988 name varchar(40) NOT NULL CHECK (name <> '')
989 );
990 Create a table with a 2-dimensional array:
992 CREATE TABLE array_int (
994 vector int[][]
995 );
996 Define a unique table constraint for the table films. Unique table
998 constraints can be defined on one or more columns of the table:
999 CREATE TABLE films (
1001 code char(5),
1002 title varchar(40),
1003 did integer,
1004 date_prod date,
1005 kind varchar(10),
1006 len interval hour to minute,
1007 CONSTRAINT production UNIQUE(date_prod)
1008 );
1009 Define a check column constraint:
1011 CREATE TABLE distributors (
1013 did integer CHECK (did > 100),
1014 name varchar(40)
1015 );
1016 Define a check table constraint:
1018 CREATE TABLE distributors (
1020 did integer,
1021 name varchar(40),
1022 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
1023 );
1024 Define a primary key table constraint for the table films:
1026 CREATE TABLE films (
1028 code char(5),
1029 title varchar(40),
1030 did integer,
1031 date_prod date,
1032 kind varchar(10),
1033 len interval hour to minute,
1034 CONSTRAINT code_title PRIMARY KEY(code,title)
1035 );
1036 Define a primary key constraint for table distributors. The following
1038 two examples are equivalent, the first using the table constraint
1039 syntax, the second the column constraint syntax:
1040 CREATE TABLE distributors (
1042 did integer,
1043 name varchar(40),
1044 PRIMARY KEY(did)
1045 );
1046 CREATE TABLE distributors (
1048 did integer PRIMARY KEY,
1049 name varchar(40)
1050 );
1051 Assign a literal constant default value for the column name, arrange
1053 for the default value of column did to be generated by selecting the
1054 next value of a sequence object, and make the default value of modtime
1055 be the time at which the row is inserted:
1056 CREATE TABLE distributors (
1058 name varchar(40) DEFAULT 'Luso Films',
1059 did integer DEFAULT nextval('distributors_serial'),
1060 modtime timestamp DEFAULT current_timestamp
1061 );
1062 Define two NOT NULL column constraints on the table distributors, one
1064 of which is explicitly given a name:
1065 CREATE TABLE distributors (
1067 did integer CONSTRAINT no_null NOT NULL,
1068 name varchar(40) NOT NULL
1069 );
1070 Define a unique constraint for the name column:
1072 CREATE TABLE distributors (
1074 did integer,
1075 name varchar(40) UNIQUE
1076 );
1077 The same, specified as a table constraint:
1079 CREATE TABLE distributors (
1081 did integer,
1082 name varchar(40),
1083 UNIQUE(name)
1084 );
1085 Create the same table, specifying 70% fill factor for both the table
1087 and its unique index:
1088 CREATE TABLE distributors (
1090 did integer,
1091 name varchar(40),
1092 UNIQUE(name) WITH (fillfactor=70)
1093 )
1094 WITH (fillfactor=70);
1095 Create table circles with an exclusion constraint that prevents any two
1097 circles from overlapping:
1098 CREATE TABLE circles (
1100 c circle,
1101 EXCLUDE USING gist (c WITH &&)
1102 );
1103 Create table cinemas in tablespace diskvol1:
1105 CREATE TABLE cinemas (
1107 id serial,
1108 name text,
1109 location text
1110 ) TABLESPACE diskvol1;
1111 Create a composite type and a typed table:
1113 CREATE TYPE employee_type AS (name text, salary numeric);
1115 CREATE TABLE employees OF employee_type (
1117 PRIMARY KEY (name),
1118 salary WITH OPTIONS DEFAULT 1000
1119 );
1120 Create a range partitioned table:
1122 CREATE TABLE measurement (
1124 logdate date not null,
1125 peaktemp int,
1126 unitsales int
1127 ) PARTITION BY RANGE (logdate);
1128 Create a range partitioned table with multiple columns in the partition
1130 key:
1131 CREATE TABLE measurement_year_month (
1133 logdate date not null,
1134 peaktemp int,
1135 unitsales int
1136 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1137 Create a list partitioned table:
1139 CREATE TABLE cities (
1141 city_id bigserial not null,
1142 name text not null,
1143 population bigint
1144 ) PARTITION BY LIST (left(lower(name), 1));
1145 Create a hash partitioned table:
1147 CREATE TABLE orders (
1149 order_id bigint not null,
1150 cust_id bigint not null,
1151 status text
1152 ) PARTITION BY HASH (order_id);
1153 Create partition of a range partitioned table:
1155 CREATE TABLE measurement_y2016m07
1157 PARTITION OF measurement (
1158 unitsales DEFAULT 0
1159 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1160 Create a few partitions of a range partitioned table with multiple
1162 columns in the partition key:
1163 CREATE TABLE measurement_ym_older
1165 PARTITION OF measurement_year_month
1166 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1167 CREATE TABLE measurement_ym_y2016m11
1169 PARTITION OF measurement_year_month
1170 FOR VALUES FROM (2016, 11) TO (2016, 12);
1171 CREATE TABLE measurement_ym_y2016m12
1173 PARTITION OF measurement_year_month
1174 FOR VALUES FROM (2016, 12) TO (2017, 01);
1175 CREATE TABLE measurement_ym_y2017m01
1177 PARTITION OF measurement_year_month
1178 FOR VALUES FROM (2017, 01) TO (2017, 02);
1179 Create partition of a list partitioned table:
1181 CREATE TABLE cities_ab
1183 PARTITION OF cities (
1184 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1185 ) FOR VALUES IN ('a', 'b');
1186 Create partition of a list partitioned table that is itself further
1188 partitioned and then add a partition to it:
1189 CREATE TABLE cities_ab
1191 PARTITION OF cities (
1192 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1193 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1194 CREATE TABLE cities_ab_10000_to_100000
1196 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1197 Create partitions of a hash partitioned table:
1199 CREATE TABLE orders_p1 PARTITION OF orders
1201 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1202 CREATE TABLE orders_p2 PARTITION OF orders
1203 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1204 CREATE TABLE orders_p3 PARTITION OF orders
1205 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1206 CREATE TABLE orders_p4 PARTITION OF orders
1207 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1208 Create a default partition:
1210 CREATE TABLE cities_partdef
1212 PARTITION OF cities DEFAULT;
1213
1215 The CREATE TABLE command conforms to the SQL standard, with exceptions
1216 listed below.
1217 Temporary Tables
1219 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1220 standard, the effect is not the same. In the standard, temporary tables
1221 are defined just once and automatically exist (starting with empty
1222 contents) in every session that needs them. PostgreSQL instead
1223 requires each session to issue its own CREATE TEMPORARY TABLE command
1224 for each temporary table to be used. This allows different sessions to
1225 use the same temporary table name for different purposes, whereas the
1226 standard's approach constrains all instances of a given temporary table
1227 name to have the same table structure.
1228 The standard's definition of the behavior of temporary tables is widely
1230 ignored. PostgreSQL's behavior on this point is similar to that of
1231 several other SQL databases.
1232 The SQL standard also distinguishes between global and local temporary
1234 tables, where a local temporary table has a separate set of contents
1235 for each SQL module within each session, though its definition is still
1236 shared across sessions. Since PostgreSQL does not support SQL modules,
1237 this distinction is not relevant in PostgreSQL.
1238 For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1240 keywords in a temporary table declaration, but they currently have no
1241 effect. Use of these keywords is discouraged, since future versions of
1242 PostgreSQL might adopt a more standard-compliant interpretation of
1243 their meaning.
1244 The ON COMMIT clause for temporary tables also resembles the SQL
1246 standard, but has some differences. If the ON COMMIT clause is omitted,
1247 SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1248 However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1249 The ON COMMIT DROP option does not exist in SQL.
1250 Non-Deferred Uniqueness Constraints
1252 When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1253 checks for uniqueness immediately whenever a row is inserted or
1254 modified. The SQL standard says that uniqueness should be enforced only
1255 at the end of the statement; this makes a difference when, for example,
1256 a single command updates multiple key values. To obtain
1257 standard-compliant behavior, declare the constraint as DEFERRABLE but
1258 not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1259 significantly slower than immediate uniqueness checking.
1260 Column Check Constraints
1262 The SQL standard says that CHECK column constraints can only refer to
1263 the column they apply to; only CHECK table constraints can refer to
1264 multiple columns. PostgreSQL does not enforce this restriction; it
1265 treats column and table check constraints alike.
1266 EXCLUDE Constraint
1268 The EXCLUDE constraint type is a PostgreSQL extension.
1269 NULL “Constraint”
1271 The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1272 extension to the SQL standard that is included for compatibility with
1273 some other database systems (and for symmetry with the NOT NULL
1274 constraint). Since it is the default for any column, its presence is
1275 simply noise.
1276 Constraint Naming
1278 The SQL standard says that table and domain constraints must have names
1279 that are unique across the schema containing the table or domain.
1280 PostgreSQL is laxer: it only requires constraint names to be unique
1281 across the constraints attached to a particular table or domain.
1282 However, this extra freedom does not exist for index-based constraints
1283 (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1284 index is named the same as the constraint, and index names must be
1285 unique across all relations within the same schema.
1286 Currently, PostgreSQL does not record names for NOT NULL constraints at
1288 all, so they are not subject to the uniqueness restriction. This might
1289 change in a future release.
1290 Inheritance
1292 Multiple inheritance via the INHERITS clause is a PostgreSQL language
1293 extension. SQL:1999 and later define single inheritance using a
1294 different syntax and different semantics. SQL:1999-style inheritance is
1295 not yet supported by PostgreSQL.
1296 Zero-Column Tables
1298 PostgreSQL allows a table of no columns to be created (for example,
1299 CREATE TABLE foo();). This is an extension from the SQL standard, which
1300 does not allow zero-column tables. Zero-column tables are not in
1301 themselves very useful, but disallowing them creates odd special cases
1302 for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1303 restriction.
1304 Multiple Identity Columns
1306 PostgreSQL allows a table to have more than one identity column. The
1307 standard specifies that a table can have at most one identity column.
1308 This is relaxed mainly to give more flexibility for doing schema
1309 changes or migrations. Note that the INSERT command supports only one
1310 override clause that applies to the entire statement, so having
1311 multiple identity columns with different behaviors is not well
1312 supported.
1313 Generated Columns
1315 The option STORED is not standard but is also used by other SQL
1316 implementations. The SQL standard does not specify the storage of
1317 generated columns.
1318 LIKE Clause
1320 While a LIKE clause exists in the SQL standard, many of the options
1321 that PostgreSQL accepts for it are not in the standard, and some of the
1322 standard's options are not implemented by PostgreSQL.
1323 WITH Clause
1325 The WITH clause is a PostgreSQL extension; storage parameters are not
1326 in the standard.
1327 Tablespaces
1329 The PostgreSQL concept of tablespaces is not part of the standard.
1330 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1331 extensions.
1332 Typed Tables
1334 Typed tables implement a subset of the SQL standard. According to the
1335 standard, a typed table has columns corresponding to the underlying
1336 composite type as well as one other column that is the
1337 “self-referencing column”. PostgreSQL does not support
1338 self-referencing columns explicitly.
1339 PARTITION BY Clause
1341 The PARTITION BY clause is a PostgreSQL extension.
1342 PARTITION OF Clause
1344 The PARTITION OF clause is a PostgreSQL extension.
1345
1347 ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1348 AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1349 CREATE TYPE (CREATE_TYPE(7))
1350PostgreSQL 13.4 2021 CREATE TABLE(7)