1CREATE TABLE(7) PostgreSQL 14.3 Documentation CREATE TABLE(7)
2
6 CREATE_TABLE - define a new table
7
9 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
10 { column_name data_type [ COMPRESSION compression_method ] [ 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 | COMPRESSION | 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). The default search_path includes the temporary schema first
131 and so identically named existing permanent tables are not chosen
132 for new plans while the temporary table exists, unless they are
133 referenced with schema-qualified names. Any indexes created on a
134 temporary table are automatically temporary as well.
135 The autovacuum daemon cannot access and therefore cannot vacuum or
137 analyze temporary tables. For this reason, appropriate vacuum and
138 analyze operations should be performed via session SQL commands.
139 For example, if a temporary table is going to be used in complex
140 queries, it is wise to run ANALYZE on the temporary table after it
141 is populated.
142 Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
144 TEMP. This presently makes no difference in PostgreSQL and is
145 deprecated; see Compatibility below.
146 UNLOGGED
148 If specified, the table is created as an unlogged table. Data
149 written to unlogged tables is not written to the write-ahead log
150 (see Chapter 30), which makes them considerably faster than
151 ordinary tables. However, they are not crash-safe: an unlogged
152 table is automatically truncated after a crash or unclean shutdown.
153 The contents of an unlogged table are also not replicated to
154 standby servers. Any indexes created on an unlogged table are
155 automatically unlogged as well.
156 IF NOT EXISTS
158 Do not throw an error if a relation with the same name already
159 exists. A notice is issued in this case. Note that there is no
160 guarantee that the existing relation is anything like the one that
161 would have been created.
162 table_name
164 The name (optionally schema-qualified) of the table to be created.
165 OF type_name
167 Creates a typed table, which takes its structure from the specified
168 composite type (name optionally schema-qualified). A typed table is
169 tied to its type; for example the table will be dropped if the type
170 is dropped (with DROP TYPE ... CASCADE).
171 When a typed table is created, then the data types of the columns
173 are determined by the underlying composite type and are not
174 specified by the CREATE TABLE command. But the CREATE TABLE command
175 can add defaults and constraints to the table and can specify
176 storage parameters.
177 column_name
179 The name of a column to be created in the new table.
180 data_type
182 The data type of the column. This can include array specifiers. For
183 more information on the data types supported by PostgreSQL, refer
184 to Chapter 8.
185 COLLATE collation
187 The COLLATE clause assigns a collation to the column (which must be
188 of a collatable data type). If not specified, the column data
189 type's default collation is used.
190 COMPRESSION compression_method
192 The COMPRESSION clause sets the compression method for the column.
193 Compression is supported only for variable-width data types, and is
194 used only when the column's storage mode is main or extended. (See
195 ALTER TABLE (ALTER_TABLE(7)) for information on column storage
196 modes.) Setting this property for a partitioned table has no direct
197 effect, because such tables have no storage of their own, but the
198 configured value will be inherited by newly-created partitions. The
199 supported compression methods are pglz and lz4. (lz4 is available
200 only if --with-lz4 was used when building PostgreSQL.) In addition,
201 compression_method can be default to explicitly specify the default
202 behavior, which is to consult the default_toast_compression setting
203 at the time of data insertion to determine the method to use.
204 INHERITS ( parent_table [, ... ] )
206 The optional INHERITS clause specifies a list of tables from which
207 the new table automatically inherits all columns. Parent tables can
208 be plain tables or foreign tables.
209 Use of INHERITS creates a persistent relationship between the new
211 child table and its parent table(s). Schema modifications to the
212 parent(s) normally propagate to children as well, and by default
213 the data of the child table is included in scans of the parent(s).
214 If the same column name exists in more than one parent table, an
216 error is reported unless the data types of the columns match in
217 each of the parent tables. If there is no conflict, then the
218 duplicate columns are merged to form a single column in the new
219 table. If the column name list of the new table contains a column
220 name that is also inherited, the data type must likewise match the
221 inherited column(s), and the column definitions are merged into
222 one. If the new table explicitly specifies a default value for the
223 column, this default overrides any defaults from inherited
224 declarations of the column. Otherwise, any parents that specify
225 default values for the column must all specify the same default, or
226 an error will be reported.
227 CHECK constraints are merged in essentially the same way as
229 columns: if multiple parent tables and/or the new table definition
230 contain identically-named CHECK constraints, these constraints must
231 all have the same check expression, or an error will be reported.
232 Constraints having the same name and expression will be merged into
233 one copy. A constraint marked NO INHERIT in a parent will not be
234 considered. Notice that an unnamed CHECK constraint in the new
235 table will never be merged, since a unique name will always be
236 chosen for it.
237 Column STORAGE settings are also copied from parent tables.
239 If a column in the parent table is an identity column, that
241 property is not inherited. A column in the child table can be
242 declared identity column if desired.
243 PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
245 [ opclass ] [, ...] )
246 The optional PARTITION BY clause specifies a strategy of
247 partitioning the table. The table thus created is called a
248 partitioned table. The parenthesized list of columns or expressions
249 forms the partition key for the table. When using range or hash
250 partitioning, the partition key can include multiple columns or
251 expressions (up to 32, but this limit can be altered when building
252 PostgreSQL), but for list partitioning, the partition key must
253 consist of a single column or expression.
254 Range and list partitioning require a btree operator class, while
256 hash partitioning requires a hash operator class. If no operator
257 class is specified explicitly, the default operator class of the
258 appropriate type will be used; if no default operator class exists,
259 an error will be raised. When hash partitioning is used, the
260 operator class used must implement support function 2 (see
261 Section 38.16.3 for details).
262 A partitioned table is divided into sub-tables (called partitions),
264 which are created using separate CREATE TABLE commands. The
265 partitioned table is itself empty. A data row inserted into the
266 table is routed to a partition based on the value of columns or
267 expressions in the partition key. If no existing partition matches
268 the values in the new row, an error will be reported.
269 Partitioned tables do not support EXCLUDE constraints; however, you
271 can define these constraints on individual partitions.
272 See Section 5.11 for more discussion on table partitioning.
274 PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
276 Creates the table as a partition of the specified parent table. The
277 table can be created either as a partition for specific values
278 using FOR VALUES or as a default partition using DEFAULT. Any
279 indexes, constraints and user-defined row-level triggers that exist
280 in the parent table are cloned on the new partition.
281 The partition_bound_spec must correspond to the partitioning method
283 and partition key of the parent table, and must not overlap with
284 any existing partition of that parent. The form with IN is used for
285 list partitioning, the form with FROM and TO is used for range
286 partitioning, and the form with WITH is used for hash partitioning.
287 partition_bound_expr is any variable-free expression (subqueries,
289 window functions, aggregate functions, and set-returning functions
290 are not allowed). Its data type must match the data type of the
291 corresponding partition key column. The expression is evaluated
292 once at table creation time, so it can even contain volatile
293 expressions such as CURRENT_TIMESTAMP.
294 When creating a list partition, NULL can be specified to signify
296 that the partition allows the partition key column to be null.
297 However, there cannot be more than one such list partition for a
298 given parent table. NULL cannot be specified for range partitions.
299 When creating a range partition, the lower bound specified with
301 FROM is an inclusive bound, whereas the upper bound specified with
302 TO is an exclusive bound. That is, the values specified in the FROM
303 list are valid values of the corresponding partition key columns
304 for this partition, whereas those in the TO list are not. Note that
305 this statement must be understood according to the rules of
306 row-wise comparison (Section 9.24.5). For example, given PARTITION
307 BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1
308 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
309 The special values MINVALUE and MAXVALUE may be used when creating
311 a range partition to indicate that there is no lower or upper bound
312 on the column's value. For example, a partition defined using FROM
313 (MINVALUE) TO (10) allows any values less than 10, and a partition
314 defined using FROM (10) TO (MAXVALUE) allows any values greater
315 than or equal to 10.
316 When creating a range partition involving more than one column, it
318 can also make sense to use MAXVALUE as part of the lower bound, and
319 MINVALUE as part of the upper bound. For example, a partition
320 defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows
321 where the first partition key column is greater than 0 and less
322 than or equal to 10. Similarly, a partition defined using FROM
323 ('a', MINVALUE) TO ('b', MINVALUE) allows any rows where the first
324 partition key column starts with "a".
325 Note that if MINVALUE or MAXVALUE is used for one column of a
327 partitioning bound, the same value must be used for all subsequent
328 columns. For example, (10, MINVALUE, 0) is not a valid bound; you
329 should write (10, MINVALUE, MINVALUE).
330 Also note that some element types, such as timestamp, have a notion
332 of "infinity", which is just another value that can be stored. This
333 is different from MINVALUE and MAXVALUE, which are not real values
334 that can be stored, but rather they are ways of saying that the
335 value is unbounded. MAXVALUE can be thought of as being greater
336 than any other value, including "infinity" and MINVALUE as being
337 less than any other value, including "minus infinity". Thus the
338 range FROM ('infinity') TO (MAXVALUE) is not an empty range; it
339 allows precisely one value to be stored — "infinity".
340 If DEFAULT is specified, the table will be created as the default
342 partition of the parent table. This option is not available for
343 hash-partitioned tables. A partition key value not fitting into any
344 other partition of the given parent will be routed to the default
345 partition.
346 When a table has an existing DEFAULT partition and a new partition
348 is added to it, the default partition must be scanned to verify
349 that it does not contain any rows which properly belong in the new
350 partition. If the default partition contains a large number of
351 rows, this may be slow. The scan will be skipped if the default
352 partition is a foreign table or if it has a constraint which proves
353 that it cannot contain rows which should be placed in the new
354 partition.
355 When creating a hash partition, a modulus and remainder must be
357 specified. The modulus must be a positive integer, and the
358 remainder must be a non-negative integer less than the modulus.
359 Typically, when initially setting up a hash-partitioned table, you
360 should choose a modulus equal to the number of partitions and
361 assign every table the same modulus and a different remainder (see
362 examples, below). However, it is not required that every partition
363 have the same modulus, only that every modulus which occurs among
364 the partitions of a hash-partitioned table is a factor of the next
365 larger modulus. This allows the number of partitions to be
366 increased incrementally without needing to move all the data at
367 once. For example, suppose you have a hash-partitioned table with 8
368 partitions, each of which has modulus 8, but find it necessary to
369 increase the number of partitions to 16. You can detach one of the
370 modulus-8 partitions, create two new modulus-16 partitions covering
371 the same portion of the key space (one with a remainder equal to
372 the remainder of the detached partition, and the other with a
373 remainder equal to that value plus 8), and repopulate them with
374 data. You can then repeat this -- perhaps at a later time -- for
375 each modulus-8 partition until none remain. While this may still
376 involve a large amount of data movement at each step, it is still
377 better than having to create a whole new table and move all the
378 data at once.
379 A partition must have the same column names and types as the
381 partitioned table to which it belongs. Modifications to the column
382 names or types of a partitioned table will automatically propagate
383 to all partitions. CHECK constraints will be inherited
384 automatically by every partition, but an individual partition may
385 specify additional CHECK constraints; additional constraints with
386 the same name and condition as in the parent will be merged with
387 the parent constraint. Defaults may be specified separately for
388 each partition. But note that a partition's default value is not
389 applied when inserting a tuple through a partitioned table.
390 Rows inserted into a partitioned table will be automatically routed
392 to the correct partition. If no suitable partition exists, an error
393 will occur.
394 Operations such as TRUNCATE which normally affect a table and all
396 of its inheritance children will cascade to all partitions, but may
397 also be performed on an individual partition. Note that dropping a
398 partition with DROP TABLE requires taking an ACCESS EXCLUSIVE lock
399 on the parent table.
400 LIKE source_table [ like_option ... ]
402 The LIKE clause specifies a table from which the new table
403 automatically copies all column names, their data types, and their
404 not-null constraints.
405 Unlike INHERITS, the new table and original table are completely
407 decoupled after creation is complete. Changes to the original table
408 will not be applied to the new table, and it is not possible to
409 include data of the new table in scans of the original table.
410 Also unlike INHERITS, columns and constraints copied by LIKE are
412 not merged with similarly named columns and constraints. If the
413 same name is specified explicitly or in another LIKE clause, an
414 error is signaled.
415 The optional like_option clauses specify which additional
417 properties of the original table to copy. Specifying INCLUDING
418 copies the property, specifying EXCLUDING omits the property.
419 EXCLUDING is the default. If multiple specifications are made for
420 the same kind of object, the last one is used. The available
421 options are:
422 INCLUDING COMMENTS
424 Comments for the copied columns, constraints, and indexes will
425 be copied. The default behavior is to exclude comments,
426 resulting in the copied columns and constraints in the new
427 table having no comments.
428 INCLUDING COMPRESSION
430 Compression method of the columns will be copied. The default
431 behavior is to exclude compression methods, resulting in
432 columns having the default compression method.
433 INCLUDING CONSTRAINTS
435 CHECK constraints will be copied. No distinction is made
436 between column constraints and table constraints. Not-null
437 constraints are always copied to the new table.
438 INCLUDING DEFAULTS
440 Default expressions for the copied column definitions will be
441 copied. Otherwise, default expressions are not copied,
442 resulting in the copied columns in the new table having null
443 defaults. Note that copying defaults that call
444 database-modification functions, such as nextval, may create a
445 functional linkage between the original and new tables.
446 INCLUDING GENERATED
448 Any generation expressions of copied column definitions will be
449 copied. By default, new columns will be regular base columns.
450 INCLUDING IDENTITY
452 Any identity specifications of copied column definitions will
453 be copied. A new sequence is created for each identity column
454 of the new table, separate from the sequences associated with
455 the old table.
456 INCLUDING INDEXES
458 Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
459 original table will be created on the new table. Names for the
460 new indexes and constraints are chosen according to the default
461 rules, regardless of how the originals were named. (This
462 behavior avoids possible duplicate-name failures for the new
463 indexes.)
464 INCLUDING STATISTICS
466 Extended statistics are copied to the new table.
467 INCLUDING STORAGE
469 STORAGE settings for the copied column definitions will be
470 copied. The default behavior is to exclude STORAGE settings,
471 resulting in the copied columns in the new table having
472 type-specific default settings. For more on STORAGE settings,
473 see Section 70.2.
474 INCLUDING ALL
476 INCLUDING ALL is an abbreviated form selecting all the
477 available individual options. (It could be useful to write
478 individual EXCLUDING clauses after INCLUDING ALL to select all
479 but some specific options.)
480 The LIKE clause can also be used to copy column definitions from
482 views, foreign tables, or composite types. Inapplicable options
483 (e.g., INCLUDING INDEXES from a view) are ignored.
484 CONSTRAINT constraint_name
486 An optional name for a column or table constraint. If the
487 constraint is violated, the constraint name is present in error
488 messages, so constraint names like col must be positive can be used
489 to communicate helpful constraint information to client
490 applications. (Double-quotes are needed to specify constraint names
491 that contain spaces.) If a constraint name is not specified, the
492 system generates a name.
493 NOT NULL
495 The column is not allowed to contain null values.
496 NULL
498 The column is allowed to contain null values. This is the default.
499 This clause is only provided for compatibility with non-standard
501 SQL databases. Its use is discouraged in new applications.
502 CHECK ( expression ) [ NO INHERIT ]
504 The CHECK clause specifies an expression producing a Boolean result
505 which new or updated rows must satisfy for an insert or update
506 operation to succeed. Expressions evaluating to TRUE or UNKNOWN
507 succeed. Should any row of an insert or update operation produce a
508 FALSE result, an error exception is raised and the insert or update
509 does not alter the database. A check constraint specified as a
510 column constraint should reference that column's value only, while
511 an expression appearing in a table constraint can reference
512 multiple columns.
513 Currently, CHECK expressions cannot contain subqueries nor refer to
515 variables other than columns of the current row (see
516 Section 5.4.1). The system column tableoid may be referenced, but
517 not any other system column.
518 A constraint marked with NO INHERIT will not propagate to child
520 tables.
521 When a table has multiple CHECK constraints, they will be tested
523 for each row in alphabetical order by name, after checking NOT NULL
524 constraints. (PostgreSQL versions before 9.5 did not honor any
525 particular firing order for CHECK constraints.)
526 DEFAULT default_expr
528 The DEFAULT clause assigns a default data value for the column
529 whose column definition it appears within. The value is any
530 variable-free expression (in particular, cross-references to other
531 columns in the current table are not allowed). Subqueries are not
532 allowed either. The data type of the default expression must match
533 the data type of the column.
534 The default expression will be used in any insert operation that
536 does not specify a value for the column. If there is no default for
537 a column, then the default is null.
538 GENERATED ALWAYS AS ( generation_expr ) STORED
540 This clause creates the column as a generated column. The column
541 cannot be written to, and when read the result of the specified
542 expression will be returned.
543 The keyword STORED is required to signify that the column will be
545 computed on write and will be stored on disk.
546 The generation expression can refer to other columns in the table,
548 but not other generated columns. Any functions and operators used
549 must be immutable. References to other tables are not allowed.
550 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
552 This clause creates the column as an identity column. It will have
553 an implicit sequence attached to it and the column in new rows will
554 automatically have values from the sequence assigned to it. Such a
555 column is implicitly NOT NULL.
556 The clauses ALWAYS and BY DEFAULT determine how explicitly
558 user-specified values are handled in INSERT and UPDATE commands.
559 In an INSERT command, if ALWAYS is selected, a user-specified value
561 is only accepted if the INSERT statement specifies OVERRIDING
562 SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified
563 value takes precedence. See INSERT(7) for details. (In the COPY
564 command, user-specified values are always used regardless of this
565 setting.)
566 In an UPDATE command, if ALWAYS is selected, any update of the
568 column to any value other than DEFAULT will be rejected. If BY
569 DEFAULT is selected, the column can be updated normally. (There is
570 no OVERRIDING clause for the UPDATE command.)
571 The optional sequence_options clause can be used to override the
573 options of the sequence. See CREATE SEQUENCE (CREATE_SEQUENCE(7))
574 for details.
575 UNIQUE (column constraint)
577 UNIQUE ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
578 (table constraint)
579 The UNIQUE constraint specifies that a group of one or more columns
580 of a table can contain only unique values. The behavior of a unique
581 table constraint is the same as that of a unique column constraint,
582 with the additional capability to span multiple columns. The
583 constraint therefore enforces that any two rows must differ in at
584 least one of these columns.
585 For the purpose of a unique constraint, null values are not
587 considered equal.
588 Each unique constraint should name a set of columns that is
590 different from the set of columns named by any other unique or
591 primary key constraint defined for the table. (Otherwise, redundant
592 unique constraints will be discarded.)
593 When establishing a unique constraint for a multi-level partition
595 hierarchy, all the columns in the partition key of the target
596 partitioned table, as well as those of all its descendant
597 partitioned tables, must be included in the constraint definition.
598 Adding a unique constraint will automatically create a unique btree
600 index on the column or group of columns used in the constraint.
601 The optional INCLUDE clause adds to that index one or more columns
603 that are simply “payload”: uniqueness is not enforced on them, and
604 the index cannot be searched on the basis of those columns. However
605 they can be retrieved by an index-only scan. Note that although the
606 constraint is not enforced on included columns, it still depends on
607 them. Consequently, some operations on such columns (e.g., DROP
608 COLUMN) can cause cascaded constraint and index deletion.
609 PRIMARY KEY (column constraint)
611 PRIMARY KEY ( column_name [, ... ] ) [ INCLUDE ( column_name [, ...]) ]
612 (table constraint)
613 The PRIMARY KEY constraint specifies that a column or columns of a
614 table can contain only unique (non-duplicate), nonnull values. Only
615 one primary key can be specified for a table, whether as a column
616 constraint or a table constraint.
617 The primary key constraint should name a set of columns that is
619 different from the set of columns named by any unique constraint
620 defined for the same table. (Otherwise, the unique constraint is
621 redundant and will be discarded.)
622 PRIMARY KEY enforces the same data constraints as a combination of
624 UNIQUE and NOT NULL. However, identifying a set of columns as the
625 primary key also provides metadata about the design of the schema,
626 since a primary key implies that other tables can rely on this set
627 of columns as a unique identifier for rows.
628 When placed on a partitioned table, PRIMARY KEY constraints share
630 the restrictions previously described for UNIQUE constraints.
631 Adding a PRIMARY KEY constraint will automatically create a unique
633 btree index on the column or group of columns used in the
634 constraint.
635 The optional INCLUDE clause adds to that index one or more columns
637 that are simply “payload”: uniqueness is not enforced on them, and
638 the index cannot be searched on the basis of those columns. However
639 they can be retrieved by an index-only scan. Note that although the
640 constraint is not enforced on included columns, it still depends on
641 them. Consequently, some operations on such columns (e.g., DROP
642 COLUMN) can cause cascaded constraint and index deletion.
643 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ]
645 ) index_parameters [ WHERE ( predicate ) ]
646 The EXCLUDE clause defines an exclusion constraint, which
647 guarantees that if any two rows are compared on the specified
648 column(s) or expression(s) using the specified operator(s), not all
649 of these comparisons will return TRUE. If all of the specified
650 operators test for equality, this is equivalent to a UNIQUE
651 constraint, although an ordinary unique constraint will be faster.
652 However, exclusion constraints can specify constraints that are
653 more general than simple equality. For example, you can specify a
654 constraint that no two rows in the table contain overlapping
655 circles (see Section 8.8) by using the && operator.
656 Exclusion constraints are implemented using an index, so each
658 specified operator must be associated with an appropriate operator
659 class (see Section 11.10) for the index access method index_method.
660 The operators are required to be commutative. Each exclude_element
661 can optionally specify an operator class and/or ordering options;
662 these are described fully under CREATE INDEX (CREATE_INDEX(7)).
663 The access method must support amgettuple (see Chapter 62); at
665 present this means GIN cannot be used. Although it's allowed, there
666 is little point in using B-tree or hash indexes with an exclusion
667 constraint, because this does nothing that an ordinary unique
668 constraint doesn't do better. So in practice the access method will
669 always be GiST or SP-GiST.
670 The predicate allows you to specify an exclusion constraint on a
672 subset of the table; internally this creates a partial index. Note
673 that parentheses are required around the predicate.
674 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
676 referential_action ] [ ON UPDATE referential_action ] (column
677 constraint)
678 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
679 [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON
680 UPDATE referential_action ] (table constraint)
681 These clauses specify a foreign key constraint, which requires that
682 a group of one or more columns of the new table must only contain
683 values that match values in the referenced column(s) of some row of
684 the referenced table. If the refcolumn list is omitted, the primary
685 key of the reftable is used. The referenced columns must be the
686 columns of a non-deferrable unique or primary key constraint in the
687 referenced table. The user must have REFERENCES permission on the
688 referenced table (either the whole table, or the specific
689 referenced columns). The addition of a foreign key constraint
690 requires a SHARE ROW EXCLUSIVE lock on the referenced table. Note
691 that foreign key constraints cannot be defined between temporary
692 tables and permanent tables.
693 A value inserted into the referencing column(s) is matched against
695 the values of the referenced table and referenced columns using the
696 given match type. There are three match types: MATCH FULL, MATCH
697 PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will
698 not allow one column of a multicolumn foreign key to be null unless
699 all foreign key columns are null; if they are all null, the row is
700 not required to have a match in the referenced table. MATCH SIMPLE
701 allows any of the foreign key columns to be null; if any of them
702 are null, the row is not required to have a match in the referenced
703 table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL
704 constraints can be applied to the referencing column(s) to prevent
705 these cases from arising.)
706 In addition, when the data in the referenced columns is changed,
708 certain actions are performed on the data in this table's columns.
709 The ON DELETE clause specifies the action to perform when a
710 referenced row in the referenced table is being deleted. Likewise,
711 the ON UPDATE clause specifies the action to perform when a
712 referenced column in the referenced table is being updated to a new
713 value. If the row is updated, but the referenced column is not
714 actually changed, no action is done. Referential actions other than
715 the NO ACTION check cannot be deferred, even if the constraint is
716 declared deferrable. There are the following possible actions for
717 each clause:
718 NO ACTION
720 Produce an error indicating that the deletion or update would
721 create a foreign key constraint violation. If the constraint is
722 deferred, this error will be produced at constraint check time
723 if there still exist any referencing rows. This is the default
724 action.
725 RESTRICT
727 Produce an error indicating that the deletion or update would
728 create a foreign key constraint violation. This is the same as
729 NO ACTION except that the check is not deferrable.
730 CASCADE
732 Delete any rows referencing the deleted row, or update the
733 values of the referencing column(s) to the new values of the
734 referenced columns, respectively.
735 SET NULL
737 Set the referencing column(s) to null.
738 SET DEFAULT
740 Set the referencing column(s) to their default values. (There
741 must be a row in the referenced table matching the default
742 values, if they are not null, or the operation will fail.)
743 If the referenced column(s) are changed frequently, it might be
745 wise to add an index to the referencing column(s) so that
746 referential actions associated with the foreign key constraint can
747 be performed more efficiently.
748 DEFERRABLE
750 NOT DEFERRABLE
751 This controls whether the constraint can be deferred. A constraint
752 that is not deferrable will be checked immediately after every
753 command. Checking of constraints that are deferrable can be
754 postponed until the end of the transaction (using the SET
755 CONSTRAINTS command). NOT DEFERRABLE is the default. Currently,
756 only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key)
757 constraints accept this clause. NOT NULL and CHECK constraints are
758 not deferrable. Note that deferrable constraints cannot be used as
759 conflict arbitrators in an INSERT statement that includes an ON
760 CONFLICT DO UPDATE clause.
761 INITIALLY IMMEDIATE
763 INITIALLY DEFERRED
764 If a constraint is deferrable, this clause specifies the default
765 time to check the constraint. If the constraint is INITIALLY
766 IMMEDIATE, it is checked after each statement. This is the default.
767 If the constraint is INITIALLY DEFERRED, it is checked only at the
768 end of the transaction. The constraint check time can be altered
769 with the SET CONSTRAINTS command.
770 USING method
772 This optional clause specifies the table access method to use to
773 store the contents for the new table; the method needs be an access
774 method of type TABLE. See Chapter 61 for more information. If this
775 option is not specified, the default table access method is chosen
776 for the new table. See default_table_access_method for more
777 information.
778 WITH ( storage_parameter [= value] [, ... ] )
780 This clause specifies optional storage parameters for a table or
781 index; see Storage Parameters below for more information. For
782 backward-compatibility the WITH clause for a table can also include
783 OIDS=FALSE to specify that rows of the new table should not contain
784 OIDs (object identifiers), OIDS=TRUE is not supported anymore.
785 WITHOUT OIDS
787 This is backward-compatible syntax for declaring a table WITHOUT
788 OIDS, creating a table WITH OIDS is not supported anymore.
789 ON COMMIT
791 The behavior of temporary tables at the end of a transaction block
792 can be controlled using ON COMMIT. The three options are:
793 PRESERVE ROWS
795 No special action is taken at the ends of transactions. This is
796 the default behavior.
797 DELETE ROWS
799 All rows in the temporary table will be deleted at the end of
800 each transaction block. Essentially, an automatic TRUNCATE is
801 done at each commit. When used on a partitioned table, this is
802 not cascaded to its partitions.
803 DROP
805 The temporary table will be dropped at the end of the current
806 transaction block. When used on a partitioned table, this
807 action drops its partitions and when used on tables with
808 inheritance children, it drops the dependent children.
809 TABLESPACE tablespace_name
811 The tablespace_name is the name of the tablespace in which the new
812 table is to be created. If not specified, default_tablespace is
813 consulted, or temp_tablespaces if the table is temporary. For
814 partitioned tables, since no storage is required for the table
815 itself, the tablespace specified overrides default_tablespace as
816 the default tablespace to use for any newly created partitions when
817 no other tablespace is explicitly specified.
818 USING INDEX TABLESPACE tablespace_name
820 This clause allows selection of the tablespace in which the index
821 associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will
822 be created. If not specified, default_tablespace is consulted, or
823 temp_tablespaces if the table is temporary.
824 Storage Parameters
826 The WITH clause can specify storage parameters for tables, and for
827 indexes associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint.
828 Storage parameters for indexes are documented in CREATE INDEX
829 (CREATE_INDEX(7)). The storage parameters currently available for
830 tables are listed below. For many of these parameters, as shown, there
831 is an additional parameter with the same name prefixed with toast.,
832 which controls the behavior of the table's secondary TOAST table, if
833 any (see Section 70.2 for more information about TOAST). If a table
834 parameter value is set and the equivalent toast. parameter is not, the
835 TOAST table will use the table's parameter value. Specifying these
836 parameters for partitioned tables is not supported, but you may specify
837 them for individual leaf partitions.
838 fillfactor (integer)
840 The fillfactor for a table is a percentage between 10 and 100. 100
841 (complete packing) is the default. When a smaller fillfactor is
842 specified, INSERT operations pack table pages only to the indicated
843 percentage; the remaining space on each page is reserved for
844 updating rows on that page. This gives UPDATE a chance to place the
845 updated copy of a row on the same page as the original, which is
846 more efficient than placing it on a different page. For a table
847 whose entries are never updated, complete packing is the best
848 choice, but in heavily updated tables smaller fillfactors are
849 appropriate. This parameter cannot be set for TOAST tables.
850 toast_tuple_target (integer)
852 The toast_tuple_target specifies the minimum tuple length required
853 before we try to compress and/or move long column values into TOAST
854 tables, and is also the target length we try to reduce the length
855 below once toasting begins. This affects columns marked as External
856 (for move), Main (for compression), or Extended (for both) and
857 applies only to new tuples. There is no effect on existing rows. By
858 default this parameter is set to allow at least 4 tuples per block,
859 which with the default block size will be 2040 bytes. Valid values
860 are between 128 bytes and the (block size - header), by default
861 8160 bytes. Changing this value may not be useful for very short or
862 very long rows. Note that the default setting is often close to
863 optimal, and it is possible that setting this parameter could have
864 negative effects in some cases. This parameter cannot be set for
865 TOAST tables.
866 parallel_workers (integer)
868 This sets the number of workers that should be used to assist a
869 parallel scan of this table. If not set, the system will determine
870 a value based on the relation size. The actual number of workers
871 chosen by the planner or by utility statements that use parallel
872 scans may be less, for example due to the setting of
873 max_worker_processes.
874 autovacuum_enabled, toast.autovacuum_enabled (boolean)
876 Enables or disables the autovacuum daemon for a particular table.
877 If true, the autovacuum daemon will perform automatic VACUUM and/or
878 ANALYZE operations on this table following the rules discussed in
879 Section 25.1.6. If false, this table will not be autovacuumed,
880 except to prevent transaction ID wraparound. See Section 25.1.5 for
881 more about wraparound prevention. Note that the autovacuum daemon
882 does not run at all (except to prevent transaction ID wraparound)
883 if the autovacuum parameter is false; setting individual tables'
884 storage parameters does not override that. Therefore there is
885 seldom much point in explicitly setting this storage parameter to
886 true, only to false.
887 vacuum_index_cleanup, toast.vacuum_index_cleanup (enum)
889 Forces or disables index cleanup when VACUUM is run on this table.
890 The default value is AUTO. With OFF, index cleanup is disabled,
891 with ON it is enabled, and with AUTO a decision is made
892 dynamically, each time VACUUM runs. The dynamic behavior allows
893 VACUUM to avoid needlessly scanning indexes to remove very few dead
894 tuples. Forcibly disabling all index cleanup can speed up VACUUM
895 very significantly, but may also lead to severely bloated indexes
896 if table modifications are frequent. The INDEX_CLEANUP parameter of
897 VACUUM, if specified, overrides the value of this option.
898 vacuum_truncate, toast.vacuum_truncate (boolean)
900 Enables or disables vacuum to try to truncate off any empty pages
901 at the end of this table. The default value is true. If true,
902 VACUUM and autovacuum do the truncation and the disk space for the
903 truncated pages is returned to the operating system. Note that the
904 truncation requires ACCESS EXCLUSIVE lock on the table. The
905 TRUNCATE parameter of VACUUM, if specified, overrides the value of
906 this option.
907 autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold
909 (integer)
910 Per-table value for autovacuum_vacuum_threshold parameter.
911 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
913 (floating point)
914 Per-table value for autovacuum_vacuum_scale_factor parameter.
915 autovacuum_vacuum_insert_threshold,
917 toast.autovacuum_vacuum_insert_threshold (integer)
918 Per-table value for autovacuum_vacuum_insert_threshold parameter.
919 The special value of -1 may be used to disable insert vacuums on
920 the table.
921 autovacuum_vacuum_insert_scale_factor,
923 toast.autovacuum_vacuum_insert_scale_factor (floating point)
924 Per-table value for autovacuum_vacuum_insert_scale_factor
925 parameter.
926 autovacuum_analyze_threshold (integer)
928 Per-table value for autovacuum_analyze_threshold parameter.
929 autovacuum_analyze_scale_factor (floating point)
931 Per-table value for autovacuum_analyze_scale_factor parameter.
932 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay
934 (floating point)
935 Per-table value for autovacuum_vacuum_cost_delay parameter.
936 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit
938 (integer)
939 Per-table value for autovacuum_vacuum_cost_limit parameter.
940 autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
942 Per-table value for vacuum_freeze_min_age parameter. Note that
943 autovacuum will ignore per-table autovacuum_freeze_min_age
944 parameters that are larger than half the system-wide
945 autovacuum_freeze_max_age setting.
946 autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
948 Per-table value for autovacuum_freeze_max_age parameter. Note that
949 autovacuum will ignore per-table autovacuum_freeze_max_age
950 parameters that are larger than the system-wide setting (it can
951 only be set smaller).
952 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age
954 (integer)
955 Per-table value for vacuum_freeze_table_age parameter.
956 autovacuum_multixact_freeze_min_age,
958 toast.autovacuum_multixact_freeze_min_age (integer)
959 Per-table value for vacuum_multixact_freeze_min_age parameter. Note
960 that autovacuum will ignore per-table
961 autovacuum_multixact_freeze_min_age parameters that are larger than
962 half the system-wide autovacuum_multixact_freeze_max_age setting.
963 autovacuum_multixact_freeze_max_age,
965 toast.autovacuum_multixact_freeze_max_age (integer)
966 Per-table value for autovacuum_multixact_freeze_max_age parameter.
967 Note that autovacuum will ignore per-table
968 autovacuum_multixact_freeze_max_age parameters that are larger than
969 the system-wide setting (it can only be set smaller).
970 autovacuum_multixact_freeze_table_age,
972 toast.autovacuum_multixact_freeze_table_age (integer)
973 Per-table value for vacuum_multixact_freeze_table_age parameter.
974 log_autovacuum_min_duration, toast.log_autovacuum_min_duration
976 (integer)
977 Per-table value for log_autovacuum_min_duration parameter.
978 user_catalog_table (boolean)
980 Declare the table as an additional catalog table for purposes of
981 logical replication. See Section 49.6.2 for details. This parameter
982 cannot be set for TOAST tables.
983
985 PostgreSQL automatically creates an index for each unique constraint
986 and primary key constraint to enforce uniqueness. Thus, it is not
987 necessary to create an index explicitly for primary key columns. (See
988 CREATE INDEX (CREATE_INDEX(7)) for more information.)
989 Unique constraints and primary keys are not inherited in the current
991 implementation. This makes the combination of inheritance and unique
992 constraints rather dysfunctional.
993 A table cannot have more than 1600 columns. (In practice, the effective
995 limit is usually lower because of tuple-length constraints.)
996
998 Create table films and table distributors:
999 CREATE TABLE films (
1001 code char(5) CONSTRAINT firstkey PRIMARY KEY,
1002 title varchar(40) NOT NULL,
1003 did integer NOT NULL,
1004 date_prod date,
1005 kind varchar(10),
1006 len interval hour to minute
1007 );
1008 CREATE TABLE distributors (
1010 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1011 name varchar(40) NOT NULL CHECK (name <> '')
1012 );
1013 Create a table with a 2-dimensional array:
1015 CREATE TABLE array_int (
1017 vector int[][]
1018 );
1019 Define a unique table constraint for the table films. Unique table
1021 constraints can be defined on one or more columns of the table:
1022 CREATE TABLE films (
1024 code char(5),
1025 title varchar(40),
1026 did integer,
1027 date_prod date,
1028 kind varchar(10),
1029 len interval hour to minute,
1030 CONSTRAINT production UNIQUE(date_prod)
1031 );
1032 Define a check column constraint:
1034 CREATE TABLE distributors (
1036 did integer CHECK (did > 100),
1037 name varchar(40)
1038 );
1039 Define a check table constraint:
1041 CREATE TABLE distributors (
1043 did integer,
1044 name varchar(40),
1045 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
1046 );
1047 Define a primary key table constraint for the table films:
1049 CREATE TABLE films (
1051 code char(5),
1052 title varchar(40),
1053 did integer,
1054 date_prod date,
1055 kind varchar(10),
1056 len interval hour to minute,
1057 CONSTRAINT code_title PRIMARY KEY(code,title)
1058 );
1059 Define a primary key constraint for table distributors. The following
1061 two examples are equivalent, the first using the table constraint
1062 syntax, the second the column constraint syntax:
1063 CREATE TABLE distributors (
1065 did integer,
1066 name varchar(40),
1067 PRIMARY KEY(did)
1068 );
1069 CREATE TABLE distributors (
1071 did integer PRIMARY KEY,
1072 name varchar(40)
1073 );
1074 Assign a literal constant default value for the column name, arrange
1076 for the default value of column did to be generated by selecting the
1077 next value of a sequence object, and make the default value of modtime
1078 be the time at which the row is inserted:
1079 CREATE TABLE distributors (
1081 name varchar(40) DEFAULT 'Luso Films',
1082 did integer DEFAULT nextval('distributors_serial'),
1083 modtime timestamp DEFAULT current_timestamp
1084 );
1085 Define two NOT NULL column constraints on the table distributors, one
1087 of which is explicitly given a name:
1088 CREATE TABLE distributors (
1090 did integer CONSTRAINT no_null NOT NULL,
1091 name varchar(40) NOT NULL
1092 );
1093 Define a unique constraint for the name column:
1095 CREATE TABLE distributors (
1097 did integer,
1098 name varchar(40) UNIQUE
1099 );
1100 The same, specified as a table constraint:
1102 CREATE TABLE distributors (
1104 did integer,
1105 name varchar(40),
1106 UNIQUE(name)
1107 );
1108 Create the same table, specifying 70% fill factor for both the table
1110 and its unique index:
1111 CREATE TABLE distributors (
1113 did integer,
1114 name varchar(40),
1115 UNIQUE(name) WITH (fillfactor=70)
1116 )
1117 WITH (fillfactor=70);
1118 Create table circles with an exclusion constraint that prevents any two
1120 circles from overlapping:
1121 CREATE TABLE circles (
1123 c circle,
1124 EXCLUDE USING gist (c WITH &&)
1125 );
1126 Create table cinemas in tablespace diskvol1:
1128 CREATE TABLE cinemas (
1130 id serial,
1131 name text,
1132 location text
1133 ) TABLESPACE diskvol1;
1134 Create a composite type and a typed table:
1136 CREATE TYPE employee_type AS (name text, salary numeric);
1138 CREATE TABLE employees OF employee_type (
1140 PRIMARY KEY (name),
1141 salary WITH OPTIONS DEFAULT 1000
1142 );
1143 Create a range partitioned table:
1145 CREATE TABLE measurement (
1147 logdate date not null,
1148 peaktemp int,
1149 unitsales int
1150 ) PARTITION BY RANGE (logdate);
1151 Create a range partitioned table with multiple columns in the partition
1153 key:
1154 CREATE TABLE measurement_year_month (
1156 logdate date not null,
1157 peaktemp int,
1158 unitsales int
1159 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1160 Create a list partitioned table:
1162 CREATE TABLE cities (
1164 city_id bigserial not null,
1165 name text not null,
1166 population bigint
1167 ) PARTITION BY LIST (left(lower(name), 1));
1168 Create a hash partitioned table:
1170 CREATE TABLE orders (
1172 order_id bigint not null,
1173 cust_id bigint not null,
1174 status text
1175 ) PARTITION BY HASH (order_id);
1176 Create partition of a range partitioned table:
1178 CREATE TABLE measurement_y2016m07
1180 PARTITION OF measurement (
1181 unitsales DEFAULT 0
1182 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1183 Create a few partitions of a range partitioned table with multiple
1185 columns in the partition key:
1186 CREATE TABLE measurement_ym_older
1188 PARTITION OF measurement_year_month
1189 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1190 CREATE TABLE measurement_ym_y2016m11
1192 PARTITION OF measurement_year_month
1193 FOR VALUES FROM (2016, 11) TO (2016, 12);
1194 CREATE TABLE measurement_ym_y2016m12
1196 PARTITION OF measurement_year_month
1197 FOR VALUES FROM (2016, 12) TO (2017, 01);
1198 CREATE TABLE measurement_ym_y2017m01
1200 PARTITION OF measurement_year_month
1201 FOR VALUES FROM (2017, 01) TO (2017, 02);
1202 Create partition of a list partitioned table:
1204 CREATE TABLE cities_ab
1206 PARTITION OF cities (
1207 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1208 ) FOR VALUES IN ('a', 'b');
1209 Create partition of a list partitioned table that is itself further
1211 partitioned and then add a partition to it:
1212 CREATE TABLE cities_ab
1214 PARTITION OF cities (
1215 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1216 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1217 CREATE TABLE cities_ab_10000_to_100000
1219 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1220 Create partitions of a hash partitioned table:
1222 CREATE TABLE orders_p1 PARTITION OF orders
1224 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1225 CREATE TABLE orders_p2 PARTITION OF orders
1226 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1227 CREATE TABLE orders_p3 PARTITION OF orders
1228 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1229 CREATE TABLE orders_p4 PARTITION OF orders
1230 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1231 Create a default partition:
1233 CREATE TABLE cities_partdef
1235 PARTITION OF cities DEFAULT;
1236
1238 The CREATE TABLE command conforms to the SQL standard, with exceptions
1239 listed below.
1240 Temporary Tables
1242 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
1243 standard, the effect is not the same. In the standard, temporary tables
1244 are defined just once and automatically exist (starting with empty
1245 contents) in every session that needs them. PostgreSQL instead
1246 requires each session to issue its own CREATE TEMPORARY TABLE command
1247 for each temporary table to be used. This allows different sessions to
1248 use the same temporary table name for different purposes, whereas the
1249 standard's approach constrains all instances of a given temporary table
1250 name to have the same table structure.
1251 The standard's definition of the behavior of temporary tables is widely
1253 ignored. PostgreSQL's behavior on this point is similar to that of
1254 several other SQL databases.
1255 The SQL standard also distinguishes between global and local temporary
1257 tables, where a local temporary table has a separate set of contents
1258 for each SQL module within each session, though its definition is still
1259 shared across sessions. Since PostgreSQL does not support SQL modules,
1260 this distinction is not relevant in PostgreSQL.
1261 For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
1263 keywords in a temporary table declaration, but they currently have no
1264 effect. Use of these keywords is discouraged, since future versions of
1265 PostgreSQL might adopt a more standard-compliant interpretation of
1266 their meaning.
1267 The ON COMMIT clause for temporary tables also resembles the SQL
1269 standard, but has some differences. If the ON COMMIT clause is omitted,
1270 SQL specifies that the default behavior is ON COMMIT DELETE ROWS.
1271 However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
1272 The ON COMMIT DROP option does not exist in SQL.
1273 Non-Deferred Uniqueness Constraints
1275 When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
1276 checks for uniqueness immediately whenever a row is inserted or
1277 modified. The SQL standard says that uniqueness should be enforced only
1278 at the end of the statement; this makes a difference when, for example,
1279 a single command updates multiple key values. To obtain
1280 standard-compliant behavior, declare the constraint as DEFERRABLE but
1281 not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
1282 significantly slower than immediate uniqueness checking.
1283 Column Check Constraints
1285 The SQL standard says that CHECK column constraints can only refer to
1286 the column they apply to; only CHECK table constraints can refer to
1287 multiple columns. PostgreSQL does not enforce this restriction; it
1288 treats column and table check constraints alike.
1289 EXCLUDE Constraint
1291 The EXCLUDE constraint type is a PostgreSQL extension.
1292 NULL “Constraint”
1294 The NULL “constraint” (actually a non-constraint) is a PostgreSQL
1295 extension to the SQL standard that is included for compatibility with
1296 some other database systems (and for symmetry with the NOT NULL
1297 constraint). Since it is the default for any column, its presence is
1298 simply noise.
1299 Constraint Naming
1301 The SQL standard says that table and domain constraints must have names
1302 that are unique across the schema containing the table or domain.
1303 PostgreSQL is laxer: it only requires constraint names to be unique
1304 across the constraints attached to a particular table or domain.
1305 However, this extra freedom does not exist for index-based constraints
1306 (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the associated
1307 index is named the same as the constraint, and index names must be
1308 unique across all relations within the same schema.
1309 Currently, PostgreSQL does not record names for NOT NULL constraints at
1311 all, so they are not subject to the uniqueness restriction. This might
1312 change in a future release.
1313 Inheritance
1315 Multiple inheritance via the INHERITS clause is a PostgreSQL language
1316 extension. SQL:1999 and later define single inheritance using a
1317 different syntax and different semantics. SQL:1999-style inheritance is
1318 not yet supported by PostgreSQL.
1319 Zero-Column Tables
1321 PostgreSQL allows a table of no columns to be created (for example,
1322 CREATE TABLE foo();). This is an extension from the SQL standard, which
1323 does not allow zero-column tables. Zero-column tables are not in
1324 themselves very useful, but disallowing them creates odd special cases
1325 for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
1326 restriction.
1327 Multiple Identity Columns
1329 PostgreSQL allows a table to have more than one identity column. The
1330 standard specifies that a table can have at most one identity column.
1331 This is relaxed mainly to give more flexibility for doing schema
1332 changes or migrations. Note that the INSERT command supports only one
1333 override clause that applies to the entire statement, so having
1334 multiple identity columns with different behaviors is not well
1335 supported.
1336 Generated Columns
1338 The option STORED is not standard but is also used by other SQL
1339 implementations. The SQL standard does not specify the storage of
1340 generated columns.
1341 LIKE Clause
1343 While a LIKE clause exists in the SQL standard, many of the options
1344 that PostgreSQL accepts for it are not in the standard, and some of the
1345 standard's options are not implemented by PostgreSQL.
1346 WITH Clause
1348 The WITH clause is a PostgreSQL extension; storage parameters are not
1349 in the standard.
1350 Tablespaces
1352 The PostgreSQL concept of tablespaces is not part of the standard.
1353 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are
1354 extensions.
1355 Typed Tables
1357 Typed tables implement a subset of the SQL standard. According to the
1358 standard, a typed table has columns corresponding to the underlying
1359 composite type as well as one other column that is the
1360 “self-referencing column”. PostgreSQL does not support
1361 self-referencing columns explicitly.
1362 PARTITION BY Clause
1364 The PARTITION BY clause is a PostgreSQL extension.
1365 PARTITION OF Clause
1367 The PARTITION OF clause is a PostgreSQL extension.
1368
1370 ALTER TABLE (ALTER_TABLE(7)), DROP TABLE (DROP_TABLE(7)), CREATE TABLE
1371 AS (CREATE_TABLE_AS(7)), CREATE TABLESPACE (CREATE_TABLESPACE(7)),
1372 CREATE TYPE (CREATE_TYPE(7))
1373PostgreSQL 14.3 2022 CREATE TABLE(7)