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