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