Debian Codegraph @

doc/src/sgml/ref/create_table.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_table.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createtable">
7 <indexterm zone="sql-createtable">
8 <primary>CREATE TABLE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE TABLE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE TABLE</refname>
19 <refpurpose>define a new table</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
25 { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION <replaceable>compression_method</replaceable> ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
26 | <replaceable>table_constraint</replaceable>
27 | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
28 [, ... ]
29 ] )
30 [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
31 [ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
32 [ USING <replaceable class="parameter">method</replaceable> ]
33 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
34 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
35 [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
36
37 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable>
38 OF <replaceable class="parameter">type_name</replaceable> [ (
39 { <replaceable class="parameter">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
40 | <replaceable>table_constraint</replaceable> }
41 [, ... ]
42 ) ]
43 [ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
44 [ USING <replaceable class="parameter">method</replaceable> ]
45 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
46 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
47 [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
48
49 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable>
50 PARTITION OF <replaceable class="parameter">parent_table</replaceable> [ (
51 { <replaceable class="parameter">column_name</replaceable> [ WITH OPTIONS ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
52 | <replaceable>table_constraint</replaceable> }
53 [, ... ]
54 ) ] { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
55 [ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
56 [ USING <replaceable class="parameter">method</replaceable> ]
57 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
58 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
59 [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
60
61 <phrase>where <replaceable class="parameter">column_constraint</replaceable> is:</phrase>
62
63 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
64 { NOT NULL [ NO INHERIT ] |
65 NULL |
66 CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
67 DEFAULT <replaceable>default_expr</replaceable> |
68 GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] |
69 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
70 UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
71 PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
72 REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
73 [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
74 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
75
76 <phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
77
78 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
79 { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
80 NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
81 UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
82 PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
83 EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
84 FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">refcolumn</replaceable> ] ) ]
85 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable
86 class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
87 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
88
89 <phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
90
91 { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
92
93 <phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
94
95 IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
96 FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] )
97 TO ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) |
98 WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REMAINDER <replaceable class="parameter">numeric_literal</replaceable> )
99
100 <phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
101
102 [ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ]
103 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
104 [ USING INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
105
106 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
107
108 { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
109
110 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
111
112 { NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] }
113 </synopsis>
114
115 </refsynopsisdiv>
116
117 <refsect1 id="sql-createtable-description">
118 <title>Description</title>
119
120 <para>
121 <command>CREATE TABLE</command> will create a new, initially empty table
122 in the current database. The table will be owned by the user issuing the
123 command.
124 </para>
125
126 <para>
127 If a schema name is given (for example, <literal>CREATE TABLE
128 myschema.mytable ...</literal>) then the table is created in the specified
129 schema. Otherwise it is created in the current schema. Temporary
130 tables exist in a special schema, so a schema name cannot be given
131 when creating a temporary table. The name of the table must be
132 distinct from the name of any other relation (table, sequence, index, view,
133 materialized view, or foreign table) in the same schema.
134 </para>
135
136 <para>
137 <command>CREATE TABLE</command> also automatically creates a data
138 type that represents the composite type corresponding
139 to one row of the table. Therefore, tables cannot have the same
140 name as any existing data type in the same schema.
141 </para>
142
143 <para>
144 The optional constraint clauses specify constraints (tests) that
145 new or updated rows must satisfy for an insert or update operation
146 to succeed. A constraint is an SQL object that helps define the
147 set of valid values in the table in various ways.
148 </para>
149
150 <para>
151 There are two ways to define constraints: table constraints and
152 column constraints. A column constraint is defined as part of a
153 column definition. A table constraint definition is not tied to a
154 particular column, and it can encompass more than one column.
155 Every column constraint can also be written as a table constraint;
156 a column constraint is only a notational convenience for use when the
157 constraint only affects one column.
158 </para>
159
160 <para>
161 To be able to create a table, you must have <literal>USAGE</literal>
162 privilege on all column types or the type in the <literal>OF</literal>
163 clause, respectively.
164 </para>
165 </refsect1>
166
167 <refsect1>
168 <title>Parameters</title>
169
170 <variablelist>
171
172 <varlistentry id="sql-createtable-temporary">
173 <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
174 <listitem>
175 <para>
176 If specified, the table is created as a temporary table.
177 Temporary tables are automatically dropped at the end of a
178 session, or optionally at the end of the current transaction
179 (see <literal>ON COMMIT</literal> below). The default
180 search_path includes the temporary schema first and so identically
181 named existing permanent tables are not chosen for new plans
182 while the temporary table exists, unless they are referenced
183 with schema-qualified names. Any indexes created on a temporary
184 table are automatically temporary as well.
185 </para>
186
187 <para>
188 The <link linkend="autovacuum">autovacuum daemon</link> cannot
189 access and therefore cannot vacuum or analyze temporary tables.
190 For this reason, appropriate vacuum and analyze operations should be
191 performed via session SQL commands. For example, if a temporary
192 table is going to be used in complex queries, it is wise to run
193 <command>ANALYZE</command> on the temporary table after it is populated.
194 </para>
195
196 <para>
197 Optionally, <literal>GLOBAL</literal> or <literal>LOCAL</literal>
198 can be written before <literal>TEMPORARY</literal> or <literal>TEMP</literal>.
199 This presently makes no difference in <productname>PostgreSQL</productname>
200 and is deprecated; see
201 <xref linkend="sql-createtable-compatibility"/> below.
202 </para>
203 </listitem>
204 </varlistentry>
205
206 <varlistentry id="sql-createtable-unlogged">
207 <term><literal>UNLOGGED</literal></term>
208 <listitem>
209 <para>
210 If specified, the table is created as an unlogged table. Data written
211 to unlogged tables is not written to the write-ahead log (see <xref
212 linkend="wal"/>), which makes them considerably faster than ordinary
213 tables. However, they are not crash-safe: an unlogged table is
214 automatically truncated after a crash or unclean shutdown. The contents
215 of an unlogged table are also not replicated to standby servers.
216 Any indexes created on an unlogged table are automatically unlogged as
217 well.
218 </para>
219
220 <para>
221 If this is specified, any sequences created together with the unlogged
222 table (for identity or serial columns) are also created as unlogged.
223 </para>
224
225 <para>
226 This form is not supported for partitioned tables.
227 </para>
228 </listitem>
229 </varlistentry>
230
231 <varlistentry id="sql-createtable-parms-if-not-exists">
232 <term><literal>IF NOT EXISTS</literal></term>
233 <listitem>
234 <para>
235 Do not throw an error if a relation with the same name already exists.
236 A notice is issued in this case. Note that there is no guarantee that
237 the existing relation is anything like the one that would have been
238 created.
239 </para>
240 </listitem>
241 </varlistentry>
242
243 <varlistentry id="sql-createtable-parms-table-name">
244 <term><replaceable class="parameter">table_name</replaceable></term>
245 <listitem>
246 <para>
247 The name (optionally schema-qualified) of the table to be created.
248 </para>
249 </listitem>
250 </varlistentry>
251
252 <varlistentry id="sql-createtable-parms-type-name">
253 <term><literal>OF <replaceable class="parameter">type_name</replaceable></literal></term>
254 <listitem>
255 <para>
256 Creates a <firstterm>typed table</firstterm>, which takes its
257 structure from the specified stand-alone composite type (that is,
258 one created using <xref linkend="sql-createtype"/>) though it still
259 produces a new composite type as well. The table will have a
260 dependency on the referenced type, meaning that cascaded alter and
261 drop actions on that type will propagate to the table.
262 </para>
263
264 <para>
265 A typed table always has the same column names and data types as the
266 type it is derived from, so you cannot specify additional columns.
267 But the <literal>CREATE TABLE</literal> command can add defaults
268 and constraints to the table, as well as specify storage parameters.
269 </para>
270 </listitem>
271 </varlistentry>
272
273 <varlistentry id="sql-createtable-parms-column-name">
274 <term><replaceable class="parameter">column_name</replaceable></term>
275 <listitem>
276 <para>
277 The name of a column to be created in the new table.
278 </para>
279 </listitem>
280 </varlistentry>
281
282 <varlistentry id="sql-createtable-parms-data-type">
283 <term><replaceable class="parameter">data_type</replaceable></term>
284 <listitem>
285 <para>
286 The data type of the column. This can include array
287 specifiers. For more information on the data types supported by
288 <productname>PostgreSQL</productname>, refer to <xref
289 linkend="datatype"/>.
290 </para>
291 </listitem>
292 </varlistentry>
293
294 <varlistentry id="sql-createtable-parms-collate">
295 <term><literal>COLLATE <replaceable>collation</replaceable></literal></term>
296 <listitem>
297 <para>
298 The <literal>COLLATE</literal> clause assigns a collation to
299 the column (which must be of a collatable data type).
300 If not specified, the column data type's default collation is used.
301 </para>
302 </listitem>
303 </varlistentry>
304
305 <varlistentry id="sql-createtable-parms-storage">
306 <term>
307 <literal>STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }</literal>
308 <indexterm>
309 <primary>TOAST</primary>
310 <secondary>per-column storage settings</secondary>
311 </indexterm>
312 </term>
313 <listitem>
314 <para>
315 This form sets the storage mode for the column. This controls whether this
316 column is held inline or in a secondary <acronym>TOAST</acronym> table,
317 and whether the data should be compressed or not. <literal>PLAIN</literal>
318 must be used for fixed-length values such as <type>integer</type> and is
319 inline, uncompressed. <literal>MAIN</literal> is for inline, compressible
320 data. <literal>EXTERNAL</literal> is for external, uncompressed data, and
321 <literal>EXTENDED</literal> is for external, compressed data.
322 Writing <literal>DEFAULT</literal> sets the storage mode to the default
323 mode for the column's data type. <literal>EXTENDED</literal> is the
324 default for most data types that support non-<literal>PLAIN</literal>
325 storage.
326 Use of <literal>EXTERNAL</literal> will make substring operations on
327 very large <type>text</type> and <type>bytea</type> values run faster,
328 at the penalty of increased storage space.
329 See <xref linkend="storage-toast"/> for more information.
330 </para>
331 </listitem>
332 </varlistentry>
333
334 <varlistentry id="sql-createtable-parms-compression">
335 <term><literal>COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal></term>
336 <listitem>
337 <para>
338 The <literal>COMPRESSION</literal> clause sets the compression method
339 for the column. Compression is supported only for variable-width data
340 types, and is used only when the column's storage mode
341 is <literal>main</literal> or <literal>extended</literal>.
342 (See <xref linkend="sql-altertable"/> for information on
343 column storage modes.) Setting this property for a partitioned table
344 has no direct effect, because such tables have no storage of their own,
345 but the configured value will be inherited by newly-created partitions.
346 The supported compression methods are <literal>pglz</literal> and
347 <literal>lz4</literal>. (<literal>lz4</literal> is available only if
348 <option>--with-lz4</option> was used when building
349 <productname>PostgreSQL</productname>.) In addition,
350 <replaceable class="parameter">compression_method</replaceable>
351 can be <literal>default</literal> to explicitly specify the default
352 behavior, which is to consult the
353 <xref linkend="guc-default-toast-compression"/> setting at the time of
354 data insertion to determine the method to use.
355 </para>
356 </listitem>
357 </varlistentry>
358
359 <varlistentry id="sql-createtable-parms-inherits">
360 <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
361 <listitem>
362 <para>
363 The optional <literal>INHERITS</literal> clause specifies a list of
364 tables from which the new table automatically inherits all
365 columns. Parent tables can be plain tables or foreign tables.
366 </para>
367
368 <para>
369 Use of <literal>INHERITS</literal> creates a persistent relationship
370 between the new child table and its parent table(s). Schema
371 modifications to the parent(s) normally propagate to children
372 as well, and by default the data of the child table is included in
373 scans of the parent(s).
374 </para>
375
376 <para>
377 If the same column name exists in more than one parent
378 table, an error is reported unless the data types of the columns
379 match in each of the parent tables. If there is no conflict,
380 then the duplicate columns are merged to form a single column in
381 the new table. If the column name list of the new table
382 contains a column name that is also inherited, the data type must
383 likewise match the inherited column(s), and the column
384 definitions are merged into one. If the
385 new table explicitly specifies a default value for the column,
386 this default overrides any defaults from inherited declarations
387 of the column. Otherwise, any parents that specify default
388 values for the column must all specify the same default, or an
389 error will be reported.
390 </para>
391
392 <para>
393 <literal>CHECK</literal> constraints are merged in essentially the same way as
394 columns: if multiple parent tables and/or the new table definition
395 contain identically-named <literal>CHECK</literal> constraints, these
396 constraints must all have the same check expression, or an error will be
397 reported. Constraints having the same name and expression will
398 be merged into one copy. A constraint marked <literal>NO INHERIT</literal> in a
399 parent will not be considered. Notice that an unnamed <literal>CHECK</literal>
400 constraint in the new table will never be merged, since a unique name
401 will always be chosen for it.
402 </para>
403
404 <para>
405 Column <literal>STORAGE</literal> settings are also copied from parent tables.
406 </para>
407
408 <para>
409 If a column in the parent table is an identity column, that property is
410 not inherited. A column in the child table can be declared identity
411 column if desired.
412 </para>
413 </listitem>
414 </varlistentry>
415
416 <varlistentry id="sql-createtable-parms-partition-by">
417 <term><literal>PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
418 <listitem>
419 <para>
420 The optional <literal>PARTITION BY</literal> clause specifies a strategy
421 of partitioning the table. The table thus created is called a
422 <firstterm>partitioned</firstterm> table. The parenthesized list of
423 columns or expressions forms the <firstterm>partition key</firstterm>
424 for the table. When using range or hash partitioning, the partition key
425 can include multiple columns or expressions (up to 32, but this limit can
426 be altered when building <productname>PostgreSQL</productname>), but for
427 list partitioning, the partition key must consist of a single column or
428 expression.
429 </para>
430
431 <para>
432 Range and list partitioning require a btree operator class, while hash
433 partitioning requires a hash operator class. If no operator class is
434 specified explicitly, the default operator class of the appropriate
435 type will be used; if no default operator class exists, an error will
436 be raised. When hash partitioning is used, the operator class used
437 must implement support function 2 (see <xref linkend="xindex-support"/>
438 for details).
439 </para>
440
441 <para>
442 A partitioned table is divided into sub-tables (called partitions),
443 which are created using separate <literal>CREATE TABLE</literal> commands.
444 The partitioned table is itself empty. A data row inserted into the
445 table is routed to a partition based on the value of columns or
446 expressions in the partition key. If no existing partition matches
447 the values in the new row, an error will be reported.
448 </para>
449
450 <para>
451 See <xref linkend="ddl-partitioning"/> for more discussion on table
452 partitioning.
453 </para>
454
455 </listitem>
456 </varlistentry>
457
458 <varlistentry id="sql-createtable-partition">
459 <term><literal>PARTITION OF <replaceable class="parameter">parent_table</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }</literal></term>
460 <listitem>
461 <para>
462 Creates the table as a <firstterm>partition</firstterm> of the specified
463 parent table. The table can be created either as a partition for specific
464 values using <literal>FOR VALUES</literal> or as a default partition
465 using <literal>DEFAULT</literal>. Any indexes, constraints and
466 user-defined row-level triggers that exist in the parent table are cloned
467 on the new partition.
468 </para>
469
470 <para>
471 The <replaceable class="parameter">partition_bound_spec</replaceable>
472 must correspond to the partitioning method and partition key of the
473 parent table, and must not overlap with any existing partition of that
474 parent. The form with <literal>IN</literal> is used for list partitioning,
475 the form with <literal>FROM</literal> and <literal>TO</literal> is used
476 for range partitioning, and the form with <literal>WITH</literal> is used
477 for hash partitioning.
478 </para>
479
480 <para>
481 <replaceable class="parameter">partition_bound_expr</replaceable> is
482 any variable-free expression (subqueries, window functions, aggregate
483 functions, and set-returning functions are not allowed). Its data type
484 must match the data type of the corresponding partition key column.
485 The expression is evaluated once at table creation time, so it can
486 even contain volatile expressions such as
487 <literal><function>CURRENT_TIMESTAMP</function></literal>.
488 </para>
489
490 <para>
491 When creating a list partition, <literal>NULL</literal> can be
492 specified to signify that the partition allows the partition key
493 column to be null. However, there cannot be more than one such
494 list partition for a given parent table. <literal>NULL</literal>
495 cannot be specified for range partitions.
496 </para>
497
498 <para>
499 When creating a range partition, the lower bound specified with
500 <literal>FROM</literal> is an inclusive bound, whereas the upper
501 bound specified with <literal>TO</literal> is an exclusive bound.
502 That is, the values specified in the <literal>FROM</literal> list
503 are valid values of the corresponding partition key columns for this
504 partition, whereas those in the <literal>TO</literal> list are
505 not. Note that this statement must be understood according to the
506 rules of row-wise comparison (<xref linkend="row-wise-comparison"/>).
507 For example, given <literal>PARTITION BY RANGE (x,y)</literal>, a partition
508 bound <literal>FROM (1, 2) TO (3, 4)</literal>
509 allows <literal>x=1</literal> with any <literal>y&gt;=2</literal>,
510 <literal>x=2</literal> with any non-null <literal>y</literal>,
511 and <literal>x=3</literal> with any <literal>y&lt;4</literal>.
512 </para>
513
514 <para>
515 The special values <literal>MINVALUE</literal> and <literal>MAXVALUE</literal>
516 may be used when creating a range partition to indicate that there
517 is no lower or upper bound on the column's value. For example, a
518 partition defined using <literal>FROM (MINVALUE) TO (10)</literal> allows
519 any values less than 10, and a partition defined using
520 <literal>FROM (10) TO (MAXVALUE)</literal> allows any values greater than
521 or equal to 10.
522 </para>
523
524 <para>
525 When creating a range partition involving more than one column, it
526 can also make sense to use <literal>MAXVALUE</literal> as part of the lower
527 bound, and <literal>MINVALUE</literal> as part of the upper bound. For
528 example, a partition defined using
529 <literal>FROM (0, MAXVALUE) TO (10, MAXVALUE)</literal> allows any rows
530 where the first partition key column is greater than 0 and less than
531 or equal to 10. Similarly, a partition defined using
532 <literal>FROM ('a', MINVALUE) TO ('b', MINVALUE)</literal> allows any rows
533 where the first partition key column starts with "a".
534 </para>
535
536 <para>
537 Note that if <literal>MINVALUE</literal> or <literal>MAXVALUE</literal> is used for
538 one column of a partitioning bound, the same value must be used for all
539 subsequent columns. For example, <literal>(10, MINVALUE, 0)</literal> is not
540 a valid bound; you should write <literal>(10, MINVALUE, MINVALUE)</literal>.
541 </para>
542
543 <para>
544 Also note that some element types, such as <literal>timestamp</literal>,
545 have a notion of "infinity", which is just another value that can
546 be stored. This is different from <literal>MINVALUE</literal> and
547 <literal>MAXVALUE</literal>, which are not real values that can be stored,
548 but rather they are ways of saying that the value is unbounded.
549 <literal>MAXVALUE</literal> can be thought of as being greater than any
550 other value, including "infinity" and <literal>MINVALUE</literal> as being
551 less than any other value, including "minus infinity". Thus the range
552 <literal>FROM ('infinity') TO (MAXVALUE)</literal> is not an empty range; it
553 allows precisely one value to be stored &mdash; "infinity".
554 </para>
555
556 <para>
557 If <literal>DEFAULT</literal> is specified, the table will be
558 created as the default partition of the parent table. This option
559 is not available for hash-partitioned tables. A partition key value
560 not fitting into any other partition of the given parent will be
561 routed to the default partition.
562 </para>
563
564 <para>
565 When a table has an existing <literal>DEFAULT</literal> partition and
566 a new partition is added to it, the default partition must
567 be scanned to verify that it does not contain any rows which properly
568 belong in the new partition. If the default partition contains a
569 large number of rows, this may be slow. The scan will be skipped if
570 the default partition is a foreign table or if it has a constraint which
571 proves that it cannot contain rows which should be placed in the new
572 partition.
573 </para>
574
575 <para>
576 When creating a hash partition, a modulus and remainder must be specified.
577 The modulus must be a positive integer, and the remainder must be a
578 non-negative integer less than the modulus. Typically, when initially
579 setting up a hash-partitioned table, you should choose a modulus equal to
580 the number of partitions and assign every table the same modulus and a
581 different remainder (see examples, below). However, it is not required
582 that every partition have the same modulus, only that every modulus which
583 occurs among the partitions of a hash-partitioned table is a factor of the
584 next larger modulus. This allows the number of partitions to be increased
585 incrementally without needing to move all the data at once. For example,
586 suppose you have a hash-partitioned table with 8 partitions, each of which
587 has modulus 8, but find it necessary to increase the number of partitions
588 to 16. You can detach one of the modulus-8 partitions, create two new
589 modulus-16 partitions covering the same portion of the key space (one with
590 a remainder equal to the remainder of the detached partition, and the
591 other with a remainder equal to that value plus 8), and repopulate them
592 with data. You can then repeat this -- perhaps at a later time -- for
593 each modulus-8 partition until none remain. While this may still involve
594 a large amount of data movement at each step, it is still better than
595 having to create a whole new table and move all the data at once.
596 </para>
597
598 <para>
599 A partition must have the same column names and types as the partitioned
600 table to which it belongs. Modifications to the column names or types of
601 a partitioned table will automatically propagate to all partitions.
602 <literal>CHECK</literal> constraints will be inherited automatically by
603 every partition, but an individual partition may specify additional
604 <literal>CHECK</literal> constraints; additional constraints with the
605 same name and condition as in the parent will be merged with the parent
606 constraint. Defaults may be specified separately for each partition.
607 But note that a partition's default value is not applied when inserting
608 a tuple through a partitioned table.
609 </para>
610
611 <para>
612 Rows inserted into a partitioned table will be automatically routed to
613 the correct partition. If no suitable partition exists, an error will
614 occur.
615 </para>
616
617 <para>
618 Operations such as <command>TRUNCATE</command>
619 which normally affect a table and all of its
620 inheritance children will cascade to all partitions, but may also be
621 performed on an individual partition.
622 </para>
623
624 <para>
625 Note that creating a partition using <literal>PARTITION OF</literal>
626 requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the
627 parent partitioned table. Likewise, dropping a partition
628 with <command>DROP TABLE</command> requires taking
629 an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
630 It is possible to use <link linkend="sql-altertable"><command>ALTER
631 TABLE ATTACH/DETACH PARTITION</command></link> to perform these
632 operations with a weaker lock, thus reducing interference with
633 concurrent operations on the partitioned table.
634 </para>
635
636 </listitem>
637 </varlistentry>
638
639 <varlistentry id="sql-createtable-parms-like">
640 <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
641 <listitem>
642 <para>
643 The <literal>LIKE</literal> clause specifies a table from which
644 the new table automatically copies all column names, their data types,
645 and their not-null constraints.
646 </para>
647 <para>
648 Unlike <literal>INHERITS</literal>, the new table and original table
649 are completely decoupled after creation is complete. Changes to the
650 original table will not be applied to the new table, and it is not
651 possible to include data of the new table in scans of the original
652 table.
653 </para>
654 <para>
655 Also unlike <literal>INHERITS</literal>, columns and
656 constraints copied by <literal>LIKE</literal> are not merged with similarly
657 named columns and constraints.
658 If the same name is specified explicitly or in another
659 <literal>LIKE</literal> clause, an error is signaled.
660 </para>
661 <para>
662 The optional <replaceable>like_option</replaceable> clauses specify
663 which additional properties of the original table to copy. Specifying
664 <literal>INCLUDING</literal> copies the property, specifying
665 <literal>EXCLUDING</literal> omits the property.
666 <literal>EXCLUDING</literal> is the default. If multiple specifications
667 are made for the same kind of object, the last one is used. The
668 available options are:
669
670 <variablelist>
671 <varlistentry id="sql-createtable-parms-like-opt-comments">
672 <term><literal>INCLUDING COMMENTS</literal></term>
673 <listitem>
674 <para>
675 Comments for the copied columns, check constraints,
676 not-null constraints, indexes, and extended statistics will be
677 copied. The default behavior is to exclude comments, resulting in
678 the corresponding objects in the new table having no
679 comments.
680 </para>
681 </listitem>
682 </varlistentry>
683
684 <varlistentry id="sql-createtable-parms-like-opt-compression">
685 <term><literal>INCLUDING COMPRESSION</literal></term>
686 <listitem>
687 <para>
688 Compression method of the columns will be copied. The default
689 behavior is to exclude compression methods, resulting in columns
690 having the default compression method.
691 </para>
692 </listitem>
693 </varlistentry>
694
695 <varlistentry id="sql-createtable-parms-like-opt-constraints">
696 <term><literal>INCLUDING CONSTRAINTS</literal></term>
697 <listitem>
698 <para>
699 <literal>CHECK</literal> constraints will be copied. No distinction
700 is made between column constraints and table constraints. Not-null
701 constraints are always copied to the new table.
702 </para>
703 </listitem>
704 </varlistentry>
705
706 <varlistentry id="sql-createtable-parms-like-opt-defaults">
707 <term><literal>INCLUDING DEFAULTS</literal></term>
708 <listitem>
709 <para>
710 Default expressions for the copied column definitions will be
711 copied. Otherwise, default expressions are not copied, resulting in
712 the copied columns in the new table having null defaults. Note that
713 copying defaults that call database-modification functions, such as
714 <function>nextval</function>, may create a functional linkage
715 between the original and new tables.
716 </para>
717 </listitem>
718 </varlistentry>
719
720 <varlistentry id="sql-createtable-parms-like-opt-generated">
721 <term><literal>INCLUDING GENERATED</literal></term>
722 <listitem>
723 <para>
724 Any generation expressions as well as the stored/virtual choice of
725 copied column definitions will be copied. By default, new columns
726 will be regular base columns.
727 </para>
728 </listitem>
729 </varlistentry>
730
731 <varlistentry id="sql-createtable-parms-like-opt-identity">
732 <term><literal>INCLUDING IDENTITY</literal></term>
733 <listitem>
734 <para>
735 Any identity specifications of copied column definitions will be
736 copied. A new sequence is created for each identity column of the
737 new table, separate from the sequences associated with the old
738 table.
739 </para>
740 </listitem>
741 </varlistentry>
742
743 <varlistentry id="sql-createtable-parms-like-opt-indexes">
744 <term><literal>INCLUDING INDEXES</literal></term>
745 <listitem>
746 <para>
747 Indexes, <literal>PRIMARY KEY</literal>, <literal>UNIQUE</literal>,
748 and <literal>EXCLUDE</literal> constraints on the original table
749 will be created on the new table. Names for the new indexes and
750 constraints are chosen according to the default rules, regardless of
751 how the originals were named. (This behavior avoids possible
752 duplicate-name failures for the new indexes.)
753 </para>
754 </listitem>
755 </varlistentry>
756
757 <varlistentry id="sql-createtable-parms-like-opt-statistics">
758 <term><literal>INCLUDING STATISTICS</literal></term>
759 <listitem>
760 <para>
761 Extended statistics are copied to the new table.
762 </para>
763 </listitem>
764 </varlistentry>
765
766 <varlistentry id="sql-createtable-parms-like-opt-storage">
767 <term><literal>INCLUDING STORAGE</literal></term>
768 <listitem>
769 <para>
770 <literal>STORAGE</literal> settings for the copied column
771 definitions will be copied. The default behavior is to exclude
772 <literal>STORAGE</literal> settings, resulting in the copied columns
773 in the new table having type-specific default settings. For more on
774 <literal>STORAGE</literal> settings, see <xref
775 linkend="storage-toast"/>.
776 </para>
777 </listitem>
778 </varlistentry>
779
780 <varlistentry id="sql-createtable-parms-like-opt-all">
781 <term><literal>INCLUDING ALL</literal></term>
782 <listitem>
783 <para>
784 <literal>INCLUDING ALL</literal> is an abbreviated form selecting
785 all the available individual options. (It could be useful to write
786 individual <literal>EXCLUDING</literal> clauses after
787 <literal>INCLUDING ALL</literal> to select all but some specific
788 options.)
789 </para>
790 </listitem>
791 </varlistentry>
792 </variablelist>
793 </para>
794
795 <para>
796 The <literal>LIKE</literal> clause can also be used to copy column
797 definitions from views, foreign tables, or composite types.
798 Inapplicable options (e.g., <literal>INCLUDING INDEXES</literal> from
799 a view) are ignored.
800 </para>
801 </listitem>
802 </varlistentry>
803
804 <varlistentry id="sql-createtable-parms-constraint">
805 <term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
806 <listitem>
807 <para>
808 An optional name for a column or table constraint. If the
809 constraint is violated, the constraint name is present in error messages,
810 so constraint names like <literal>col must be positive</literal> can be used
811 to communicate helpful constraint information to client applications.
812 (Double-quotes are needed to specify constraint names that contain spaces.)
813 If a constraint name is not specified, the system generates a name.
814 </para>
815 </listitem>
816 </varlistentry>
817
818 <varlistentry id="sql-createtable-parms-not-null">
819 <term><literal>NOT NULL [ NO INHERIT ] </literal></term>
820 <listitem>
821 <para>
822 The column is not allowed to contain null values.
823 </para>
824
825 <para>
826 A constraint marked with <literal>NO INHERIT</literal> will not propagate to
827 child tables.
828 </para>
829 </listitem>
830 </varlistentry>
831
832 <varlistentry id="sql-createtable-parms-null">
833 <term><literal>NULL</literal></term>
834 <listitem>
835 <para>
836 The column is allowed to contain null values. This is the default.
837 </para>
838
839 <para>
840 This clause is only provided for compatibility with
841 non-standard SQL databases. Its use is discouraged in new
842 applications.
843 </para>
844 </listitem>
845 </varlistentry>
846
847 <varlistentry id="sql-createtable-parms-check">
848 <term><literal>CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] </literal></term>
849 <listitem>
850 <para>
851 The <literal>CHECK</literal> clause specifies an expression producing a
852 Boolean result which new or updated rows must satisfy for an
853 insert or update operation to succeed. Expressions evaluating
854 to TRUE or UNKNOWN succeed. Should any row of an insert or
855 update operation produce a FALSE result, an error exception is
856 raised and the insert or update does not alter the database. A
857 check constraint specified as a column constraint should
858 reference that column's value only, while an expression
859 appearing in a table constraint can reference multiple columns.
860 </para>
861
862 <para>
863 Currently, <literal>CHECK</literal> expressions cannot contain
864 subqueries nor refer to variables other than columns of the
865 current row (see <xref linkend="ddl-constraints-check-constraints"/>).
866 The system column <literal>tableoid</literal>
867 may be referenced, but not any other system column.
868 </para>
869
870 <para>
871 A constraint marked with <literal>NO INHERIT</literal> will not propagate to
872 child tables.
873 </para>
874
875 <para>
876 When a table has multiple <literal>CHECK</literal> constraints,
877 they will be tested for each row in alphabetical order by name,
878 after checking <literal>NOT NULL</literal> constraints.
879 (<productname>PostgreSQL</productname> versions before 9.5 did not honor any
880 particular firing order for <literal>CHECK</literal> constraints.)
881 </para>
882 </listitem>
883 </varlistentry>
884
885 <varlistentry id="sql-createtable-parms-default">
886 <term><literal>DEFAULT
887 <replaceable>default_expr</replaceable></literal></term>
888 <listitem>
889 <para>
890 The <literal>DEFAULT</literal> clause assigns a default data value for
891 the column whose column definition it appears within. The value
892 is any variable-free expression (in particular, cross-references
893 to other columns in the current table are not allowed). Subqueries
894 are not allowed either. The data type of the default expression must
895 match the data type of the column.
896 </para>
897
898 <para>
899 The default expression will be used in any insert operation that
900 does not specify a value for the column. If there is no default
901 for a column, then the default is null.
902 </para>
903 </listitem>
904 </varlistentry>
905
906 <varlistentry id="sql-createtable-parms-generated-stored">
907 <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
908 <listitem>
909 <para>
910 This clause creates the column as a <firstterm>generated
911 column</firstterm>. The column cannot be written to, and when read the
912 result of the specified expression will be returned.
913 </para>
914
915 <para>
916 When <literal>VIRTUAL</literal> is specified, the column will be
917 computed when it is read, and it will not occupy any storage. When
918 <literal>STORED</literal> is specified, the column will be computed on
919 write and will be stored on disk. <literal>VIRTUAL</literal> is the
920 default.
921 </para>
922
923 <para>
924 The generation expression can refer to other columns in the table, but
925 not other generated columns. Any functions and operators used must be
926 immutable. References to other tables are not allowed.
927 </para>
928
929 <para>
930 A virtual generated column cannot have a user-defined type, and the
931 generation expression of a virtual generated column must not reference
932 user-defined functions or types, that is, it can only use built-in
933 functions or types. This applies also indirectly, such as for functions
934 or types that underlie operators or casts. (This restriction does not
935 exist for stored generated columns.)
936 </para>
937 </listitem>
938 </varlistentry>
939
940 <varlistentry id="sql-createtable-parms-generated-identity">
941 <term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
942 <listitem>
943 <para>
944 This clause creates the column as an <firstterm>identity
945 column</firstterm>. It will have an implicit sequence attached to it
946 and in newly-inserted rows the column will automatically have values
947 from the sequence assigned to it.
948 Such a column is implicitly <literal>NOT NULL</literal>.
949 </para>
950
951 <para>
952 The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
953 determine how explicitly user-specified values are handled in
954 <command>INSERT</command> and <command>UPDATE</command> commands.
955 </para>
956
957 <para>
958 In an <command>INSERT</command> command, if <literal>ALWAYS</literal> is
959 selected, a user-specified value is only accepted if the
960 <command>INSERT</command> statement specifies <literal>OVERRIDING SYSTEM
961 VALUE</literal>. If <literal>BY DEFAULT</literal> is selected, then the
962 user-specified value takes precedence. See <xref linkend="sql-insert"/>
963 for details. (In the <command>COPY</command> command, user-specified
964 values are always used regardless of this setting.)
965 </para>
966
967 <para>
968 In an <command>UPDATE</command> command, if <literal>ALWAYS</literal> is
969 selected, any update of the column to any value other than
970 <literal>DEFAULT</literal> will be rejected. If <literal>BY
971 DEFAULT</literal> is selected, the column can be updated normally.
972 (There is no <literal>OVERRIDING</literal> clause for the
973 <command>UPDATE</command> command.)
974 </para>
975
976 <para>
977 The optional <replaceable>sequence_options</replaceable> clause can
978 be used to override the parameters of the sequence. The available
979 options include those shown for <xref linkend="sql-createsequence"/>,
980 plus <literal>SEQUENCE NAME <replaceable>name</replaceable></literal>,
981 <literal>LOGGED</literal>, and <literal>UNLOGGED</literal>, which
982 allow selection of the name and persistence level of the
983 sequence. Without <literal>SEQUENCE NAME</literal>, the system
984 chooses an unused name for the sequence.
985 Without <literal>LOGGED</literal> or <literal>UNLOGGED</literal>,
986 the sequence will have the same persistence level as the table.
987 </para>
988 </listitem>
989 </varlistentry>
990
991 <varlistentry id="sql-createtable-parms-unique">
992 <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
993 <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal>
994 <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
995
996 <listitem>
997 <para>
998 The <literal>UNIQUE</literal> constraint specifies that a
999 group of one or more columns of a table can contain
1000 only unique values. The behavior of a unique table constraint
1001 is the same as that of a unique column constraint, with the
1002 additional capability to span multiple columns. The constraint
1003 therefore enforces that any two rows must differ in at least one
1004 of these columns.
1005 </para>
1006
1007 <para>
1008 If the <literal>WITHOUT OVERLAPS</literal> option is specified for the
1009 last column, then that column is checked for overlaps instead of
1010 equality. In that case, the other columns of the constraint will allow
1011 duplicates so long as the duplicates don't overlap in the
1012 <literal>WITHOUT OVERLAPS</literal> column. (This is sometimes called a
1013 temporal key, if the column is a range of dates or timestamps, but
1014 <productname>PostgreSQL</productname> allows ranges over any base type.)
1015 In effect, such a constraint is enforced with an <literal>EXCLUDE</literal>
1016 constraint rather than a <literal>UNIQUE</literal> constraint. So for
1017 example <literal>UNIQUE (id, valid_at WITHOUT OVERLAPS)</literal> behaves
1018 like <literal>EXCLUDE USING GIST (id WITH =, valid_at WITH
1019 &amp;&amp;)</literal>. The <literal>WITHOUT OVERLAPS</literal> column
1020 must have a range or multirange type. Empty ranges/multiranges are
1021 not permitted. The non-<literal>WITHOUT OVERLAPS</literal> columns of
1022 the constraint can be any type that can be compared for equality in a
1023 GiST index. By default, only range types are supported, but you can use
1024 other types by adding the <xref linkend="btree-gist"/> extension (which
1025 is the expected way to use this feature).
1026 </para>
1027
1028 <para>
1029 For the purpose of a unique constraint, null values are not
1030 considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
1031 specified.
1032 </para>
1033
1034 <para>
1035 Each unique constraint should name a set of columns that is
1036 different from the set of columns named by any other unique or
1037 primary key constraint defined for the table. (Otherwise, redundant
1038 unique constraints will be discarded.)
1039 </para>
1040
1041 <para>
1042 When establishing a unique constraint for a multi-level partition
1043 hierarchy, all the columns in the partition key of the target
1044 partitioned table, as well as those of all its descendant partitioned
1045 tables, must be included in the constraint definition.
1046 </para>
1047
1048 <para>
1049 Adding a unique constraint will automatically create a unique btree
1050 index on the column or group of columns used in the constraint. But if
1051 the constraint includes a <literal>WITHOUT OVERLAPS</literal> clause, it
1052 will use a GiST index. The created index has the same name as the
1053 unique constraint.
1054 </para>
1055
1056 <para>
1057 The optional <literal>INCLUDE</literal> clause adds to that index
1058 one or more columns that are simply <quote>payload</quote>: uniqueness
1059 is not enforced on them, and the index cannot be searched on the basis
1060 of those columns. However they can be retrieved by an index-only scan.
1061 Note that although the constraint is not enforced on included columns,
1062 it still depends on them. Consequently, some operations on such columns
1063 (e.g., <literal>DROP COLUMN</literal>) can cause cascaded constraint and
1064 index deletion.
1065 </para>
1066 </listitem>
1067 </varlistentry>
1068
1069 <varlistentry id="sql-createtable-parms-primary-key">
1070 <term><literal>PRIMARY KEY</literal> (column constraint)</term>
1071 <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal>
1072 <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
1073 <listitem>
1074 <para>
1075 The <literal>PRIMARY KEY</literal> constraint specifies that a column or
1076 columns of a table can contain only unique (non-duplicate), nonnull
1077 values. Only one primary key can be specified for a table, whether as a
1078 column constraint or a table constraint.
1079 </para>
1080
1081 <para>
1082 The primary key constraint should name a set of columns that is
1083 different from the set of columns named by any unique
1084 constraint defined for the same table. (Otherwise, the unique
1085 constraint is redundant and will be discarded.)
1086 </para>
1087
1088 <para>
1089 <literal>PRIMARY KEY</literal> enforces the same data constraints as
1090 a combination of <literal>UNIQUE</literal> and <literal>NOT
1091 NULL</literal>. However,
1092 identifying a set of columns as the primary key also provides metadata
1093 about the design of the schema, since a primary key implies that other
1094 tables can rely on this set of columns as a unique identifier for rows.
1095 </para>
1096
1097 <para>
1098 When placed on a partitioned table, <literal>PRIMARY KEY</literal>
1099 constraints share the restrictions previously described
1100 for <literal>UNIQUE</literal> constraints.
1101 </para>
1102
1103 <para>
1104 Adding a <literal>PRIMARY KEY</literal> constraint will automatically
1105 create a unique btree index on the column or group of columns used in
1106 the constraint, or GiST if <literal>WITHOUT OVERLAPS</literal> was
1107 specified.
1108 </para>
1109
1110 <para>
1111 The optional <literal>INCLUDE</literal> clause adds to that index
1112 one or more columns that are simply <quote>payload</quote>: uniqueness
1113 is not enforced on them, and the index cannot be searched on the basis
1114 of those columns. However they can be retrieved by an index-only scan.
1115 Note that although the constraint is not enforced on included columns,
1116 it still depends on them. Consequently, some operations on such columns
1117 (e.g., <literal>DROP COLUMN</literal>) can cause cascaded constraint and
1118 index deletion.
1119 </para>
1120 </listitem>
1121 </varlistentry>
1122
1123 <varlistentry id="sql-createtable-exclude">
1124 <term><literal>EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ]</literal></term>
1125 <listitem>
1126 <para>
1127 The <literal>EXCLUDE</literal> clause defines an exclusion
1128 constraint, which guarantees that if
1129 any two rows are compared on the specified column(s) or
1130 expression(s) using the specified operator(s), not all of these
1131 comparisons will return <literal>TRUE</literal>. If all of the
1132 specified operators test for equality, this is equivalent to a
1133 <literal>UNIQUE</literal> constraint, although an ordinary unique constraint
1134 will be faster. However, exclusion constraints can specify
1135 constraints that are more general than simple equality.
1136 For example, you can specify a constraint that
1137 no two rows in the table contain overlapping circles
1138 (see <xref linkend="datatype-geometric"/>) by using the
1139 <literal>&amp;&amp;</literal> operator.
1140 The operator(s) are required to be commutative.
1141 </para>
1142
1143 <para>
1144 Exclusion constraints are implemented using
1145 an index that has the same name as the constraint, so each specified
1146 operator must be associated with an appropriate operator class
1147 (see <xref linkend="indexes-opclass"/>) for the index access
1148 method <replaceable>index_method</replaceable>.
1149 Each <replaceable class="parameter">exclude_element</replaceable>
1150 defines a column of the index, so it can optionally specify a collation,
1151 an operator class, operator class parameters, and/or ordering options;
1152 these are described fully under <xref linkend="sql-createindex"/>.
1153 </para>
1154
1155 <para>
1156 The access method must support <literal>amgettuple</literal> (see <xref
1157 linkend="indexam"/>); at present this means <acronym>GIN</acronym>
1158 cannot be used. Although it's allowed, there is little point in using
1159 B-tree or hash indexes with an exclusion constraint, because this
1160 does nothing that an ordinary unique constraint doesn't do better.
1161 So in practice the access method will always be <acronym>GiST</acronym> or
1162 <acronym>SP-GiST</acronym>.
1163 </para>
1164
1165 <para>
1166 The <replaceable class="parameter">predicate</replaceable> allows you to specify an
1167 exclusion constraint on a subset of the table; internally this creates a
1168 partial index. Note that parentheses are required around the predicate.
1169 </para>
1170
1171 <para>
1172 When establishing an exclusion constraint for a multi-level partition
1173 hierarchy, all the columns in the partition key of the target
1174 partitioned table, as well as those of all its descendant partitioned
1175 tables, must be included in the constraint definition. Additionally,
1176 those columns must be compared using the equality operator. These
1177 restrictions ensure that potentially-conflicting rows will exist in the
1178 same partition. The constraint may also refer to other columns which
1179 are not a part of any partition key, which can be compared using any
1180 appropriate operator.
1181 </para>
1182 </listitem>
1183 </varlistentry>
1184
1185 <varlistentry id="sql-createtable-parms-references">
1186 <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal> (column constraint)</term>
1187
1188 <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] )
1189 REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">refcolumn</replaceable> ] ) ]
1190 [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
1191 [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ]
1192 [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal>
1193 (table constraint)</term>
1194
1195 <listitem>
1196 <para>
1197 These clauses specify a foreign key constraint, which requires
1198 that a group of one or more columns of the new table must only
1199 contain values that match values in the referenced
1200 column(s) of some row of the referenced table. If the <replaceable
1201 class="parameter">refcolumn</replaceable> list is omitted, the
1202 primary key of the <replaceable class="parameter">reftable</replaceable>
1203 is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
1204 list must refer to the columns of a non-deferrable unique or primary key
1205 constraint or be the columns of a non-partial unique index.
1206 </para>
1207
1208 <para>
1209 If the last column is marked with <literal>PERIOD</literal>, it is
1210 treated in a special way. While the non-<literal>PERIOD</literal>
1211 columns are compared for equality (and there must be at least one of
1212 them), the <literal>PERIOD</literal> column is not. Instead, the
1213 constraint is considered satisfied if the referenced table has matching
1214 records (based on the non-<literal>PERIOD</literal> parts of the key)
1215 whose combined <literal>PERIOD</literal> values completely cover the
1216 referencing record's. In other words, the reference must have a
1217 referent for its entire duration. This column must be a range or
1218 multirange type. In addition, the referenced table must have a primary
1219 key or unique constraint declared with <literal>WITHOUT
1220 OVERLAPS</literal>. Finally, if the foreign key has a PERIOD
1221 <replaceable class="parameter">column_name</replaceable> specification
1222 the corresponding <replaceable class="parameter">refcolumn</replaceable>,
1223 if present, must also be marked <literal>PERIOD</literal>. If the
1224 <replaceable class="parameter">refcolumn</replaceable> clause is omitted,
1225 and thus the reftable's primary key constraint chosen, the primary key
1226 must have its final column marked <literal>WITHOUT OVERLAPS</literal>.
1227 </para>
1228
1229 <para>
1230 For each pair of referencing and referenced column, if they are of a
1231 collatable data type, then the collations must either be both
1232 deterministic or else both the same. This ensures that both columns
1233 have a consistent notion of equality.
1234 </para>
1235
1236 <para>
1237 The user
1238 must have <literal>REFERENCES</literal> permission on the referenced
1239 table (either the whole table, or the specific referenced columns). The
1240 addition of a foreign key constraint requires a
1241 <literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
1242 Note that foreign key constraints cannot be defined between temporary
1243 tables and permanent tables.
1244 </para>
1245
1246 <para>
1247 A value inserted into the referencing column(s) is matched against the
1248 values of the referenced table and referenced columns using the
1249 given match type. There are three match types: <literal>MATCH
1250 FULL</literal>, <literal>MATCH PARTIAL</literal>, and <literal>MATCH
1251 SIMPLE</literal> (which is the default). <literal>MATCH
1252 FULL</literal> will not allow one column of a multicolumn foreign key
1253 to be null unless all foreign key columns are null; if they are all
1254 null, the row is not required to have a match in the referenced table.
1255 <literal>MATCH SIMPLE</literal> allows any of the foreign key columns
1256 to be null; if any of them are null, the row is not required to have a
1257 match in the referenced table.
1258 <literal>MATCH PARTIAL</literal> is not yet implemented.
1259 (Of course, <literal>NOT NULL</literal> constraints can be applied to the
1260 referencing column(s) to prevent these cases from arising.)
1261 </para>
1262
1263 <para>
1264 In addition, when the data in the referenced columns is changed,
1265 certain actions are performed on the data in this table's
1266 columns. The <literal>ON DELETE</literal> clause specifies the
1267 action to perform when a referenced row in the referenced table is
1268 being deleted. Likewise, the <literal>ON UPDATE</literal>
1269 clause specifies the action to perform when a referenced column
1270 in the referenced table is being updated to a new value. If the
1271 row is updated, but the referenced column is not actually
1272 changed, no action is done. Referential actions are executed as part of
1273 the data changing command, even if the constraint is deferred. There
1274 are the following possible actions for each clause:
1275
1276 <variablelist>
1277 <varlistentry id="sql-createtable-parms-references-refact-no-action">
1278 <term><literal>NO ACTION</literal></term>
1279 <listitem>
1280 <para>
1281 Produce an error if the deletion or update
1282 would create a foreign key constraint violation.
1283 If the constraint is deferred, this
1284 error will be produced at constraint check time if there still
1285 exist any referencing rows. This is the default action.
1286 </para>
1287 </listitem>
1288 </varlistentry>
1289
1290 <varlistentry id="sql-createtable-parms-references-refact-restrict">
1291 <term><literal>RESTRICT</literal></term>
1292 <listitem>
1293 <para>
1294 Produce an error if a row to be deleted or updated matches a row in
1295 the referencing table. This prevents the action even if the state
1296 after the action would not violate the foreign key constraint. In
1297 particular, it prevents updates of referenced rows to values that
1298 are distinct but compare as equal. (But it does not prevent
1299 <quote>no-op</quote> updates that update a column to the same
1300 value.)
1301 </para>
1302
1303 <para>
1304 In a temporal foreign key, this option is not supported.
1305 </para>
1306 </listitem>
1307 </varlistentry>
1308
1309 <varlistentry id="sql-createtable-parms-references-refact-cascade">
1310 <term><literal>CASCADE</literal></term>
1311 <listitem>
1312 <para>
1313 Delete any rows referencing the deleted row, or update the
1314 values of the referencing column(s) to the new values of the
1315 referenced columns, respectively.
1316 </para>
1317
1318 <para>
1319 In a temporal foreign key, this option is not supported.
1320 </para>
1321 </listitem>
1322 </varlistentry>
1323
1324 <varlistentry id="sql-createtable-parms-references-refact-set-null">
1325 <term><literal>SET NULL [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term>
1326 <listitem>
1327 <para>
1328 Set all of the referencing columns, or a specified subset of the
1329 referencing columns, to null. A subset of columns can only be
1330 specified for <literal>ON DELETE</literal> actions.
1331 </para>
1332
1333 <para>
1334 In a temporal foreign key, this option is not supported.
1335 </para>
1336 </listitem>
1337 </varlistentry>
1338
1339 <varlistentry id="sql-createtable-parms-references-refact-set-default">
1340 <term><literal>SET DEFAULT [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term>
1341 <listitem>
1342 <para>
1343 Set all of the referencing columns, or a specified subset of the
1344 referencing columns, to their default values. A subset of columns
1345 can only be specified for <literal>ON DELETE</literal> actions.
1346 (There must be a row in the referenced table matching the default
1347 values, if they are not null, or the operation will fail.)
1348 </para>
1349
1350 <para>
1351 In a temporal foreign key, this option is not supported.
1352 </para>
1353 </listitem>
1354 </varlistentry>
1355 </variablelist>
1356 </para>
1357
1358 <para>
1359 If the referenced column(s) are changed frequently, it might be wise to
1360 add an index to the referencing column(s) so that referential actions
1361 associated with the foreign key constraint can be performed more
1362 efficiently.
1363 </para>
1364 </listitem>
1365 </varlistentry>
1366
1367 <varlistentry id="sql-createtable-parms-deferrable">
1368 <term><literal>DEFERRABLE</literal></term>
1369 <term><literal>NOT DEFERRABLE</literal></term>
1370 <listitem>
1371 <para>
1372 This controls whether the constraint can be deferred. A
1373 constraint that is not deferrable will be checked immediately
1374 after every command. Checking of constraints that are
1375 deferrable can be postponed until the end of the transaction
1376 (using the <link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link> command).
1377 <literal>NOT DEFERRABLE</literal> is the default.
1378 Currently, only <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
1379 <literal>EXCLUDE</literal>, and
1380 <literal>REFERENCES</literal> (foreign key) constraints accept this
1381 clause. <literal>NOT NULL</literal> and <literal>CHECK</literal> constraints are not
1382 deferrable. Note that deferrable constraints cannot be used as
1383 conflict arbiters in an <command>INSERT</command> statement that
1384 includes an <literal>ON CONFLICT</literal> clause.
1385 </para>
1386 </listitem>
1387 </varlistentry>
1388
1389 <varlistentry id="sql-createtable-parms-initially">
1390 <term><literal>INITIALLY IMMEDIATE</literal></term>
1391 <term><literal>INITIALLY DEFERRED</literal></term>
1392 <listitem>
1393 <para>
1394 If a constraint is deferrable, this clause specifies the default
1395 time to check the constraint. If the constraint is
1396 <literal>INITIALLY IMMEDIATE</literal>, it is checked after each
1397 statement. This is the default. If the constraint is
1398 <literal>INITIALLY DEFERRED</literal>, it is checked only at the
1399 end of the transaction. The constraint check time can be
1400 altered with the <link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link> command.
1401 </para>
1402 </listitem>
1403 </varlistentry>
1404
1405 <varlistentry id="sql-createtable-parms-enforced">
1406 <term><literal>ENFORCED</literal></term>
1407 <term><literal>NOT ENFORCED</literal></term>
1408 <listitem>
1409 <para>
1410 When the constraint is <literal>ENFORCED</literal>, then the database
1411 system will ensure that the constraint is satisfied, by checking the
1412 constraint at appropriate times (after each statement or at the end of
1413 the transaction, as appropriate). That is the default. If the
1414 constraint is <literal>NOT ENFORCED</literal>, the database system will
1415 not check the constraint. It is then up to the application code to
1416 ensure that the constraints are satisfied. The database system might
1417 still assume that the data actually satisfies the constraint for
1418 optimization decisions where this does not affect the correctness of the
1419 result.
1420 </para>
1421
1422 <para>
1423 <literal>NOT ENFORCED</literal> constraints can be useful as
1424 documentation if the actual checking of the constraint at run time is
1425 too expensive.
1426 </para>
1427
1428 <para>
1429 This is currently only supported for foreign key and <literal>CHECK</literal>
1430 constraints.
1431 </para>
1432 </listitem>
1433 </varlistentry>
1434
1435 <varlistentry id="sql-createtable-method">
1436 <term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
1437 <listitem>
1438 <para>
1439 This optional clause specifies the table access method to use to store
1440 the contents for the new table; the method needs be an access method of
1441 type <literal>TABLE</literal>. See <xref linkend="tableam"/> for more
1442 information. If this option is not specified, the default table access
1443 method is chosen for the new table. See <xref
1444 linkend="guc-default-table-access-method"/> for more information.
1445 </para>
1446 <para>
1447 When creating a partition, the table access method is the access method
1448 of its partitioned table, if set.
1449 </para>
1450 </listitem>
1451 </varlistentry>
1452
1453 <varlistentry id="sql-createtable-parms-with">
1454 <term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
1455 <listitem>
1456 <para>
1457 This clause specifies optional storage parameters for a table or index;
1458 see <xref linkend="sql-createtable-storage-parameters"/> below for more
1459 information. For backward-compatibility the <literal>WITH</literal>
1460 clause for a table can also include <literal>OIDS=FALSE</literal> to
1461 specify that rows of the new table should not contain OIDs (object
1462 identifiers), <literal>OIDS=TRUE</literal> is not supported anymore.
1463 </para>
1464 </listitem>
1465 </varlistentry>
1466
1467 <varlistentry id="sql-createtable-parms-without-oids">
1468 <term><literal>WITHOUT OIDS</literal></term>
1469 <listitem>
1470 <para>
1471 This is backward-compatible syntax for declaring a table
1472 <literal>WITHOUT OIDS</literal>, creating a table <literal>WITH
1473 OIDS</literal> is not supported anymore.
1474 </para>
1475 </listitem>
1476 </varlistentry>
1477
1478 <varlistentry id="sql-createtable-parms-on-commit">
1479 <term><literal>ON COMMIT</literal></term>
1480 <listitem>
1481 <para>
1482 The behavior of temporary tables at the end of a transaction
1483 block can be controlled using <literal>ON COMMIT</literal>.
1484 The three options are:
1485
1486 <variablelist>
1487 <varlistentry id="sql-createtable-parms-on-commit-preserve-rows">
1488 <term><literal>PRESERVE ROWS</literal></term>
1489 <listitem>
1490 <para>
1491 No special action is taken at the ends of transactions.
1492 This is the default behavior.
1493 </para>
1494 </listitem>
1495 </varlistentry>
1496
1497 <varlistentry id="sql-createtable-parms-on-commit-delete-rows">
1498 <term><literal>DELETE ROWS</literal></term>
1499 <listitem>
1500 <para>
1501 All rows in the temporary table will be deleted at the end
1502 of each transaction block. Essentially, an automatic <link
1503 linkend="sql-truncate"><command>TRUNCATE</command></link> is done
1504 at each commit. When used on a partitioned table, this
1505 is not cascaded to its partitions.
1506 </para>
1507 </listitem>
1508 </varlistentry>
1509
1510 <varlistentry id="sql-createtable-parms-on-commit-drop">
1511 <term><literal>DROP</literal></term>
1512 <listitem>
1513 <para>
1514 The temporary table will be dropped at the end of the current
1515 transaction block. When used on a partitioned table, this action
1516 drops its partitions and when used on tables with inheritance
1517 children, it drops the dependent children.
1518 </para>
1519 </listitem>
1520 </varlistentry>
1521 </variablelist></para>
1522 </listitem>
1523 </varlistentry>
1524
1525 <varlistentry id="sql-createtable-tablespace">
1526 <term><literal>TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
1527 <listitem>
1528 <para>
1529 The <replaceable class="parameter">tablespace_name</replaceable> is the name
1530 of the tablespace in which the new table is to be created.
1531 If not specified,
1532 <xref linkend="guc-default-tablespace"/> is consulted, or
1533 <xref linkend="guc-temp-tablespaces"/> if the table is temporary. For
1534 partitioned tables, since no storage is required for the table itself,
1535 the tablespace specified overrides <literal>default_tablespace</literal>
1536 as the default tablespace to use for any newly created partitions when no
1537 other tablespace is explicitly specified.
1538 </para>
1539 </listitem>
1540 </varlistentry>
1541
1542 <varlistentry id="sql-createtable-parms-using-index-tablespace">
1543 <term><literal>USING INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
1544 <listitem>
1545 <para>
1546 This clause allows selection of the tablespace in which the index
1547 associated with a <literal>UNIQUE</literal>, <literal>PRIMARY
1548 KEY</literal>, or <literal>EXCLUDE</literal> constraint will be created.
1549 If not specified,
1550 <xref linkend="guc-default-tablespace"/> is consulted, or
1551 <xref linkend="guc-temp-tablespaces"/> if the table is temporary.
1552 </para>
1553 </listitem>
1554 </varlistentry>
1555
1556 </variablelist>
1557
1558 <refsect2 id="sql-createtable-storage-parameters" xreflabel="Storage Parameters">
1559 <title>Storage Parameters</title>
1560
1561 <indexterm zone="sql-createtable-storage-parameters">
1562 <primary>storage parameters</primary>
1563 </indexterm>
1564
1565 <para>
1566 The <literal>WITH</literal> clause can specify <firstterm>storage parameters</firstterm>
1567 for tables, and for indexes associated with a <literal>UNIQUE</literal>,
1568 <literal>PRIMARY KEY</literal>, or <literal>EXCLUDE</literal> constraint.
1569 Storage parameters for
1570 indexes are documented in <xref linkend="sql-createindex"/>.
1571 The storage parameters currently
1572 available for tables are listed below. For many of these parameters, as
1573 shown, there is an additional parameter with the same name prefixed with
1574 <literal>toast.</literal>, which controls the behavior of the
1575 table's secondary <acronym>TOAST</acronym> table, if any
1576 (see <xref linkend="storage-toast"/> for more information about TOAST).
1577 If a table parameter value is set and the
1578 equivalent <literal>toast.</literal> parameter is not, the TOAST table
1579 will use the table's parameter value.
1580 Specifying these parameters for partitioned tables is not supported,
1581 but you may specify them for individual leaf partitions.
1582 </para>
1583
1584 <variablelist>
1585
1586 <varlistentry id="reloption-fillfactor" xreflabel="fillfactor">
1587 <term><varname>fillfactor</varname> (<type>integer</type>)
1588 <indexterm>
1589 <primary><varname>fillfactor</varname> storage parameter</primary>
1590 </indexterm>
1591 </term>
1592 <listitem>
1593 <para>
1594 The fillfactor for a table is a percentage between 10 and 100.
1595 100 (complete packing) is the default. When a smaller fillfactor
1596 is specified, <command>INSERT</command> operations pack table pages only
1597 to the indicated percentage; the remaining space on each page is
1598 reserved for updating rows on that page. This gives <command>UPDATE</command>
1599 a chance to place the updated copy of a row on the same page as the
1600 original, which is more efficient than placing it on a different
1601 page, and makes <link linkend="storage-hot">heap-only tuple
1602 updates</link> more likely.
1603 For a table whose entries are never updated, complete packing is the
1604 best choice, but in heavily updated tables smaller fillfactors are
1605 appropriate. This parameter cannot be set for TOAST tables.
1606 </para>
1607 </listitem>
1608 </varlistentry>
1609
1610 <varlistentry id="reloption-toast-tuple-target" xreflabel="toast_tuple_target">
1611 <term><literal>toast_tuple_target</literal> (<type>integer</type>)
1612 <indexterm>
1613 <primary><varname>toast_tuple_target</varname> storage parameter</primary>
1614 </indexterm>
1615 </term>
1616 <listitem>
1617 <para>
1618 The toast_tuple_target specifies the minimum tuple length required before
1619 we try to compress and/or move long column values into TOAST tables, and
1620 is also the target length we try to reduce the length below once toasting
1621 begins. This affects columns marked as External (for move),
1622 Main (for compression), or Extended (for both) and applies only to new
1623 tuples. There is no effect on existing rows.
1624 By default this parameter is set to allow at least 4 tuples per block,
1625 which with the default block size will be 2040 bytes. Valid values are
1626 between 128 bytes and the (block size - header), by default 8160 bytes.
1627 Changing this value may not be useful for very short or very long rows.
1628 Note that the default setting is often close to optimal, and
1629 it is possible that setting this parameter could have negative
1630 effects in some cases.
1631 This parameter cannot be set for TOAST tables.
1632 </para>
1633 </listitem>
1634 </varlistentry>
1635
1636 <varlistentry id="reloption-parallel-workers" xreflabel="parallel_workers">
1637 <term><literal>parallel_workers</literal> (<type>integer</type>)
1638 <indexterm>
1639 <primary><varname>parallel_workers</varname> storage parameter</primary>
1640 </indexterm>
1641 </term>
1642 <listitem>
1643 <para>
1644 This sets the number of workers that should be used to assist a parallel
1645 scan of this table. If not set, the system will determine a value based
1646 on the relation size. The actual number of workers chosen by the planner
1647 or by utility statements that use parallel scans may be less, for example
1648 due to the setting of <xref linkend="guc-max-worker-processes"/>.
1649 </para>
1650 </listitem>
1651 </varlistentry>
1652
1653 <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
1654 <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
1655 <indexterm>
1656 <primary><varname>autovacuum_enabled</varname> storage parameter</primary>
1657 </indexterm>
1658 </term>
1659 <listitem>
1660 <para>
1661 Enables or disables the autovacuum daemon for a particular table.
1662 If true, the autovacuum daemon will perform automatic <command>VACUUM</command>
1663 and/or <command>ANALYZE</command> operations on this table following the rules
1664 discussed in <xref linkend="autovacuum"/>.
1665 If false, this table will not be autovacuumed, except to prevent
1666 transaction ID wraparound. See <xref linkend="vacuum-for-wraparound"/> for
1667 more about wraparound prevention.
1668 Note that the autovacuum daemon does not run at all (except to prevent
1669 transaction ID wraparound) if the <xref linkend="guc-autovacuum"/>
1670 parameter is false; setting individual tables' storage parameters does
1671 not override that. Therefore there is seldom much point in explicitly
1672 setting this storage parameter to <literal>true</literal>, only
1673 to <literal>false</literal>.
1674 </para>
1675 </listitem>
1676 </varlistentry>
1677
1678 <varlistentry id="reloption-vacuum-index-cleanup" xreflabel="vacuum_index_cleanup">
1679 <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>enum</type>)
1680 <indexterm>
1681 <primary><varname>vacuum_index_cleanup</varname> storage parameter</primary>
1682 </indexterm>
1683 </term>
1684 <listitem>
1685 <para>
1686 Forces or disables index cleanup when <command>VACUUM</command>
1687 is run on this table. The default value is
1688 <literal>AUTO</literal>. With <literal>OFF</literal>, index
1689 cleanup is disabled, with <literal>ON</literal> it is enabled,
1690 and with <literal>AUTO</literal> a decision is made dynamically,
1691 each time <command>VACUUM</command> runs. The dynamic behavior
1692 allows <command>VACUUM</command> to avoid needlessly scanning
1693 indexes to remove very few dead tuples. Forcibly disabling all
1694 index cleanup can speed up <command>VACUUM</command> very
1695 significantly, but may also lead to severely bloated indexes if
1696 table modifications are frequent. The
1697 <literal>INDEX_CLEANUP</literal> parameter of <link
1698 linkend="sql-vacuum"><command>VACUUM</command></link>, if
1699 specified, overrides the value of this option.
1700 </para>
1701 </listitem>
1702 </varlistentry>
1703
1704 <varlistentry id="reloption-vacuum-truncate" xreflabel="vacuum_truncate">
1705 <term><literal>vacuum_truncate</literal>, <literal>toast.vacuum_truncate</literal> (<type>boolean</type>)
1706 <indexterm>
1707 <primary><varname>vacuum_truncate</varname></primary>
1708 <secondary>storage parameter</secondary>
1709 </indexterm>
1710 </term>
1711 <listitem>
1712 <para>
1713 Per-table value for <xref linkend="guc-vacuum-truncate"/> parameter. The
1714 <literal>TRUNCATE</literal> parameter of
1715 <link linkend="sql-vacuum"><command>VACUUM</command></link>, if
1716 specified, overrides the value of this option.
1717 </para>
1718 </listitem>
1719 </varlistentry>
1720
1721 <varlistentry id="reloption-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
1722 <term><literal>autovacuum_vacuum_threshold</literal>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</type>)
1723 <indexterm>
1724 <primary><varname>autovacuum_vacuum_threshold</varname></primary>
1725 <secondary>storage parameter</secondary>
1726 </indexterm>
1727 </term>
1728 <listitem>
1729 <para>
1730 Per-table value for <xref linkend="guc-autovacuum-vacuum-threshold"/>
1731 parameter.
1732 </para>
1733 </listitem>
1734 </varlistentry>
1735
1736 <varlistentry id="reloption-autovacuum-vacuum-max-threshold" xreflabel="autovacuum_vacuum_max_threshold">
1737 <term><literal>autovacuum_vacuum_max_threshold</literal>, <literal>toast.autovacuum_vacuum_max_threshold</literal> (<type>integer</type>)
1738 <indexterm>
1739 <primary><varname>autovacuum_vacuum_max_threshold</varname></primary>
1740 <secondary>storage parameter</secondary>
1741 </indexterm>
1742 </term>
1743 <listitem>
1744 <para>
1745 Per-table value for <xref linkend="guc-autovacuum-vacuum-max-threshold"/>
1746 parameter.
1747 </para>
1748 </listitem>
1749 </varlistentry>
1750
1751 <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
1752 <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
1753 <indexterm>
1754 <primary><varname>autovacuum_vacuum_scale_factor</varname> </primary>
1755 <secondary>storage parameter</secondary>
1756 </indexterm>
1757 </term>
1758 <listitem>
1759 <para>
1760 Per-table value for <xref linkend="guc-autovacuum-vacuum-scale-factor"/>
1761 parameter.
1762 </para>
1763 </listitem>
1764 </varlistentry>
1765
1766 <varlistentry id="reloption-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
1767 <term><literal>autovacuum_vacuum_insert_threshold</literal>, <literal>toast.autovacuum_vacuum_insert_threshold</literal> (<type>integer</type>)
1768 <indexterm>
1769 <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
1770 <secondary>storage parameter</secondary>
1771 </indexterm>
1772 </term>
1773 <listitem>
1774 <para>
1775 Per-table value for <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>
1776 parameter. The special value of -1 may be used to disable insert vacuums on the table.
1777 </para>
1778 </listitem>
1779 </varlistentry>
1780
1781 <varlistentry id="reloption-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
1782 <term><literal>autovacuum_vacuum_insert_scale_factor</literal>, <literal>toast.autovacuum_vacuum_insert_scale_factor</literal> (<type>floating point</type>)
1783 <indexterm>
1784 <primary><varname>autovacuum_vacuum_insert_scale_factor</varname> </primary>
1785 <secondary>storage parameter</secondary>
1786 </indexterm>
1787 </term>
1788 <listitem>
1789 <para>
1790 Per-table value for <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>
1791 parameter.
1792 </para>
1793 </listitem>
1794 </varlistentry>
1795
1796 <varlistentry id="reloption-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
1797 <term><literal>autovacuum_analyze_threshold</literal> (<type>integer</type>)
1798 <indexterm>
1799 <primary><varname>autovacuum_analyze_threshold</varname></primary>
1800 <secondary>storage parameter</secondary>
1801 </indexterm>
1802 </term>
1803 <listitem>
1804 <para>
1805 Per-table value for <xref linkend="guc-autovacuum-analyze-threshold"/>
1806 parameter.
1807 </para>
1808 </listitem>
1809 </varlistentry>
1810
1811 <varlistentry id="reloption-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
1812 <term><literal>autovacuum_analyze_scale_factor</literal> (<type>floating point</type>)
1813 <indexterm>
1814 <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
1815 <secondary>storage parameter</secondary>
1816 </indexterm>
1817 </term>
1818 <listitem>
1819 <para>
1820 Per-table value for <xref linkend="guc-autovacuum-analyze-scale-factor"/>
1821 parameter.
1822 </para>
1823 </listitem>
1824 </varlistentry>
1825
1826 <varlistentry id="reloption-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
1827 <term><literal>autovacuum_vacuum_cost_delay</literal>, <literal>toast.autovacuum_vacuum_cost_delay</literal> (<type>floating point</type>)
1828 <indexterm>
1829 <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
1830 <secondary>storage parameter</secondary>
1831 </indexterm>
1832 </term>
1833 <listitem>
1834 <para>
1835 Per-table value for <xref linkend="guc-autovacuum-vacuum-cost-delay"/>
1836 parameter.
1837 </para>
1838 </listitem>
1839 </varlistentry>
1840
1841 <varlistentry id="reloption-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
1842 <term><literal>autovacuum_vacuum_cost_limit</literal>, <literal>toast.autovacuum_vacuum_cost_limit</literal> (<type>integer</type>)
1843 <indexterm>
1844 <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
1845 <secondary>storage parameter</secondary>
1846 </indexterm>
1847 </term>
1848 <listitem>
1849 <para>
1850 Per-table value for <xref linkend="guc-autovacuum-vacuum-cost-limit"/>
1851 parameter.
1852 </para>
1853 </listitem>
1854 </varlistentry>
1855
1856 <varlistentry id="reloption-autovacuum-freeze-min-age" xreflabel="autovacuum_freeze_min_age">
1857 <term><literal>autovacuum_freeze_min_age</literal>, <literal>toast.autovacuum_freeze_min_age</literal> (<type>integer</type>)
1858 <indexterm>
1859 <primary><varname>autovacuum_freeze_min_age</varname> storage parameter</primary>
1860 </indexterm>
1861 </term>
1862 <listitem>
1863 <para>
1864 Per-table value for <xref linkend="guc-vacuum-freeze-min-age"/>
1865 parameter. Note that autovacuum will ignore
1866 per-table <literal>autovacuum_freeze_min_age</literal> parameters that are
1867 larger than half the
1868 system-wide <xref linkend="guc-autovacuum-freeze-max-age"/> setting.
1869 </para>
1870 </listitem>
1871 </varlistentry>
1872
1873 <varlistentry id="reloption-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
1874 <term><literal>autovacuum_freeze_max_age</literal>, <literal>toast.autovacuum_freeze_max_age</literal> (<type>integer</type>)
1875 <indexterm>
1876 <primary><varname>autovacuum_freeze_max_age</varname></primary>
1877 <secondary>storage parameter</secondary>
1878 </indexterm>
1879 </term>
1880 <listitem>
1881 <para>
1882 Per-table value for <xref linkend="guc-autovacuum-freeze-max-age"/>
1883 parameter. Note that autovacuum will ignore
1884 per-table <literal>autovacuum_freeze_max_age</literal> parameters that are
1885 larger than the system-wide setting (it can only be set smaller).
1886 </para>
1887 </listitem>
1888 </varlistentry>
1889
1890 <varlistentry id="reloption-autovacuum-freeze-table-age" xreflabel="autovacuum_freeze_table_age">
1891 <term><literal>autovacuum_freeze_table_age</literal>, <literal>toast.autovacuum_freeze_table_age</literal> (<type>integer</type>)
1892 <indexterm>
1893 <primary><varname>autovacuum_freeze_table_age</varname> storage parameter</primary>
1894 </indexterm>
1895 </term>
1896 <listitem>
1897 <para>
1898 Per-table value for <xref linkend="guc-vacuum-freeze-table-age"/>
1899 parameter.
1900 </para>
1901 </listitem>
1902 </varlistentry>
1903
1904 <varlistentry id="reloption-autovacuum-multixact-freeze-min-age" xreflabel="autovacuum_multixact_freeze_min_age">
1905 <term><literal>autovacuum_multixact_freeze_min_age</literal>, <literal>toast.autovacuum_multixact_freeze_min_age</literal> (<type>integer</type>)
1906 <indexterm>
1907 <primary><varname>autovacuum_multixact_freeze_min_age</varname> storage parameter</primary>
1908 </indexterm>
1909 </term>
1910 <listitem>
1911 <para>
1912 Per-table value for <xref linkend="guc-vacuum-multixact-freeze-min-age"/>
1913 parameter. Note that autovacuum will ignore
1914 per-table <literal>autovacuum_multixact_freeze_min_age</literal> parameters
1915 that are larger than half the
1916 system-wide <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>
1917 setting.
1918 </para>
1919 </listitem>
1920 </varlistentry>
1921
1922 <varlistentry id="reloption-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
1923 <term><literal>autovacuum_multixact_freeze_max_age</literal>, <literal>toast.autovacuum_multixact_freeze_max_age</literal> (<type>integer</type>)
1924 <indexterm>
1925 <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
1926 <secondary>storage parameter</secondary>
1927 </indexterm>
1928 </term>
1929 <listitem>
1930 <para>
1931 Per-table value
1932 for <xref linkend="guc-autovacuum-multixact-freeze-max-age"/> parameter.
1933 Note that autovacuum will ignore
1934 per-table <literal>autovacuum_multixact_freeze_max_age</literal> parameters
1935 that are larger than the system-wide setting (it can only be set
1936 smaller).
1937 </para>
1938 </listitem>
1939 </varlistentry>
1940
1941 <varlistentry id="reloption-autovacuum-multixact-freeze-table-age" xreflabel="autovacuum_multixact_freeze_table_age">
1942 <term><literal>autovacuum_multixact_freeze_table_age</literal>, <literal>toast.autovacuum_multixact_freeze_table_age</literal> (<type>integer</type>)
1943 <indexterm>
1944 <primary><varname>autovacuum_multixact_freeze_table_age</varname> storage parameter</primary>
1945 </indexterm>
1946 </term>
1947 <listitem>
1948 <para>
1949 Per-table value
1950 for <xref linkend="guc-vacuum-multixact-freeze-table-age"/> parameter.
1951 </para>
1952 </listitem>
1953 </varlistentry>
1954
1955 <varlistentry id="reloption-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration">
1956 <term><literal>log_autovacuum_min_duration</literal>, <literal>toast.log_autovacuum_min_duration</literal> (<type>integer</type>)
1957 <indexterm>
1958 <primary><varname>log_autovacuum_min_duration</varname></primary>
1959 <secondary>storage parameter</secondary>
1960 </indexterm>
1961 </term>
1962 <listitem>
1963 <para>
1964 Per-table value for <xref linkend="guc-log-autovacuum-min-duration"/>
1965 parameter.
1966 </para>
1967 </listitem>
1968 </varlistentry>
1969
1970 <varlistentry id="reloption-vacuum-max-eager-freeze-failure-rate" xreflabel="vacuum_max_eager_freeze_failure_rate">
1971 <term><literal>vacuum_max_eager_freeze_failure_rate</literal>, <literal>toast.vacuum_max_eager_freeze_failure_rate</literal> (<type>floating point</type>)
1972 <indexterm>
1973 <primary><varname>vacuum_max_eager_freeze_failure_rate</varname></primary>
1974 <secondary>storage parameter</secondary>
1975 </indexterm>
1976 </term>
1977 <listitem>
1978 <para>
1979 Per-table value for <xref linkend="guc-vacuum-max-eager-freeze-failure-rate"/>
1980 parameter.
1981 </para>
1982 </listitem>
1983 </varlistentry>
1984
1985 <varlistentry id="reloption-user-catalog-table" xreflabel="user_catalog_table">
1986 <term><literal>user_catalog_table</literal> (<type>boolean</type>)
1987 <indexterm>
1988 <primary><varname>user_catalog_table</varname> storage parameter</primary>
1989 </indexterm>
1990 </term>
1991 <listitem>
1992 <para>
1993 Declare the table as an additional catalog table for purposes of
1994 logical replication. See
1995 <xref linkend="logicaldecoding-capabilities"/> for details.
1996 This parameter cannot be set for TOAST tables.
1997 </para>
1998 </listitem>
1999 </varlistentry>
2000
2001 </variablelist>
2002
2003 </refsect2>
2004 </refsect1>
2005
2006 <refsect1 id="sql-createtable-notes">
2007 <title>Notes</title>
2008 <para>
2009 <productname>PostgreSQL</productname> automatically creates an
2010 index for each unique constraint and primary key constraint to
2011 enforce uniqueness. Thus, it is not necessary to create an
2012 index explicitly for primary key columns. (See <xref
2013 linkend="sql-createindex"/> for more information.)
2014 </para>
2015
2016 <para>
2017 Unique constraints and primary keys are not inherited in the
2018 current implementation. This makes the combination of
2019 inheritance and unique constraints rather dysfunctional.
2020 </para>
2021
2022 <para>
2023 A table cannot have more than 1600 columns. (In practice, the
2024 effective limit is usually lower because of tuple-length constraints.)
2025 </para>
2026
2027 </refsect1>
2028
2029
2030 <refsect1 id="sql-createtable-examples">
2031 <title>Examples</title>
2032
2033 <para>
2034 Create table <structname>films</structname> and table
2035 <structname>distributors</structname>:
2036
2037 <programlisting>
2038 CREATE TABLE films (
2039 code char(5) CONSTRAINT firstkey PRIMARY KEY,
2040 title varchar(40) NOT NULL,
2041 did integer NOT NULL,
2042 date_prod date,
2043 kind varchar(10),
2044 len interval hour to minute
2045 );
2046
2047 CREATE TABLE distributors (
2048 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
2049 name varchar(40) NOT NULL CHECK (name &lt;&gt; '')
2050 );
2051 </programlisting>
2052 </para>
2053
2054 <para>
2055 Create a table with a 2-dimensional array:
2056
2057 <programlisting>
2058 CREATE TABLE array_int (
2059 vector int[][]
2060 );
2061 </programlisting>
2062 </para>
2063
2064 <para>
2065 Define a unique table constraint for the table
2066 <literal>films</literal>. Unique table constraints can be defined
2067 on one or more columns of the table:
2068
2069 <programlisting>
2070 CREATE TABLE films (
2071 code char(5),
2072 title varchar(40),
2073 did integer,
2074 date_prod date,
2075 kind varchar(10),
2076 len interval hour to minute,
2077 CONSTRAINT production UNIQUE(date_prod)
2078 );
2079 </programlisting>
2080 </para>
2081
2082 <para>
2083 Define a check column constraint:
2084
2085 <programlisting>
2086 CREATE TABLE distributors (
2087 did integer CHECK (did &gt; 100),
2088 name varchar(40)
2089 );
2090 </programlisting>
2091 </para>
2092
2093 <para>
2094 Define a check table constraint:
2095
2096 <programlisting>
2097 CREATE TABLE distributors (
2098 did integer,
2099 name varchar(40),
2100 CONSTRAINT con1 CHECK (did &gt; 100 AND name &lt;&gt; '')
2101 );
2102 </programlisting>
2103 </para>
2104
2105 <para>
2106 Define a primary key table constraint for the table
2107 <structname>films</structname>:
2108
2109 <programlisting>
2110 CREATE TABLE films (
2111 code char(5),
2112 title varchar(40),
2113 did integer,
2114 date_prod date,
2115 kind varchar(10),
2116 len interval hour to minute,
2117 CONSTRAINT code_title PRIMARY KEY(code,title)
2118 );
2119 </programlisting>
2120 </para>
2121
2122 <para>
2123 Define a primary key constraint for table
2124 <structname>distributors</structname>. The following two examples are
2125 equivalent, the first using the table constraint syntax, the second
2126 the column constraint syntax:
2127
2128 <programlisting>
2129 CREATE TABLE distributors (
2130 did integer,
2131 name varchar(40),
2132 PRIMARY KEY(did)
2133 );
2134
2135 CREATE TABLE distributors (
2136 did integer PRIMARY KEY,
2137 name varchar(40)
2138 );
2139 </programlisting>
2140 </para>
2141
2142 <para>
2143 Assign a literal constant default value for the column
2144 <literal>name</literal>, arrange for the default value of column
2145 <literal>did</literal> to be generated by selecting the next value
2146 of a sequence object, and make the default value of
2147 <literal>modtime</literal> be the time at which the row is
2148 inserted:
2149
2150 <programlisting>
2151 CREATE TABLE distributors (
2152 name varchar(40) DEFAULT 'Luso Films',
2153 did integer DEFAULT nextval('distributors_serial'),
2154 modtime timestamp DEFAULT current_timestamp
2155 );
2156 </programlisting>
2157 </para>
2158
2159 <para>
2160 Define two <literal>NOT NULL</literal> column constraints on the table
2161 <classname>distributors</classname>, one of which is explicitly
2162 given a name:
2163
2164 <programlisting>
2165 CREATE TABLE distributors (
2166 did integer CONSTRAINT no_null NOT NULL,
2167 name varchar(40) NOT NULL
2168 );
2169 </programlisting>
2170 </para>
2171
2172 <para>
2173 Define a unique constraint for the <literal>name</literal> column:
2174
2175 <programlisting>
2176 CREATE TABLE distributors (
2177 did integer,
2178 name varchar(40) UNIQUE
2179 );
2180 </programlisting>
2181
2182 The same, specified as a table constraint:
2183
2184 <programlisting>
2185 CREATE TABLE distributors (
2186 did integer,
2187 name varchar(40),
2188 UNIQUE(name)
2189 );
2190 </programlisting>
2191 </para>
2192
2193 <para>
2194 Create the same table, specifying 70% fill factor for both the table
2195 and its unique index:
2196
2197 <programlisting>
2198 CREATE TABLE distributors (
2199 did integer,
2200 name varchar(40),
2201 UNIQUE(name) WITH (fillfactor=70)
2202 )
2203 WITH (fillfactor=70);
2204 </programlisting>
2205 </para>
2206
2207 <para>
2208 Create table <structname>circles</structname> with an exclusion
2209 constraint that prevents any two circles from overlapping:
2210
2211 <programlisting>
2212 CREATE TABLE circles (
2213 c circle,
2214 EXCLUDE USING gist (c WITH &amp;&amp;)
2215 );
2216 </programlisting>
2217 </para>
2218
2219 <para>
2220 Create table <structname>cinemas</structname> in tablespace <structname>diskvol1</structname>:
2221
2222 <programlisting>
2223 CREATE TABLE cinemas (
2224 id serial,
2225 name text,
2226 location text
2227 ) TABLESPACE diskvol1;
2228 </programlisting>
2229 </para>
2230
2231 <para>
2232 Create a composite type and a typed table:
2233 <programlisting>
2234 CREATE TYPE employee_type AS (name text, salary numeric);
2235
2236 CREATE TABLE employees OF employee_type (
2237 PRIMARY KEY (name),
2238 salary WITH OPTIONS DEFAULT 1000
2239 );
2240 </programlisting></para>
2241
2242 <para>
2243 Create a range partitioned table:
2244 <programlisting>
2245 CREATE TABLE measurement (
2246 logdate date not null,
2247 peaktemp int,
2248 unitsales int
2249 ) PARTITION BY RANGE (logdate);
2250 </programlisting></para>
2251
2252 <para>
2253 Create a range partitioned table with multiple columns in the partition key:
2254 <programlisting>
2255 CREATE TABLE measurement_year_month (
2256 logdate date not null,
2257 peaktemp int,
2258 unitsales int
2259 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
2260 </programlisting></para>
2261
2262 <para>
2263 Create a list partitioned table:
2264 <programlisting>
2265 CREATE TABLE cities (
2266 city_id bigserial not null,
2267 name text not null,
2268 population bigint
2269 ) PARTITION BY LIST (left(lower(name), 1));
2270 </programlisting></para>
2271
2272 <para>
2273 Create a hash partitioned table:
2274 <programlisting>
2275 CREATE TABLE orders (
2276 order_id bigint not null,
2277 cust_id bigint not null,
2278 status text
2279 ) PARTITION BY HASH (order_id);
2280 </programlisting></para>
2281
2282 <para>
2283 Create partition of a range partitioned table:
2284 <programlisting>
2285 CREATE TABLE measurement_y2016m07
2286 PARTITION OF measurement (
2287 unitsales DEFAULT 0
2288 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
2289 </programlisting></para>
2290
2291 <para>
2292 Create a few partitions of a range partitioned table with multiple
2293 columns in the partition key:
2294 <programlisting>
2295 CREATE TABLE measurement_ym_older
2296 PARTITION OF measurement_year_month
2297 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
2298
2299 CREATE TABLE measurement_ym_y2016m11
2300 PARTITION OF measurement_year_month
2301 FOR VALUES FROM (2016, 11) TO (2016, 12);
2302
2303 CREATE TABLE measurement_ym_y2016m12
2304 PARTITION OF measurement_year_month
2305 FOR VALUES FROM (2016, 12) TO (2017, 01);
2306
2307 CREATE TABLE measurement_ym_y2017m01
2308 PARTITION OF measurement_year_month
2309 FOR VALUES FROM (2017, 01) TO (2017, 02);
2310 </programlisting></para>
2311
2312 <para>
2313 Create partition of a list partitioned table:
2314 <programlisting>
2315 CREATE TABLE cities_ab
2316 PARTITION OF cities (
2317 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
2318 ) FOR VALUES IN ('a', 'b');
2319 </programlisting></para>
2320
2321 <para>
2322 Create partition of a list partitioned table that is itself further
2323 partitioned and then add a partition to it:
2324 <programlisting>
2325 CREATE TABLE cities_ab
2326 PARTITION OF cities (
2327 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
2328 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
2329
2330 CREATE TABLE cities_ab_10000_to_100000
2331 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
2332 </programlisting></para>
2333
2334 <para>
2335 Create partitions of a hash partitioned table:
2336 <programlisting>
2337 CREATE TABLE orders_p1 PARTITION OF orders
2338 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
2339 CREATE TABLE orders_p2 PARTITION OF orders
2340 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
2341 CREATE TABLE orders_p3 PARTITION OF orders
2342 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
2343 CREATE TABLE orders_p4 PARTITION OF orders
2344 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
2345 </programlisting></para>
2346
2347 <para>
2348 Create a default partition:
2349 <programlisting>
2350 CREATE TABLE cities_partdef
2351 PARTITION OF cities DEFAULT;
2352 </programlisting></para>
2353 </refsect1>
2354
2355 <refsect1 id="sql-createtable-compatibility" xreflabel="Compatibility">
2356 <title>Compatibility</title>
2357
2358 <para>
2359 The <command>CREATE TABLE</command> command conforms to the
2360 <acronym>SQL</acronym> standard, with exceptions listed below.
2361 </para>
2362
2363 <refsect2>
2364 <title>Temporary Tables</title>
2365
2366 <para>
2367 Although the syntax of <literal>CREATE TEMPORARY TABLE</literal>
2368 resembles that of the SQL standard, the effect is not the same. In the
2369 standard,
2370 temporary tables are defined just once and automatically exist (starting
2371 with empty contents) in every session that needs them.
2372 <productname>PostgreSQL</productname> instead
2373 requires each session to issue its own <literal>CREATE TEMPORARY
2374 TABLE</literal> command for each temporary table to be used. This allows
2375 different sessions to use the same temporary table name for different
2376 purposes, whereas the standard's approach constrains all instances of a
2377 given temporary table name to have the same table structure.
2378 </para>
2379
2380 <para>
2381 The standard's definition of the behavior of temporary tables is
2382 widely ignored. <productname>PostgreSQL</productname>'s behavior
2383 on this point is similar to that of several other SQL databases.
2384 </para>
2385
2386 <para>
2387 The SQL standard also distinguishes between global and local temporary
2388 tables, where a local temporary table has a separate set of contents for
2389 each SQL module within each session, though its definition is still shared
2390 across sessions. Since <productname>PostgreSQL</productname> does not
2391 support SQL modules, this distinction is not relevant in
2392 <productname>PostgreSQL</productname>.
2393 </para>
2394
2395 <para>
2396 For compatibility's sake, <productname>PostgreSQL</productname> will
2397 accept the <literal>GLOBAL</literal> and <literal>LOCAL</literal> keywords
2398 in a temporary table declaration, but they currently have no effect.
2399 Use of these keywords is discouraged, since future versions of
2400 <productname>PostgreSQL</productname> might adopt a more
2401 standard-compliant interpretation of their meaning.
2402 </para>
2403
2404 <para>
2405 The <literal>ON COMMIT</literal> clause for temporary tables
2406 also resembles the SQL standard, but has some differences.
2407 If the <literal>ON COMMIT</literal> clause is omitted, SQL specifies that the
2408 default behavior is <literal>ON COMMIT DELETE ROWS</literal>. However, the
2409 default behavior in <productname>PostgreSQL</productname> is
2410 <literal>ON COMMIT PRESERVE ROWS</literal>. The <literal>ON COMMIT
2411 DROP</literal> option does not exist in SQL.
2412 </para>
2413 </refsect2>
2414
2415 <refsect2>
2416 <title>Non-Deferred Uniqueness Constraints</title>
2417
2418 <para>
2419 When a <literal>UNIQUE</literal> or <literal>PRIMARY KEY</literal> constraint is
2420 not deferrable, <productname>PostgreSQL</productname> checks for
2421 uniqueness immediately whenever a row is inserted or modified.
2422 The SQL standard says that uniqueness should be enforced only at
2423 the end of the statement; this makes a difference when, for example,
2424 a single command updates multiple key values. To obtain
2425 standard-compliant behavior, declare the constraint as
2426 <literal>DEFERRABLE</literal> but not deferred (i.e., <literal>INITIALLY
2427 IMMEDIATE</literal>). Be aware that this can be significantly slower than
2428 immediate uniqueness checking.
2429 </para>
2430 </refsect2>
2431
2432 <refsect2>
2433 <title>Column Check Constraints</title>
2434
2435 <para>
2436 The SQL standard says that <literal>CHECK</literal> column constraints
2437 can only refer to the column they apply to; only <literal>CHECK</literal>
2438 table constraints can refer to multiple columns.
2439 <productname>PostgreSQL</productname> does not enforce this
2440 restriction; it treats column and table check constraints alike.
2441 </para>
2442 </refsect2>
2443
2444 <refsect2>
2445 <title><literal>EXCLUDE</literal> Constraint</title>
2446
2447 <para>
2448 The <literal>EXCLUDE</literal> constraint type is a
2449 <productname>PostgreSQL</productname> extension.
2450 </para>
2451 </refsect2>
2452
2453 <refsect2>
2454 <title>Foreign Key Constraints</title>
2455
2456 <para>
2457 The ability to specify column lists in the foreign key actions
2458 <literal>SET DEFAULT</literal> and <literal>SET NULL</literal> is a
2459 <productname>PostgreSQL</productname> extension.
2460 </para>
2461
2462 <para>
2463 It is a <productname>PostgreSQL</productname> extension that a
2464 foreign key constraint may reference columns of a unique index instead of
2465 columns of a primary key or unique constraint.
2466 </para>
2467 </refsect2>
2468
2469 <refsect2>
2470 <title><literal>NULL</literal> <quote>Constraint</quote></title>
2471
2472 <para>
2473 The <literal>NULL</literal> <quote>constraint</quote> (actually a
2474 non-constraint) is a <productname>PostgreSQL</productname>
2475 extension to the SQL standard that is included for compatibility with some
2476 other database systems (and for symmetry with the <literal>NOT
2477 NULL</literal> constraint). Since it is the default for any
2478 column, its presence is simply noise.
2479 </para>
2480 </refsect2>
2481
2482 <refsect2>
2483 <title>Constraint Naming</title>
2484
2485 <para>
2486 The SQL standard says that table and domain constraints must have names
2487 that are unique across the schema containing the table or domain.
2488 <productname>PostgreSQL</productname> is laxer: it only requires
2489 constraint names to be unique across the constraints attached to a
2490 particular table or domain. However, this extra freedom does not exist
2491 for index-based constraints (<literal>UNIQUE</literal>,
2492 <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal>
2493 constraints), because the associated index is named the same as the
2494 constraint, and index names must be unique across all relations within
2495 the same schema.
2496 </para>
2497 </refsect2>
2498
2499 <refsect2>
2500 <title>Inheritance</title>
2501
2502 <para>
2503 Multiple inheritance via the <literal>INHERITS</literal> clause is
2504 a <productname>PostgreSQL</productname> language extension.
2505 SQL:1999 and later define single inheritance using a
2506 different syntax and different semantics. SQL:1999-style
2507 inheritance is not yet supported by
2508 <productname>PostgreSQL</productname>.
2509 </para>
2510 </refsect2>
2511
2512 <refsect2>
2513 <title>Zero-Column Tables</title>
2514
2515 <para>
2516 <productname>PostgreSQL</productname> allows a table of no columns
2517 to be created (for example, <literal>CREATE TABLE foo();</literal>). This
2518 is an extension from the SQL standard, which does not allow zero-column
2519 tables. Zero-column tables are not in themselves very useful, but
2520 disallowing them creates odd special cases for <command>ALTER TABLE
2521 DROP COLUMN</command>, so it seems cleaner to ignore this spec restriction.
2522 </para>
2523 </refsect2>
2524
2525 <refsect2>
2526 <title>Multiple Identity Columns</title>
2527
2528 <para>
2529 <productname>PostgreSQL</productname> allows a table to have more than one
2530 identity column. The standard specifies that a table can have at most one
2531 identity column. This is relaxed mainly to give more flexibility for
2532 doing schema changes or migrations. Note that
2533 the <command>INSERT</command> command supports only one override clause
2534 that applies to the entire statement, so having multiple identity columns
2535 with different behaviors is not well supported.
2536 </para>
2537 </refsect2>
2538
2539 <refsect2>
2540 <title>Generated Columns</title>
2541
2542 <para>
2543 The options <literal>STORED</literal> and <literal>VIRTUAL</literal> are
2544 not standard but are also used by other SQL implementations. The SQL
2545 standard does not specify the storage of generated columns.
2546 </para>
2547 </refsect2>
2548
2549 <refsect2>
2550 <title><literal>LIKE</literal> Clause</title>
2551
2552 <para>
2553 While a <literal>LIKE</literal> clause exists in the SQL standard, many of the
2554 options that <productname>PostgreSQL</productname> accepts for it are not
2555 in the standard, and some of the standard's options are not implemented
2556 by <productname>PostgreSQL</productname>.
2557 </para>
2558 </refsect2>
2559
2560 <refsect2>
2561 <title><literal>WITH</literal> Clause</title>
2562
2563 <para>
2564 The <literal>WITH</literal> clause is a <productname>PostgreSQL</productname>
2565 extension; storage parameters are not in the standard.
2566 </para>
2567 </refsect2>
2568
2569 <refsect2>
2570 <title>Tablespaces</title>
2571
2572 <para>
2573 The <productname>PostgreSQL</productname> concept of tablespaces is not
2574 part of the standard. Hence, the clauses <literal>TABLESPACE</literal>
2575 and <literal>USING INDEX TABLESPACE</literal> are extensions.
2576 </para>
2577 </refsect2>
2578
2579 <refsect2>
2580 <title>Typed Tables</title>
2581
2582 <para>
2583 Typed tables implement a subset of the SQL standard. According to
2584 the standard, a typed table has columns corresponding to the
2585 underlying composite type as well as one other column that is
2586 the <quote>self-referencing column</quote>.
2587 <productname>PostgreSQL</productname> does not support self-referencing
2588 columns explicitly.
2589 </para>
2590 </refsect2>
2591
2592 <refsect2>
2593 <title><literal>PARTITION BY</literal> Clause</title>
2594
2595 <para>
2596 The <literal>PARTITION BY</literal> clause is a
2597 <productname>PostgreSQL</productname> extension.
2598 </para>
2599 </refsect2>
2600
2601 <refsect2>
2602 <title><literal>PARTITION OF</literal> Clause</title>
2603
2604 <para>
2605 The <literal>PARTITION OF</literal> clause is a
2606 <productname>PostgreSQL</productname> extension.
2607 </para>
2608 </refsect2>
2609
2610 </refsect1>
2611
2612
2613 <refsect1>
2614 <title>See Also</title>
2615
2616 <simplelist type="inline">
2617 <member><xref linkend="sql-altertable"/></member>
2618 <member><xref linkend="sql-droptable"/></member>
2619 <member><xref linkend="sql-createtableas"/></member>
2620 <member><xref linkend="sql-createtablespace"/></member>
2621 <member><xref linkend="sql-createtype"/></member>
2622 </simplelist>
2623 </refsect1>
2624 </refentry>