codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE TABLE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION" /><link rel="next" href="sql-createtableas.html" title="CREATE TABLE AS" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE TABLE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createtableas.html" title="CREATE TABLE AS">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATETABLE"><div class="titlepage"></div><a id="id-1.9.3.85.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE TABLE</span></h2><p>CREATE TABLE — define a new table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <em class="replaceable"><code>table_name</code></em> ( [4 { <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION <em class="replaceable"><code>compression_method</code></em> ] [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]5 | <em class="replaceable"><code>table_constraint</code></em>6 | LIKE <em class="replaceable"><code>source_table</code></em> [ <em class="replaceable"><code>like_option</code></em> ... ] }7 [, ... ]8] )9[ INHERITS ( <em class="replaceable"><code>parent_table</code></em> [, ... ] ) ]10[ PARTITION BY { RANGE | LIST | HASH } ( { <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>opclass</code></em> ] [, ... ] ) ]11[ USING <em class="replaceable"><code>method</code></em> ]12[ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) | WITHOUT OIDS ]13[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]14[ TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ]15 16CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <em class="replaceable"><code>table_name</code></em>17 OF <em class="replaceable"><code>type_name</code></em> [ (18 { <em class="replaceable"><code>column_name</code></em> [ WITH OPTIONS ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]19 | <em class="replaceable"><code>table_constraint</code></em> }20 [, ... ]21) ]22[ PARTITION BY { RANGE | LIST | HASH } ( { <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>opclass</code></em> ] [, ... ] ) ]23[ USING <em class="replaceable"><code>method</code></em> ]24[ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) | WITHOUT OIDS ]25[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]26[ TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ]27 28CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <em class="replaceable"><code>table_name</code></em>29 PARTITION OF <em class="replaceable"><code>parent_table</code></em> [ (30 { <em class="replaceable"><code>column_name</code></em> [ WITH OPTIONS ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]31 | <em class="replaceable"><code>table_constraint</code></em> }32 [, ... ]33) ] { FOR VALUES <em class="replaceable"><code>partition_bound_spec</code></em> | DEFAULT }34[ PARTITION BY { RANGE | LIST | HASH } ( { <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>opclass</code></em> ] [, ... ] ) ]35[ USING <em class="replaceable"><code>method</code></em> ]36[ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) | WITHOUT OIDS ]37[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]38[ TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ]39 40<span class="phrase">where <em class="replaceable"><code>column_constraint</code></em> is:</span>41 42[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]43{ NOT NULL |44 NULL |45 CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] |46 DEFAULT <em class="replaceable"><code>default_expr</code></em> |47 GENERATED ALWAYS AS ( <em class="replaceable"><code>generation_expr</code></em> ) STORED |48 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <em class="replaceable"><code>sequence_options</code></em> ) ] |49 UNIQUE [ NULLS [ NOT ] DISTINCT ] <em class="replaceable"><code>index_parameters</code></em> |50 PRIMARY KEY <em class="replaceable"><code>index_parameters</code></em> |51 REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]52 [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ] [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ] }53[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]54 55<span class="phrase">and <em class="replaceable"><code>table_constraint</code></em> is:</span>56 57[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]58{ CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] |59 UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> |60 PRIMARY KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> |61 EXCLUDE [ USING <em class="replaceable"><code>index_method</code></em> ] ( <em class="replaceable"><code>exclude_element</code></em> WITH <em class="replaceable"><code>operator</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> [ WHERE ( <em class="replaceable"><code>predicate</code></em> ) ] |62 FOREIGN KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] ) ]63 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ] [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ] }64[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]65 66<span class="phrase">and <em class="replaceable"><code>like_option</code></em> is:</span>67 68{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }69 70<span class="phrase">and <em class="replaceable"><code>partition_bound_spec</code></em> is:</span>71 72IN ( <em class="replaceable"><code>partition_bound_expr</code></em> [, ...] ) |73FROM ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] )74 TO ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] ) |75WITH ( MODULUS <em class="replaceable"><code>numeric_literal</code></em>, REMAINDER <em class="replaceable"><code>numeric_literal</code></em> )76 77<span class="phrase"><em class="replaceable"><code>index_parameters</code></em> in <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>, and <code class="literal">EXCLUDE</code> constraints are:</span>78 79[ INCLUDE ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ]80[ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]81[ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ]82 83<span class="phrase"><em class="replaceable"><code>exclude_element</code></em> in an <code class="literal">EXCLUDE</code> constraint is:</span>84 85{ <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>opclass</code></em> [ ( <em class="replaceable"><code>opclass_parameter</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]86 87<span class="phrase"><em class="replaceable"><code>referential_action</code></em> in a <code class="literal">FOREIGN KEY</code>/<code class="literal">REFERENCES</code> constraint is:</span>88 89{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] | SET DEFAULT [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] }90</pre></div><div class="refsect1" id="SQL-CREATETABLE-DESCRIPTION"><h2>Description</h2><p>91 <code class="command">CREATE TABLE</code> will create a new, initially empty table92 in the current database. The table will be owned by the user issuing the93 command.94 </p><p>95 If a schema name is given (for example, <code class="literal">CREATE TABLE96 myschema.mytable ...</code>) then the table is created in the specified97 schema. Otherwise it is created in the current schema. Temporary98 tables exist in a special schema, so a schema name cannot be given99 when creating a temporary table. The name of the table must be100 distinct from the name of any other relation (table, sequence, index, view,101 materialized view, or foreign table) in the same schema.102 </p><p>103 <code class="command">CREATE TABLE</code> also automatically creates a data104 type that represents the composite type corresponding105 to one row of the table. Therefore, tables cannot have the same106 name as any existing data type in the same schema.107 </p><p>108 The optional constraint clauses specify constraints (tests) that109 new or updated rows must satisfy for an insert or update operation110 to succeed. A constraint is an SQL object that helps define the111 set of valid values in the table in various ways.112 </p><p>113 There are two ways to define constraints: table constraints and114 column constraints. A column constraint is defined as part of a115 column definition. A table constraint definition is not tied to a116 particular column, and it can encompass more than one column.117 Every column constraint can also be written as a table constraint;118 a column constraint is only a notational convenience for use when the119 constraint only affects one column.120 </p><p>121 To be able to create a table, you must have <code class="literal">USAGE</code>122 privilege on all column types or the type in the <code class="literal">OF</code>123 clause, respectively.124 </p></div><div class="refsect1" id="id-1.9.3.85.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATETABLE-TEMPORARY"><span class="term"><code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code></span> <a href="#SQL-CREATETABLE-TEMPORARY" class="id_link">#</a></dt><dd><p>125 If specified, the table is created as a temporary table.126 Temporary tables are automatically dropped at the end of a127 session, or optionally at the end of the current transaction128 (see <code class="literal">ON COMMIT</code> below). The default129 search_path includes the temporary schema first and so identically130 named existing permanent tables are not chosen for new plans131 while the temporary table exists, unless they are referenced132 with schema-qualified names. Any indexes created on a temporary133 table are automatically temporary as well.134 </p><p>135 The <a class="link" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">autovacuum daemon</a> cannot136 access and therefore cannot vacuum or analyze temporary tables.137 For this reason, appropriate vacuum and analyze operations should be138 performed via session SQL commands. For example, if a temporary139 table is going to be used in complex queries, it is wise to run140 <code class="command">ANALYZE</code> on the temporary table after it is populated.141 </p><p>142 Optionally, <code class="literal">GLOBAL</code> or <code class="literal">LOCAL</code>143 can be written before <code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code>.144 This presently makes no difference in <span class="productname">PostgreSQL</span>145 and is deprecated; see146 <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY" title="Compatibility">Compatibility</a> below.147 </p></dd><dt id="SQL-CREATETABLE-UNLOGGED"><span class="term"><code class="literal">UNLOGGED</code></span> <a href="#SQL-CREATETABLE-UNLOGGED" class="id_link">#</a></dt><dd><p>148 If specified, the table is created as an unlogged table. Data written149 to unlogged tables is not written to the write-ahead log (see <a class="xref" href="wal.html" title="Chapter 30. Reliability and the Write-Ahead Log">Chapter 30</a>), which makes them considerably faster than ordinary150 tables. However, they are not crash-safe: an unlogged table is151 automatically truncated after a crash or unclean shutdown. The contents152 of an unlogged table are also not replicated to standby servers.153 Any indexes created on an unlogged table are automatically unlogged as154 well.155 </p><p>156 If this is specified, any sequences created together with the unlogged157 table (for identity or serial columns) are also created as unlogged.158 </p></dd><dt id="SQL-CREATETABLE-PARMS-IF-NOT-EXISTS"><span class="term"><code class="literal">IF NOT EXISTS</code></span> <a href="#SQL-CREATETABLE-PARMS-IF-NOT-EXISTS" class="id_link">#</a></dt><dd><p>159 Do not throw an error if a relation with the same name already exists.160 A notice is issued in this case. Note that there is no guarantee that161 the existing relation is anything like the one that would have been162 created.163 </p></dd><dt id="SQL-CREATETABLE-PARMS-TABLE-NAME"><span class="term"><em class="replaceable"><code>table_name</code></em></span> <a href="#SQL-CREATETABLE-PARMS-TABLE-NAME" class="id_link">#</a></dt><dd><p>164 The name (optionally schema-qualified) of the table to be created.165 </p></dd><dt id="SQL-CREATETABLE-PARMS-TYPE-NAME"><span class="term"><code class="literal">OF <em class="replaceable"><code>type_name</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-TYPE-NAME" class="id_link">#</a></dt><dd><p>166 Creates a <em class="firstterm">typed table</em>, which takes its167 structure from the specified composite type (name optionally168 schema-qualified). A typed table is tied to its type; for169 example the table will be dropped if the type is dropped170 (with <code class="literal">DROP TYPE ... CASCADE</code>).171 </p><p>172 When a typed table is created, then the data types of the173 columns are determined by the underlying composite type and are174 not specified by the <code class="literal">CREATE TABLE</code> command.175 But the <code class="literal">CREATE TABLE</code> command can add defaults176 and constraints to the table and can specify storage parameters.177 </p></dd><dt id="SQL-CREATETABLE-PARMS-COLUMN-NAME"><span class="term"><em class="replaceable"><code>column_name</code></em></span> <a href="#SQL-CREATETABLE-PARMS-COLUMN-NAME" class="id_link">#</a></dt><dd><p>178 The name of a column to be created in the new table.179 </p></dd><dt id="SQL-CREATETABLE-PARMS-DATA-TYPE"><span class="term"><em class="replaceable"><code>data_type</code></em></span> <a href="#SQL-CREATETABLE-PARMS-DATA-TYPE" class="id_link">#</a></dt><dd><p>180 The data type of the column. This can include array181 specifiers. For more information on the data types supported by182 <span class="productname">PostgreSQL</span>, refer to <a class="xref" href="datatype.html" title="Chapter 8. Data Types">Chapter 8</a>.183 </p></dd><dt id="SQL-CREATETABLE-PARMS-COLLATE"><span class="term"><code class="literal">COLLATE <em class="replaceable"><code>collation</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-COLLATE" class="id_link">#</a></dt><dd><p>184 The <code class="literal">COLLATE</code> clause assigns a collation to185 the column (which must be of a collatable data type).186 If not specified, the column data type's default collation is used.187 </p></dd><dt id="SQL-CREATETABLE-PARMS-STORAGE"><span class="term">188 <code class="literal">STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }</code>189 <a id="id-1.9.3.85.6.2.9.1.2" class="indexterm"></a>190 </span> <a href="#SQL-CREATETABLE-PARMS-STORAGE" class="id_link">#</a></dt><dd><p>191 This form sets the storage mode for the column. This controls whether this192 column is held inline or in a secondary <acronym class="acronym">TOAST</acronym> table,193 and whether the data should be compressed or not. <code class="literal">PLAIN</code>194 must be used for fixed-length values such as <code class="type">integer</code> and is195 inline, uncompressed. <code class="literal">MAIN</code> is for inline, compressible196 data. <code class="literal">EXTERNAL</code> is for external, uncompressed data, and197 <code class="literal">EXTENDED</code> is for external, compressed data.198 Writing <code class="literal">DEFAULT</code> sets the storage mode to the default199 mode for the column's data type. <code class="literal">EXTENDED</code> is the200 default for most data types that support non-<code class="literal">PLAIN</code>201 storage.202 Use of <code class="literal">EXTERNAL</code> will make substring operations on203 very large <code class="type">text</code> and <code class="type">bytea</code> values run faster,204 at the penalty of increased storage space.205 See <a class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a> for more information.206 </p></dd><dt id="SQL-CREATETABLE-PARMS-COMPRESSION"><span class="term"><code class="literal">COMPRESSION <em class="replaceable"><code>compression_method</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-COMPRESSION" class="id_link">#</a></dt><dd><p>207 The <code class="literal">COMPRESSION</code> clause sets the compression method208 for the column. Compression is supported only for variable-width data209 types, and is used only when the column's storage mode210 is <code class="literal">main</code> or <code class="literal">extended</code>.211 (See <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> for information on212 column storage modes.) Setting this property for a partitioned table213 has no direct effect, because such tables have no storage of their own,214 but the configured value will be inherited by newly-created partitions.215 The supported compression methods are <code class="literal">pglz</code> and216 <code class="literal">lz4</code>. (<code class="literal">lz4</code> is available only if217 <code class="option">--with-lz4</code> was used when building218 <span class="productname">PostgreSQL</span>.) In addition,219 <em class="replaceable"><code>compression_method</code></em>220 can be <code class="literal">default</code> to explicitly specify the default221 behavior, which is to consult the222 <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION">default_toast_compression</a> setting at the time of223 data insertion to determine the method to use.224 </p></dd><dt id="SQL-CREATETABLE-PARMS-INHERITS"><span class="term"><code class="literal">INHERITS ( <em class="replaceable"><code>parent_table</code></em> [, ... ] )</code></span> <a href="#SQL-CREATETABLE-PARMS-INHERITS" class="id_link">#</a></dt><dd><p>225 The optional <code class="literal">INHERITS</code> clause specifies a list of226 tables from which the new table automatically inherits all227 columns. Parent tables can be plain tables or foreign tables.228 </p><p>229 Use of <code class="literal">INHERITS</code> creates a persistent relationship230 between the new child table and its parent table(s). Schema231 modifications to the parent(s) normally propagate to children232 as well, and by default the data of the child table is included in233 scans of the parent(s).234 </p><p>235 If the same column name exists in more than one parent236 table, an error is reported unless the data types of the columns237 match in each of the parent tables. If there is no conflict,238 then the duplicate columns are merged to form a single column in239 the new table. If the column name list of the new table240 contains a column name that is also inherited, the data type must241 likewise match the inherited column(s), and the column242 definitions are merged into one. If the243 new table explicitly specifies a default value for the column,244 this default overrides any defaults from inherited declarations245 of the column. Otherwise, any parents that specify default246 values for the column must all specify the same default, or an247 error will be reported.248 </p><p>249 <code class="literal">CHECK</code> constraints are merged in essentially the same way as250 columns: if multiple parent tables and/or the new table definition251 contain identically-named <code class="literal">CHECK</code> constraints, these252 constraints must all have the same check expression, or an error will be253 reported. Constraints having the same name and expression will254 be merged into one copy. A constraint marked <code class="literal">NO INHERIT</code> in a255 parent will not be considered. Notice that an unnamed <code class="literal">CHECK</code>256 constraint in the new table will never be merged, since a unique name257 will always be chosen for it.258 </p><p>259 Column <code class="literal">STORAGE</code> settings are also copied from parent tables.260 </p><p>261 If a column in the parent table is an identity column, that property is262 not inherited. A column in the child table can be declared identity263 column if desired.264 </p></dd><dt id="SQL-CREATETABLE-PARMS-PARTITION-BY"><span class="term"><code class="literal">PARTITION BY { RANGE | LIST | HASH } ( { <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ <em class="replaceable"><code>opclass</code></em> ] [, ...] ) </code></span> <a href="#SQL-CREATETABLE-PARMS-PARTITION-BY" class="id_link">#</a></dt><dd><p>265 The optional <code class="literal">PARTITION BY</code> clause specifies a strategy266 of partitioning the table. The table thus created is called a267 <em class="firstterm">partitioned</em> table. The parenthesized list of268 columns or expressions forms the <em class="firstterm">partition key</em>269 for the table. When using range or hash partitioning, the partition key270 can include multiple columns or expressions (up to 32, but this limit can271 be altered when building <span class="productname">PostgreSQL</span>), but for272 list partitioning, the partition key must consist of a single column or273 expression.274 </p><p>275 Range and list partitioning require a btree operator class, while hash276 partitioning requires a hash operator class. If no operator class is277 specified explicitly, the default operator class of the appropriate278 type will be used; if no default operator class exists, an error will279 be raised. When hash partitioning is used, the operator class used280 must implement support function 2 (see <a class="xref" href="xindex.html#XINDEX-SUPPORT" title="38.16.3. Index Method Support Routines">Section 38.16.3</a>281 for details).282 </p><p>283 A partitioned table is divided into sub-tables (called partitions),284 which are created using separate <code class="literal">CREATE TABLE</code> commands.285 The partitioned table is itself empty. A data row inserted into the286 table is routed to a partition based on the value of columns or287 expressions in the partition key. If no existing partition matches288 the values in the new row, an error will be reported.289 </p><p>290 Partitioned tables do not support <code class="literal">EXCLUDE</code> constraints;291 however, you can define these constraints on individual partitions.292 </p><p>293 See <a class="xref" href="ddl-partitioning.html" title="5.11. Table Partitioning">Section 5.11</a> for more discussion on table294 partitioning.295 </p></dd><dt id="SQL-CREATETABLE-PARTITION"><span class="term"><code class="literal">PARTITION OF <em class="replaceable"><code>parent_table</code></em> { FOR VALUES <em class="replaceable"><code>partition_bound_spec</code></em> | DEFAULT }</code></span> <a href="#SQL-CREATETABLE-PARTITION" class="id_link">#</a></dt><dd><p>296 Creates the table as a <em class="firstterm">partition</em> of the specified297 parent table. The table can be created either as a partition for specific298 values using <code class="literal">FOR VALUES</code> or as a default partition299 using <code class="literal">DEFAULT</code>. Any indexes, constraints and300 user-defined row-level triggers that exist in the parent table are cloned301 on the new partition.302 </p><p>303 The <em class="replaceable"><code>partition_bound_spec</code></em>304 must correspond to the partitioning method and partition key of the305 parent table, and must not overlap with any existing partition of that306 parent. The form with <code class="literal">IN</code> is used for list partitioning,307 the form with <code class="literal">FROM</code> and <code class="literal">TO</code> is used308 for range partitioning, and the form with <code class="literal">WITH</code> is used309 for hash partitioning.310 </p><p>311 <em class="replaceable"><code>partition_bound_expr</code></em> is312 any variable-free expression (subqueries, window functions, aggregate313 functions, and set-returning functions are not allowed). Its data type314 must match the data type of the corresponding partition key column.315 The expression is evaluated once at table creation time, so it can316 even contain volatile expressions such as317 <code class="literal"><code class="function">CURRENT_TIMESTAMP</code></code>.318 </p><p>319 When creating a list partition, <code class="literal">NULL</code> can be320 specified to signify that the partition allows the partition key321 column to be null. However, there cannot be more than one such322 list partition for a given parent table. <code class="literal">NULL</code>323 cannot be specified for range partitions.324 </p><p>325 When creating a range partition, the lower bound specified with326 <code class="literal">FROM</code> is an inclusive bound, whereas the upper327 bound specified with <code class="literal">TO</code> is an exclusive bound.328 That is, the values specified in the <code class="literal">FROM</code> list329 are valid values of the corresponding partition key columns for this330 partition, whereas those in the <code class="literal">TO</code> list are331 not. Note that this statement must be understood according to the332 rules of row-wise comparison (<a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.24.5. Row Constructor Comparison">Section 9.24.5</a>).333 For example, given <code class="literal">PARTITION BY RANGE (x,y)</code>, a partition334 bound <code class="literal">FROM (1, 2) TO (3, 4)</code>335 allows <code class="literal">x=1</code> with any <code class="literal">y>=2</code>,336 <code class="literal">x=2</code> with any non-null <code class="literal">y</code>,337 and <code class="literal">x=3</code> with any <code class="literal">y<4</code>.338 </p><p>339 The special values <code class="literal">MINVALUE</code> and <code class="literal">MAXVALUE</code>340 may be used when creating a range partition to indicate that there341 is no lower or upper bound on the column's value. For example, a342 partition defined using <code class="literal">FROM (MINVALUE) TO (10)</code> allows343 any values less than 10, and a partition defined using344 <code class="literal">FROM (10) TO (MAXVALUE)</code> allows any values greater than345 or equal to 10.346 </p><p>347 When creating a range partition involving more than one column, it348 can also make sense to use <code class="literal">MAXVALUE</code> as part of the lower349 bound, and <code class="literal">MINVALUE</code> as part of the upper bound. For350 example, a partition defined using351 <code class="literal">FROM (0, MAXVALUE) TO (10, MAXVALUE)</code> allows any rows352 where the first partition key column is greater than 0 and less than353 or equal to 10. Similarly, a partition defined using354 <code class="literal">FROM ('a', MINVALUE) TO ('b', MINVALUE)</code> allows any rows355 where the first partition key column starts with "a".356 </p><p>357 Note that if <code class="literal">MINVALUE</code> or <code class="literal">MAXVALUE</code> is used for358 one column of a partitioning bound, the same value must be used for all359 subsequent columns. For example, <code class="literal">(10, MINVALUE, 0)</code> is not360 a valid bound; you should write <code class="literal">(10, MINVALUE, MINVALUE)</code>.361 </p><p>362 Also note that some element types, such as <code class="literal">timestamp</code>,363 have a notion of "infinity", which is just another value that can364 be stored. This is different from <code class="literal">MINVALUE</code> and365 <code class="literal">MAXVALUE</code>, which are not real values that can be stored,366 but rather they are ways of saying that the value is unbounded.367 <code class="literal">MAXVALUE</code> can be thought of as being greater than any368 other value, including "infinity" and <code class="literal">MINVALUE</code> as being369 less than any other value, including "minus infinity". Thus the range370 <code class="literal">FROM ('infinity') TO (MAXVALUE)</code> is not an empty range; it371 allows precisely one value to be stored — "infinity".372 </p><p>373 If <code class="literal">DEFAULT</code> is specified, the table will be374 created as the default partition of the parent table. This option375 is not available for hash-partitioned tables. A partition key value376 not fitting into any other partition of the given parent will be377 routed to the default partition.378 </p><p>379 When a table has an existing <code class="literal">DEFAULT</code> partition and380 a new partition is added to it, the default partition must381 be scanned to verify that it does not contain any rows which properly382 belong in the new partition. If the default partition contains a383 large number of rows, this may be slow. The scan will be skipped if384 the default partition is a foreign table or if it has a constraint which385 proves that it cannot contain rows which should be placed in the new386 partition.387 </p><p>388 When creating a hash partition, a modulus and remainder must be specified.389 The modulus must be a positive integer, and the remainder must be a390 non-negative integer less than the modulus. Typically, when initially391 setting up a hash-partitioned table, you should choose a modulus equal to392 the number of partitions and assign every table the same modulus and a393 different remainder (see examples, below). However, it is not required394 that every partition have the same modulus, only that every modulus which395 occurs among the partitions of a hash-partitioned table is a factor of the396 next larger modulus. This allows the number of partitions to be increased397 incrementally without needing to move all the data at once. For example,398 suppose you have a hash-partitioned table with 8 partitions, each of which399 has modulus 8, but find it necessary to increase the number of partitions400 to 16. You can detach one of the modulus-8 partitions, create two new401 modulus-16 partitions covering the same portion of the key space (one with402 a remainder equal to the remainder of the detached partition, and the403 other with a remainder equal to that value plus 8), and repopulate them404 with data. You can then repeat this -- perhaps at a later time -- for405 each modulus-8 partition until none remain. While this may still involve406 a large amount of data movement at each step, it is still better than407 having to create a whole new table and move all the data at once.408 </p><p>409 A partition must have the same column names and types as the partitioned410 table to which it belongs. Modifications to the column names or types of411 a partitioned table will automatically propagate to all partitions.412 <code class="literal">CHECK</code> constraints will be inherited automatically by413 every partition, but an individual partition may specify additional414 <code class="literal">CHECK</code> constraints; additional constraints with the415 same name and condition as in the parent will be merged with the parent416 constraint. Defaults may be specified separately for each partition.417 But note that a partition's default value is not applied when inserting418 a tuple through a partitioned table.419 </p><p>420 Rows inserted into a partitioned table will be automatically routed to421 the correct partition. If no suitable partition exists, an error will422 occur.423 </p><p>424 Operations such as <code class="command">TRUNCATE</code>425 which normally affect a table and all of its426 inheritance children will cascade to all partitions, but may also be427 performed on an individual partition.428 </p><p>429 Note that creating a partition using <code class="literal">PARTITION OF</code>430 requires taking an <code class="literal">ACCESS EXCLUSIVE</code> lock on the431 parent partitioned table. Likewise, dropping a partition432 with <code class="command">DROP TABLE</code> requires taking433 an <code class="literal">ACCESS EXCLUSIVE</code> lock on the parent table.434 It is possible to use <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER435 TABLE ATTACH/DETACH PARTITION</code></a> to perform these436 operations with a weaker lock, thus reducing interference with437 concurrent operations on the partitioned table.438 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE"><span class="term"><code class="literal">LIKE <em class="replaceable"><code>source_table</code></em> [ <em class="replaceable"><code>like_option</code></em> ... ]</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE" class="id_link">#</a></dt><dd><p>439 The <code class="literal">LIKE</code> clause specifies a table from which440 the new table automatically copies all column names, their data types,441 and their not-null constraints.442 </p><p>443 Unlike <code class="literal">INHERITS</code>, the new table and original table444 are completely decoupled after creation is complete. Changes to the445 original table will not be applied to the new table, and it is not446 possible to include data of the new table in scans of the original447 table.448 </p><p>449 Also unlike <code class="literal">INHERITS</code>, columns and450 constraints copied by <code class="literal">LIKE</code> are not merged with similarly451 named columns and constraints.452 If the same name is specified explicitly or in another453 <code class="literal">LIKE</code> clause, an error is signaled.454 </p><p>455 The optional <em class="replaceable"><code>like_option</code></em> clauses specify456 which additional properties of the original table to copy. Specifying457 <code class="literal">INCLUDING</code> copies the property, specifying458 <code class="literal">EXCLUDING</code> omits the property.459 <code class="literal">EXCLUDING</code> is the default. If multiple specifications460 are made for the same kind of object, the last one is used. The461 available options are:462 463 </p><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-COMMENTS"><span class="term"><code class="literal">INCLUDING COMMENTS</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-COMMENTS" class="id_link">#</a></dt><dd><p>464 Comments for the copied columns, constraints, and indexes will be465 copied. The default behavior is to exclude comments, resulting in466 the copied columns and constraints in the new table having no467 comments.468 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-COMPRESSION"><span class="term"><code class="literal">INCLUDING COMPRESSION</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-COMPRESSION" class="id_link">#</a></dt><dd><p>469 Compression method of the columns will be copied. The default470 behavior is to exclude compression methods, resulting in columns471 having the default compression method.472 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-CONSTRAINTS"><span class="term"><code class="literal">INCLUDING CONSTRAINTS</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-CONSTRAINTS" class="id_link">#</a></dt><dd><p>473 <code class="literal">CHECK</code> constraints will be copied. No distinction474 is made between column constraints and table constraints. Not-null475 constraints are always copied to the new table.476 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-DEFAULTS"><span class="term"><code class="literal">INCLUDING DEFAULTS</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-DEFAULTS" class="id_link">#</a></dt><dd><p>477 Default expressions for the copied column definitions will be478 copied. Otherwise, default expressions are not copied, resulting in479 the copied columns in the new table having null defaults. Note that480 copying defaults that call database-modification functions, such as481 <code class="function">nextval</code>, may create a functional linkage482 between the original and new tables.483 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-GENERATED"><span class="term"><code class="literal">INCLUDING GENERATED</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-GENERATED" class="id_link">#</a></dt><dd><p>484 Any generation expressions of copied column definitions will be485 copied. By default, new columns will be regular base columns.486 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-IDENTITY"><span class="term"><code class="literal">INCLUDING IDENTITY</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-IDENTITY" class="id_link">#</a></dt><dd><p>487 Any identity specifications of copied column definitions will be488 copied. A new sequence is created for each identity column of the489 new table, separate from the sequences associated with the old490 table.491 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-INDEXES"><span class="term"><code class="literal">INCLUDING INDEXES</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-INDEXES" class="id_link">#</a></dt><dd><p>492 Indexes, <code class="literal">PRIMARY KEY</code>, <code class="literal">UNIQUE</code>,493 and <code class="literal">EXCLUDE</code> constraints on the original table494 will be created on the new table. Names for the new indexes and495 constraints are chosen according to the default rules, regardless of496 how the originals were named. (This behavior avoids possible497 duplicate-name failures for the new indexes.)498 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-STATISTICS"><span class="term"><code class="literal">INCLUDING STATISTICS</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-STATISTICS" class="id_link">#</a></dt><dd><p>499 Extended statistics are copied to the new table.500 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-STORAGE"><span class="term"><code class="literal">INCLUDING STORAGE</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-STORAGE" class="id_link">#</a></dt><dd><p>501 <code class="literal">STORAGE</code> settings for the copied column502 definitions will be copied. The default behavior is to exclude503 <code class="literal">STORAGE</code> settings, resulting in the copied columns504 in the new table having type-specific default settings. For more on505 <code class="literal">STORAGE</code> settings, see <a class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a>.506 </p></dd><dt id="SQL-CREATETABLE-PARMS-LIKE-OPT-ALL"><span class="term"><code class="literal">INCLUDING ALL</code></span> <a href="#SQL-CREATETABLE-PARMS-LIKE-OPT-ALL" class="id_link">#</a></dt><dd><p>507 <code class="literal">INCLUDING ALL</code> is an abbreviated form selecting508 all the available individual options. (It could be useful to write509 individual <code class="literal">EXCLUDING</code> clauses after510 <code class="literal">INCLUDING ALL</code> to select all but some specific511 options.)512 </p></dd></dl></div><p>513 </p><p>514 The <code class="literal">LIKE</code> clause can also be used to copy column515 definitions from views, foreign tables, or composite types.516 Inapplicable options (e.g., <code class="literal">INCLUDING INDEXES</code> from517 a view) are ignored.518 </p></dd><dt id="SQL-CREATETABLE-PARMS-CONSTRAINT"><span class="term"><code class="literal">CONSTRAINT <em class="replaceable"><code>constraint_name</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-CONSTRAINT" class="id_link">#</a></dt><dd><p>519 An optional name for a column or table constraint. If the520 constraint is violated, the constraint name is present in error messages,521 so constraint names like <code class="literal">col must be positive</code> can be used522 to communicate helpful constraint information to client applications.523 (Double-quotes are needed to specify constraint names that contain spaces.)524 If a constraint name is not specified, the system generates a name.525 </p></dd><dt id="SQL-CREATETABLE-PARMS-NOT-NULL"><span class="term"><code class="literal">NOT NULL</code></span> <a href="#SQL-CREATETABLE-PARMS-NOT-NULL" class="id_link">#</a></dt><dd><p>526 The column is not allowed to contain null values.527 </p></dd><dt id="SQL-CREATETABLE-PARMS-NULL"><span class="term"><code class="literal">NULL</code></span> <a href="#SQL-CREATETABLE-PARMS-NULL" class="id_link">#</a></dt><dd><p>528 The column is allowed to contain null values. This is the default.529 </p><p>530 This clause is only provided for compatibility with531 non-standard SQL databases. Its use is discouraged in new532 applications.533 </p></dd><dt id="SQL-CREATETABLE-PARMS-CHECK"><span class="term"><code class="literal">CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] </code></span> <a href="#SQL-CREATETABLE-PARMS-CHECK" class="id_link">#</a></dt><dd><p>534 The <code class="literal">CHECK</code> clause specifies an expression producing a535 Boolean result which new or updated rows must satisfy for an536 insert or update operation to succeed. Expressions evaluating537 to TRUE or UNKNOWN succeed. Should any row of an insert or538 update operation produce a FALSE result, an error exception is539 raised and the insert or update does not alter the database. A540 check constraint specified as a column constraint should541 reference that column's value only, while an expression542 appearing in a table constraint can reference multiple columns.543 </p><p>544 Currently, <code class="literal">CHECK</code> expressions cannot contain545 subqueries nor refer to variables other than columns of the546 current row (see <a class="xref" href="ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS" title="5.4.1. Check Constraints">Section 5.4.1</a>).547 The system column <code class="literal">tableoid</code>548 may be referenced, but not any other system column.549 </p><p>550 A constraint marked with <code class="literal">NO INHERIT</code> will not propagate to551 child tables.552 </p><p>553 When a table has multiple <code class="literal">CHECK</code> constraints,554 they will be tested for each row in alphabetical order by name,555 after checking <code class="literal">NOT NULL</code> constraints.556 (<span class="productname">PostgreSQL</span> versions before 9.5 did not honor any557 particular firing order for <code class="literal">CHECK</code> constraints.)558 </p></dd><dt id="SQL-CREATETABLE-PARMS-DEFAULT"><span class="term"><code class="literal">DEFAULT559 <em class="replaceable"><code>default_expr</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-DEFAULT" class="id_link">#</a></dt><dd><p>560 The <code class="literal">DEFAULT</code> clause assigns a default data value for561 the column whose column definition it appears within. The value562 is any variable-free expression (in particular, cross-references563 to other columns in the current table are not allowed). Subqueries564 are not allowed either. The data type of the default expression must565 match the data type of the column.566 </p><p>567 The default expression will be used in any insert operation that568 does not specify a value for the column. If there is no default569 for a column, then the default is null.570 </p></dd><dt id="SQL-CREATETABLE-PARMS-GENERATED-STORED"><span class="term"><code class="literal">GENERATED ALWAYS AS ( <em class="replaceable"><code>generation_expr</code></em> ) STORED</code><a id="id-1.9.3.85.6.2.20.1.2" class="indexterm"></a></span> <a href="#SQL-CREATETABLE-PARMS-GENERATED-STORED" class="id_link">#</a></dt><dd><p>571 This clause creates the column as a <em class="firstterm">generated572 column</em>. The column cannot be written to, and when read the573 result of the specified expression will be returned.574 </p><p>575 The keyword <code class="literal">STORED</code> is required to signify that the576 column will be computed on write and will be stored on disk.577 </p><p>578 The generation expression can refer to other columns in the table, but579 not other generated columns. Any functions and operators used must be580 immutable. References to other tables are not allowed.581 </p></dd><dt id="SQL-CREATETABLE-PARMS-GENERATED-IDENTITY"><span class="term"><code class="literal">GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <em class="replaceable"><code>sequence_options</code></em> ) ]</code></span> <a href="#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY" class="id_link">#</a></dt><dd><p>582 This clause creates the column as an <em class="firstterm">identity583 column</em>. It will have an implicit sequence attached to it584 and the column in new rows will automatically have values from the585 sequence assigned to it.586 Such a column is implicitly <code class="literal">NOT NULL</code>.587 </p><p>588 The clauses <code class="literal">ALWAYS</code> and <code class="literal">BY DEFAULT</code>589 determine how explicitly user-specified values are handled in590 <code class="command">INSERT</code> and <code class="command">UPDATE</code> commands.591 </p><p>592 In an <code class="command">INSERT</code> command, if <code class="literal">ALWAYS</code> is593 selected, a user-specified value is only accepted if the594 <code class="command">INSERT</code> statement specifies <code class="literal">OVERRIDING SYSTEM595 VALUE</code>. If <code class="literal">BY DEFAULT</code> is selected, then the596 user-specified value takes precedence. See <a class="xref" href="sql-insert.html" title="INSERT"><span class="refentrytitle">INSERT</span></a>597 for details. (In the <code class="command">COPY</code> command, user-specified598 values are always used regardless of this setting.)599 </p><p>600 In an <code class="command">UPDATE</code> command, if <code class="literal">ALWAYS</code> is601 selected, any update of the column to any value other than602 <code class="literal">DEFAULT</code> will be rejected. If <code class="literal">BY603 DEFAULT</code> is selected, the column can be updated normally.604 (There is no <code class="literal">OVERRIDING</code> clause for the605 <code class="command">UPDATE</code> command.)606 </p><p>607 The optional <em class="replaceable"><code>sequence_options</code></em> clause can be608 used to override the options of the sequence.609 See <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a> for details.610 </p></dd><dt id="SQL-CREATETABLE-PARMS-UNIQUE"><span class="term"><code class="literal">UNIQUE [ NULLS [ NOT ] DISTINCT ]</code> (column constraint)<br /></span><span class="term"><code class="literal">UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <em class="replaceable"><code>column_name</code></em> [, ... ] )</code>611 [<span class="optional"> <code class="literal">INCLUDE ( <em class="replaceable"><code>column_name</code></em> [, ...])</code> </span>] (table constraint)</span> <a href="#SQL-CREATETABLE-PARMS-UNIQUE" class="id_link">#</a></dt><dd><p>612 The <code class="literal">UNIQUE</code> constraint specifies that a613 group of one or more columns of a table can contain614 only unique values. The behavior of a unique table constraint615 is the same as that of a unique column constraint, with the616 additional capability to span multiple columns. The constraint617 therefore enforces that any two rows must differ in at least one618 of these columns.619 </p><p>620 For the purpose of a unique constraint, null values are not621 considered equal, unless <code class="literal">NULLS NOT DISTINCT</code> is622 specified.623 </p><p>624 Each unique constraint should name a set of columns that is625 different from the set of columns named by any other unique or626 primary key constraint defined for the table. (Otherwise, redundant627 unique constraints will be discarded.)628 </p><p>629 When establishing a unique constraint for a multi-level partition630 hierarchy, all the columns in the partition key of the target631 partitioned table, as well as those of all its descendant partitioned632 tables, must be included in the constraint definition.633 </p><p>634 Adding a unique constraint will automatically create a unique btree635 index on the column or group of columns used in the constraint.636 </p><p>637 The optional <code class="literal">INCLUDE</code> clause adds to that index638 one or more columns that are simply <span class="quote">“<span class="quote">payload</span>”</span>: uniqueness639 is not enforced on them, and the index cannot be searched on the basis640 of those columns. However they can be retrieved by an index-only scan.641 Note that although the constraint is not enforced on included columns,642 it still depends on them. Consequently, some operations on such columns643 (e.g., <code class="literal">DROP COLUMN</code>) can cause cascaded constraint and644 index deletion.645 </p></dd><dt id="SQL-CREATETABLE-PARMS-PRIMARY-KEY"><span class="term"><code class="literal">PRIMARY KEY</code> (column constraint)<br /></span><span class="term"><code class="literal">PRIMARY KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] )</code>646 [<span class="optional"> <code class="literal">INCLUDE ( <em class="replaceable"><code>column_name</code></em> [, ...])</code> </span>] (table constraint)</span> <a href="#SQL-CREATETABLE-PARMS-PRIMARY-KEY" class="id_link">#</a></dt><dd><p>647 The <code class="literal">PRIMARY KEY</code> constraint specifies that a column or648 columns of a table can contain only unique (non-duplicate), nonnull649 values. Only one primary key can be specified for a table, whether as a650 column constraint or a table constraint.651 </p><p>652 The primary key constraint should name a set of columns that is653 different from the set of columns named by any unique654 constraint defined for the same table. (Otherwise, the unique655 constraint is redundant and will be discarded.)656 </p><p>657 <code class="literal">PRIMARY KEY</code> enforces the same data constraints as658 a combination of <code class="literal">UNIQUE</code> and <code class="literal">NOT659 NULL</code>. However,660 identifying a set of columns as the primary key also provides metadata661 about the design of the schema, since a primary key implies that other662 tables can rely on this set of columns as a unique identifier for rows.663 </p><p>664 When placed on a partitioned table, <code class="literal">PRIMARY KEY</code>665 constraints share the restrictions previously described666 for <code class="literal">UNIQUE</code> constraints.667 </p><p>668 Adding a <code class="literal">PRIMARY KEY</code> constraint will automatically669 create a unique btree index on the column or group of columns used in the670 constraint.671 </p><p>672 The optional <code class="literal">INCLUDE</code> clause adds to that index673 one or more columns that are simply <span class="quote">“<span class="quote">payload</span>”</span>: uniqueness674 is not enforced on them, and the index cannot be searched on the basis675 of those columns. However they can be retrieved by an index-only scan.676 Note that although the constraint is not enforced on included columns,677 it still depends on them. Consequently, some operations on such columns678 (e.g., <code class="literal">DROP COLUMN</code>) can cause cascaded constraint and679 index deletion.680 </p></dd><dt id="SQL-CREATETABLE-EXCLUDE"><span class="term"><code class="literal">EXCLUDE [ USING <em class="replaceable"><code>index_method</code></em> ] ( <em class="replaceable"><code>exclude_element</code></em> WITH <em class="replaceable"><code>operator</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> [ WHERE ( <em class="replaceable"><code>predicate</code></em> ) ]</code></span> <a href="#SQL-CREATETABLE-EXCLUDE" class="id_link">#</a></dt><dd><p>681 The <code class="literal">EXCLUDE</code> clause defines an exclusion682 constraint, which guarantees that if683 any two rows are compared on the specified column(s) or684 expression(s) using the specified operator(s), not all of these685 comparisons will return <code class="literal">TRUE</code>. If all of the686 specified operators test for equality, this is equivalent to a687 <code class="literal">UNIQUE</code> constraint, although an ordinary unique constraint688 will be faster. However, exclusion constraints can specify689 constraints that are more general than simple equality.690 For example, you can specify a constraint that691 no two rows in the table contain overlapping circles692 (see <a class="xref" href="datatype-geometric.html" title="8.8. Geometric Types">Section 8.8</a>) by using the693 <code class="literal">&&</code> operator.694 The operator(s) are required to be commutative.695 </p><p>696 Exclusion constraints are implemented using697 an index, so each specified operator must be associated with an698 appropriate operator class699 (see <a class="xref" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families">Section 11.10</a>) for the index access700 method <em class="replaceable"><code>index_method</code></em>.701 Each <em class="replaceable"><code>exclude_element</code></em>702 defines a column of the index, so it can optionally specify a collation,703 an operator class, operator class parameters, and/or ordering options;704 these are described fully under <a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a>.705 </p><p>706 The access method must support <code class="literal">amgettuple</code> (see <a class="xref" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">Chapter 64</a>); at present this means <acronym class="acronym">GIN</acronym>707 cannot be used. Although it's allowed, there is little point in using708 B-tree or hash indexes with an exclusion constraint, because this709 does nothing that an ordinary unique constraint doesn't do better.710 So in practice the access method will always be <acronym class="acronym">GiST</acronym> or711 <acronym class="acronym">SP-GiST</acronym>.712 </p><p>713 The <em class="replaceable"><code>predicate</code></em> allows you to specify an714 exclusion constraint on a subset of the table; internally this creates a715 partial index. Note that parentheses are required around the predicate.716 </p></dd><dt id="SQL-CREATETABLE-PARMS-REFERENCES"><span class="term"><code class="literal">REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> ) ] [ MATCH <em class="replaceable"><code>matchtype</code></em> ] [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ] [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ]</code> (column constraint)<br /></span><span class="term"><code class="literal">FOREIGN KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] )717 REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] ) ]718 [ MATCH <em class="replaceable"><code>matchtype</code></em> ]719 [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ]720 [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ]</code>721 (table constraint)</span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES" class="id_link">#</a></dt><dd><p>722 These clauses specify a foreign key constraint, which requires723 that a group of one or more columns of the new table must only724 contain values that match values in the referenced725 column(s) of some row of the referenced table. If the <em class="replaceable"><code>refcolumn</code></em> list is omitted, the726 primary key of the <em class="replaceable"><code>reftable</code></em>727 is used. Otherwise, the <em class="replaceable"><code>refcolumn</code></em>728 list must refer to the columns of a non-deferrable unique or primary key729 constraint or be the columns of a non-partial unique index. The user730 must have <code class="literal">REFERENCES</code> permission on the referenced731 table (either the whole table, or the specific referenced columns). The732 addition of a foreign key constraint requires a733 <code class="literal">SHARE ROW EXCLUSIVE</code> lock on the referenced table.734 Note that foreign key constraints cannot be defined between temporary735 tables and permanent tables.736 </p><p>737 A value inserted into the referencing column(s) is matched against the738 values of the referenced table and referenced columns using the739 given match type. There are three match types: <code class="literal">MATCH740 FULL</code>, <code class="literal">MATCH PARTIAL</code>, and <code class="literal">MATCH741 SIMPLE</code> (which is the default). <code class="literal">MATCH742 FULL</code> will not allow one column of a multicolumn foreign key743 to be null unless all foreign key columns are null; if they are all744 null, the row is not required to have a match in the referenced table.745 <code class="literal">MATCH SIMPLE</code> allows any of the foreign key columns746 to be null; if any of them are null, the row is not required to have a747 match in the referenced table.748 <code class="literal">MATCH PARTIAL</code> is not yet implemented.749 (Of course, <code class="literal">NOT NULL</code> constraints can be applied to the750 referencing column(s) to prevent these cases from arising.)751 </p><p>752 In addition, when the data in the referenced columns is changed,753 certain actions are performed on the data in this table's754 columns. The <code class="literal">ON DELETE</code> clause specifies the755 action to perform when a referenced row in the referenced table is756 being deleted. Likewise, the <code class="literal">ON UPDATE</code>757 clause specifies the action to perform when a referenced column758 in the referenced table is being updated to a new value. If the759 row is updated, but the referenced column is not actually760 changed, no action is done. Referential actions other than the761 <code class="literal">NO ACTION</code> check cannot be deferred, even if762 the constraint is declared deferrable. There are the following possible763 actions for each clause:764 765 </p><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATETABLE-PARMS-REFERENCES-REFACT-NO-ACTION"><span class="term"><code class="literal">NO ACTION</code></span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES-REFACT-NO-ACTION" class="id_link">#</a></dt><dd><p>766 Produce an error indicating that the deletion or update767 would create a foreign key constraint violation.768 If the constraint is deferred, this769 error will be produced at constraint check time if there still770 exist any referencing rows. This is the default action.771 </p></dd><dt id="SQL-CREATETABLE-PARMS-REFERENCES-REFACT-RESTRICT"><span class="term"><code class="literal">RESTRICT</code></span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES-REFACT-RESTRICT" class="id_link">#</a></dt><dd><p>772 Produce an error indicating that the deletion or update773 would create a foreign key constraint violation.774 This is the same as <code class="literal">NO ACTION</code> except that775 the check is not deferrable.776 </p></dd><dt id="SQL-CREATETABLE-PARMS-REFERENCES-REFACT-CASCADE"><span class="term"><code class="literal">CASCADE</code></span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES-REFACT-CASCADE" class="id_link">#</a></dt><dd><p>777 Delete any rows referencing the deleted row, or update the778 values of the referencing column(s) to the new values of the779 referenced columns, respectively.780 </p></dd><dt id="SQL-CREATETABLE-PARMS-REFERENCES-REFACT-SET-NULL"><span class="term"><code class="literal">SET NULL [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ]</code></span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES-REFACT-SET-NULL" class="id_link">#</a></dt><dd><p>781 Set all of the referencing columns, or a specified subset of the782 referencing columns, to null. A subset of columns can only be783 specified for <code class="literal">ON DELETE</code> actions.784 </p></dd><dt id="SQL-CREATETABLE-PARMS-REFERENCES-REFACT-SET-DEFAULT"><span class="term"><code class="literal">SET DEFAULT [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ]</code></span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES-REFACT-SET-DEFAULT" class="id_link">#</a></dt><dd><p>785 Set all of the referencing columns, or a specified subset of the786 referencing columns, to their default values. A subset of columns787 can only be specified for <code class="literal">ON DELETE</code> actions.788 (There must be a row in the referenced table matching the default789 values, if they are not null, or the operation will fail.)790 </p></dd></dl></div><p>791 </p><p>792 If the referenced column(s) are changed frequently, it might be wise to793 add an index to the referencing column(s) so that referential actions794 associated with the foreign key constraint can be performed more795 efficiently.796 </p></dd><dt id="SQL-CREATETABLE-PARMS-DEFERRABLE"><span class="term"><code class="literal">DEFERRABLE</code><br /></span><span class="term"><code class="literal">NOT DEFERRABLE</code></span> <a href="#SQL-CREATETABLE-PARMS-DEFERRABLE" class="id_link">#</a></dt><dd><p>797 This controls whether the constraint can be deferred. A798 constraint that is not deferrable will be checked immediately799 after every command. Checking of constraints that are800 deferrable can be postponed until the end of the transaction801 (using the <a class="link" href="sql-set-constraints.html" title="SET CONSTRAINTS"><code class="command">SET CONSTRAINTS</code></a> command).802 <code class="literal">NOT DEFERRABLE</code> is the default.803 Currently, only <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>,804 <code class="literal">EXCLUDE</code>, and805 <code class="literal">REFERENCES</code> (foreign key) constraints accept this806 clause. <code class="literal">NOT NULL</code> and <code class="literal">CHECK</code> constraints are not807 deferrable. Note that deferrable constraints cannot be used as808 conflict arbitrators in an <code class="command">INSERT</code> statement that809 includes an <code class="literal">ON CONFLICT DO UPDATE</code> clause.810 </p></dd><dt id="SQL-CREATETABLE-PARMS-INITIALLY"><span class="term"><code class="literal">INITIALLY IMMEDIATE</code><br /></span><span class="term"><code class="literal">INITIALLY DEFERRED</code></span> <a href="#SQL-CREATETABLE-PARMS-INITIALLY" class="id_link">#</a></dt><dd><p>811 If a constraint is deferrable, this clause specifies the default812 time to check the constraint. If the constraint is813 <code class="literal">INITIALLY IMMEDIATE</code>, it is checked after each814 statement. This is the default. If the constraint is815 <code class="literal">INITIALLY DEFERRED</code>, it is checked only at the816 end of the transaction. The constraint check time can be817 altered with the <a class="link" href="sql-set-constraints.html" title="SET CONSTRAINTS"><code class="command">SET CONSTRAINTS</code></a> command.818 </p></dd><dt id="SQL-CREATETABLE-METHOD"><span class="term"><code class="literal">USING <em class="replaceable"><code>method</code></em></code></span> <a href="#SQL-CREATETABLE-METHOD" class="id_link">#</a></dt><dd><p>819 This optional clause specifies the table access method to use to store820 the contents for the new table; the method needs be an access method of821 type <code class="literal">TABLE</code>. See <a class="xref" href="tableam.html" title="Chapter 63. Table Access Method Interface Definition">Chapter 63</a> for more822 information. If this option is not specified, the default table access823 method is chosen for the new table. See <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD">default_table_access_method</a> for more information.824 </p></dd><dt id="SQL-CREATETABLE-PARMS-WITH"><span class="term"><code class="literal">WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span> <a href="#SQL-CREATETABLE-PARMS-WITH" class="id_link">#</a></dt><dd><p>825 This clause specifies optional storage parameters for a table or index;826 see <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS" title="Storage Parameters">Storage Parameters</a> below for more827 information. For backward-compatibility the <code class="literal">WITH</code>828 clause for a table can also include <code class="literal">OIDS=FALSE</code> to829 specify that rows of the new table should not contain OIDs (object830 identifiers), <code class="literal">OIDS=TRUE</code> is not supported anymore.831 </p></dd><dt id="SQL-CREATETABLE-PARMS-WITHOUT-OIDS"><span class="term"><code class="literal">WITHOUT OIDS</code></span> <a href="#SQL-CREATETABLE-PARMS-WITHOUT-OIDS" class="id_link">#</a></dt><dd><p>832 This is backward-compatible syntax for declaring a table833 <code class="literal">WITHOUT OIDS</code>, creating a table <code class="literal">WITH834 OIDS</code> is not supported anymore.835 </p></dd><dt id="SQL-CREATETABLE-PARMS-ON-COMMIT"><span class="term"><code class="literal">ON COMMIT</code></span> <a href="#SQL-CREATETABLE-PARMS-ON-COMMIT" class="id_link">#</a></dt><dd><p>836 The behavior of temporary tables at the end of a transaction837 block can be controlled using <code class="literal">ON COMMIT</code>.838 The three options are:839 840 </p><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATETABLE-PARMS-ON-COMMIT-PRESERVE-ROWS"><span class="term"><code class="literal">PRESERVE ROWS</code></span> <a href="#SQL-CREATETABLE-PARMS-ON-COMMIT-PRESERVE-ROWS" class="id_link">#</a></dt><dd><p>841 No special action is taken at the ends of transactions.842 This is the default behavior.843 </p></dd><dt id="SQL-CREATETABLE-PARMS-ON-COMMIT-DELETE-ROWS"><span class="term"><code class="literal">DELETE ROWS</code></span> <a href="#SQL-CREATETABLE-PARMS-ON-COMMIT-DELETE-ROWS" class="id_link">#</a></dt><dd><p>844 All rows in the temporary table will be deleted at the end845 of each transaction block. Essentially, an automatic <a class="link" href="sql-truncate.html" title="TRUNCATE"><code class="command">TRUNCATE</code></a> is done846 at each commit. When used on a partitioned table, this847 is not cascaded to its partitions.848 </p></dd><dt id="SQL-CREATETABLE-PARMS-ON-COMMIT-DROP"><span class="term"><code class="literal">DROP</code></span> <a href="#SQL-CREATETABLE-PARMS-ON-COMMIT-DROP" class="id_link">#</a></dt><dd><p>849 The temporary table will be dropped at the end of the current850 transaction block. When used on a partitioned table, this action851 drops its partitions and when used on tables with inheritance852 children, it drops the dependent children.853 </p></dd></dl></div></dd><dt id="SQL-CREATETABLE-TABLESPACE"><span class="term"><code class="literal">TABLESPACE <em class="replaceable"><code>tablespace_name</code></em></code></span> <a href="#SQL-CREATETABLE-TABLESPACE" class="id_link">#</a></dt><dd><p>854 The <em class="replaceable"><code>tablespace_name</code></em> is the name855 of the tablespace in which the new table is to be created.856 If not specified,857 <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLESPACE">default_tablespace</a> is consulted, or858 <a class="xref" href="runtime-config-client.html#GUC-TEMP-TABLESPACES">temp_tablespaces</a> if the table is temporary. For859 partitioned tables, since no storage is required for the table itself,860 the tablespace specified overrides <code class="literal">default_tablespace</code>861 as the default tablespace to use for any newly created partitions when no862 other tablespace is explicitly specified.863 </p></dd><dt id="SQL-CREATETABLE-PARMS-USING-INDEX-TABLESPACE"><span class="term"><code class="literal">USING INDEX TABLESPACE <em class="replaceable"><code>tablespace_name</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-USING-INDEX-TABLESPACE" class="id_link">#</a></dt><dd><p>864 This clause allows selection of the tablespace in which the index865 associated with a <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY866 KEY</code>, or <code class="literal">EXCLUDE</code> constraint will be created.867 If not specified,868 <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLESPACE">default_tablespace</a> is consulted, or869 <a class="xref" href="runtime-config-client.html#GUC-TEMP-TABLESPACES">temp_tablespaces</a> if the table is temporary.870 </p></dd></dl></div><div class="refsect2" id="SQL-CREATETABLE-STORAGE-PARAMETERS"><h3>Storage Parameters</h3><a id="id-1.9.3.85.6.3.2" class="indexterm"></a><p>871 The <code class="literal">WITH</code> clause can specify <em class="firstterm">storage parameters</em>872 for tables, and for indexes associated with a <code class="literal">UNIQUE</code>,873 <code class="literal">PRIMARY KEY</code>, or <code class="literal">EXCLUDE</code> constraint.874 Storage parameters for875 indexes are documented in <a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a>.876 The storage parameters currently877 available for tables are listed below. For many of these parameters, as878 shown, there is an additional parameter with the same name prefixed with879 <code class="literal">toast.</code>, which controls the behavior of the880 table's secondary <acronym class="acronym">TOAST</acronym> table, if any881 (see <a class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a> for more information about TOAST).882 If a table parameter value is set and the883 equivalent <code class="literal">toast.</code> parameter is not, the TOAST table884 will use the table's parameter value.885 Specifying these parameters for partitioned tables is not supported,886 but you may specify them for individual leaf partitions.887 </p><div class="variablelist"><dl class="variablelist"><dt id="RELOPTION-FILLFACTOR"><span class="term"><code class="varname">fillfactor</code> (<code class="type">integer</code>)888 <a id="id-1.9.3.85.6.3.4.1.1.3" class="indexterm"></a>889 </span> <a href="#RELOPTION-FILLFACTOR" class="id_link">#</a></dt><dd><p>890 The fillfactor for a table is a percentage between 10 and 100.891 100 (complete packing) is the default. When a smaller fillfactor892 is specified, <code class="command">INSERT</code> operations pack table pages only893 to the indicated percentage; the remaining space on each page is894 reserved for updating rows on that page. This gives <code class="command">UPDATE</code>895 a chance to place the updated copy of a row on the same page as the896 original, which is more efficient than placing it on a different897 page, and makes <a class="link" href="storage-hot.html" title="73.7. Heap-Only Tuples (HOT)">heap-only tuple898 updates</a> more likely.899 For a table whose entries are never updated, complete packing is the900 best choice, but in heavily updated tables smaller fillfactors are901 appropriate. This parameter cannot be set for TOAST tables.902 </p></dd><dt id="RELOPTION-TOAST-TUPLE-TARGET"><span class="term"><code class="literal">toast_tuple_target</code> (<code class="type">integer</code>)903 <a id="id-1.9.3.85.6.3.4.2.1.3" class="indexterm"></a>904 </span> <a href="#RELOPTION-TOAST-TUPLE-TARGET" class="id_link">#</a></dt><dd><p>905 The toast_tuple_target specifies the minimum tuple length required before906 we try to compress and/or move long column values into TOAST tables, and907 is also the target length we try to reduce the length below once toasting908 begins. This affects columns marked as External (for move),909 Main (for compression), or Extended (for both) and applies only to new910 tuples. There is no effect on existing rows.911 By default this parameter is set to allow at least 4 tuples per block,912 which with the default block size will be 2040 bytes. Valid values are913 between 128 bytes and the (block size - header), by default 8160 bytes.914 Changing this value may not be useful for very short or very long rows.915 Note that the default setting is often close to optimal, and916 it is possible that setting this parameter could have negative917 effects in some cases.918 This parameter cannot be set for TOAST tables.919 </p></dd><dt id="RELOPTION-PARALLEL-WORKERS"><span class="term"><code class="literal">parallel_workers</code> (<code class="type">integer</code>)920 <a id="id-1.9.3.85.6.3.4.3.1.3" class="indexterm"></a>921 </span> <a href="#RELOPTION-PARALLEL-WORKERS" class="id_link">#</a></dt><dd><p>922 This sets the number of workers that should be used to assist a parallel923 scan of this table. If not set, the system will determine a value based924 on the relation size. The actual number of workers chosen by the planner925 or by utility statements that use parallel scans may be less, for example926 due to the setting of <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>.927 </p></dd><dt id="RELOPTION-AUTOVACUUM-ENABLED"><span class="term"><code class="literal">autovacuum_enabled</code>, <code class="literal">toast.autovacuum_enabled</code> (<code class="type">boolean</code>)928 <a id="id-1.9.3.85.6.3.4.4.1.4" class="indexterm"></a>929 </span> <a href="#RELOPTION-AUTOVACUUM-ENABLED" class="id_link">#</a></dt><dd><p>930 Enables or disables the autovacuum daemon for a particular table.931 If true, the autovacuum daemon will perform automatic <code class="command">VACUUM</code>932 and/or <code class="command">ANALYZE</code> operations on this table following the rules933 discussed in <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a>.934 If false, this table will not be autovacuumed, except to prevent935 transaction ID wraparound. See <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="25.1.5. Preventing Transaction ID Wraparound Failures">Section 25.1.5</a> for936 more about wraparound prevention.937 Note that the autovacuum daemon does not run at all (except to prevent938 transaction ID wraparound) if the <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM">autovacuum</a>939 parameter is false; setting individual tables' storage parameters does940 not override that. Therefore there is seldom much point in explicitly941 setting this storage parameter to <code class="literal">true</code>, only942 to <code class="literal">false</code>.943 </p></dd><dt id="RELOPTION-VACUUM-INDEX-CLEANUP"><span class="term"><code class="literal">vacuum_index_cleanup</code>, <code class="literal">toast.vacuum_index_cleanup</code> (<code class="type">enum</code>)944 <a id="id-1.9.3.85.6.3.4.5.1.4" class="indexterm"></a>945 </span> <a href="#RELOPTION-VACUUM-INDEX-CLEANUP" class="id_link">#</a></dt><dd><p>946 Forces or disables index cleanup when <code class="command">VACUUM</code>947 is run on this table. The default value is948 <code class="literal">AUTO</code>. With <code class="literal">OFF</code>, index949 cleanup is disabled, with <code class="literal">ON</code> it is enabled,950 and with <code class="literal">AUTO</code> a decision is made dynamically,951 each time <code class="command">VACUUM</code> runs. The dynamic behavior952 allows <code class="command">VACUUM</code> to avoid needlessly scanning953 indexes to remove very few dead tuples. Forcibly disabling all954 index cleanup can speed up <code class="command">VACUUM</code> very955 significantly, but may also lead to severely bloated indexes if956 table modifications are frequent. The957 <code class="literal">INDEX_CLEANUP</code> parameter of <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a>, if958 specified, overrides the value of this option.959 </p></dd><dt id="RELOPTION-VACUUM-TRUNCATE"><span class="term"><code class="literal">vacuum_truncate</code>, <code class="literal">toast.vacuum_truncate</code> (<code class="type">boolean</code>)960 <a id="id-1.9.3.85.6.3.4.6.1.4" class="indexterm"></a>961 </span> <a href="#RELOPTION-VACUUM-TRUNCATE" class="id_link">#</a></dt><dd><p>962 Enables or disables vacuum to try to truncate off any empty pages963 at the end of this table. The default value is <code class="literal">true</code>.964 If <code class="literal">true</code>, <code class="command">VACUUM</code> and965 autovacuum do the truncation and the disk space for966 the truncated pages is returned to the operating system.967 Note that the truncation requires <code class="literal">ACCESS EXCLUSIVE</code>968 lock on the table. The <code class="literal">TRUNCATE</code> parameter969 of <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a>, if specified, overrides the value970 of this option.971 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-THRESHOLD"><span class="term"><code class="literal">autovacuum_vacuum_threshold</code>, <code class="literal">toast.autovacuum_vacuum_threshold</code> (<code class="type">integer</code>)972 <a id="id-1.9.3.85.6.3.4.7.1.4" class="indexterm"></a>973 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-THRESHOLD" class="id_link">#</a></dt><dd><p>974 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD">autovacuum_vacuum_threshold</a>975 parameter.976 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-SCALE-FACTOR"><span class="term"><code class="literal">autovacuum_vacuum_scale_factor</code>, <code class="literal">toast.autovacuum_vacuum_scale_factor</code> (<code class="type">floating point</code>)977 <a id="id-1.9.3.85.6.3.4.8.1.4" class="indexterm"></a>978 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>979 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR">autovacuum_vacuum_scale_factor</a>980 parameter.981 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-INSERT-THRESHOLD"><span class="term"><code class="literal">autovacuum_vacuum_insert_threshold</code>, <code class="literal">toast.autovacuum_vacuum_insert_threshold</code> (<code class="type">integer</code>)982 <a id="id-1.9.3.85.6.3.4.9.1.4" class="indexterm"></a>983 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-INSERT-THRESHOLD" class="id_link">#</a></dt><dd><p>984 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD">autovacuum_vacuum_insert_threshold</a>985 parameter. The special value of -1 may be used to disable insert vacuums on the table.986 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR"><span class="term"><code class="literal">autovacuum_vacuum_insert_scale_factor</code>, <code class="literal">toast.autovacuum_vacuum_insert_scale_factor</code> (<code class="type">floating point</code>)987 <a id="id-1.9.3.85.6.3.4.10.1.4" class="indexterm"></a>988 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>989 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR">autovacuum_vacuum_insert_scale_factor</a>990 parameter.991 </p></dd><dt id="RELOPTION-AUTOVACUUM-ANALYZE-THRESHOLD"><span class="term"><code class="literal">autovacuum_analyze_threshold</code> (<code class="type">integer</code>)992 <a id="id-1.9.3.85.6.3.4.11.1.3" class="indexterm"></a>993 </span> <a href="#RELOPTION-AUTOVACUUM-ANALYZE-THRESHOLD" class="id_link">#</a></dt><dd><p>994 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD">autovacuum_analyze_threshold</a>995 parameter.996 </p></dd><dt id="RELOPTION-AUTOVACUUM-ANALYZE-SCALE-FACTOR"><span class="term"><code class="literal">autovacuum_analyze_scale_factor</code> (<code class="type">floating point</code>)997 <a id="id-1.9.3.85.6.3.4.12.1.3" class="indexterm"></a>998 </span> <a href="#RELOPTION-AUTOVACUUM-ANALYZE-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>999 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR">autovacuum_analyze_scale_factor</a>1000 parameter.1001 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-COST-DELAY"><span class="term"><code class="literal">autovacuum_vacuum_cost_delay</code>, <code class="literal">toast.autovacuum_vacuum_cost_delay</code> (<code class="type">floating point</code>)1002 <a id="id-1.9.3.85.6.3.4.13.1.4" class="indexterm"></a>1003 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-COST-DELAY" class="id_link">#</a></dt><dd><p>1004 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY">autovacuum_vacuum_cost_delay</a>1005 parameter.1006 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-COST-LIMIT"><span class="term"><code class="literal">autovacuum_vacuum_cost_limit</code>, <code class="literal">toast.autovacuum_vacuum_cost_limit</code> (<code class="type">integer</code>)1007 <a id="id-1.9.3.85.6.3.4.14.1.4" class="indexterm"></a>1008 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-COST-LIMIT" class="id_link">#</a></dt><dd><p>1009 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT">autovacuum_vacuum_cost_limit</a>1010 parameter.1011 </p></dd><dt id="RELOPTION-AUTOVACUUM-FREEZE-MIN-AGE"><span class="term"><code class="literal">autovacuum_freeze_min_age</code>, <code class="literal">toast.autovacuum_freeze_min_age</code> (<code class="type">integer</code>)1012 <a id="id-1.9.3.85.6.3.4.15.1.4" class="indexterm"></a>1013 </span> <a href="#RELOPTION-AUTOVACUUM-FREEZE-MIN-AGE" class="id_link">#</a></dt><dd><p>1014 Per-table value for <a class="xref" href="runtime-config-client.html#GUC-VACUUM-FREEZE-MIN-AGE">vacuum_freeze_min_age</a>1015 parameter. Note that autovacuum will ignore1016 per-table <code class="literal">autovacuum_freeze_min_age</code> parameters that are1017 larger than half the1018 system-wide <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a> setting.1019 </p></dd><dt id="RELOPTION-AUTOVACUUM-FREEZE-MAX-AGE"><span class="term"><code class="literal">autovacuum_freeze_max_age</code>, <code class="literal">toast.autovacuum_freeze_max_age</code> (<code class="type">integer</code>)1020 <a id="id-1.9.3.85.6.3.4.16.1.4" class="indexterm"></a>1021 </span> <a href="#RELOPTION-AUTOVACUUM-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>1022 Per-table value for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a>1023 parameter. Note that autovacuum will ignore1024 per-table <code class="literal">autovacuum_freeze_max_age</code> parameters that are1025 larger than the system-wide setting (it can only be set smaller).1026 </p></dd><dt id="RELOPTION-AUTOVACUUM-FREEZE-TABLE-AGE"><span class="term"><code class="literal">autovacuum_freeze_table_age</code>, <code class="literal">toast.autovacuum_freeze_table_age</code> (<code class="type">integer</code>)1027 <a id="id-1.9.3.85.6.3.4.17.1.4" class="indexterm"></a>1028 </span> <a href="#RELOPTION-AUTOVACUUM-FREEZE-TABLE-AGE" class="id_link">#</a></dt><dd><p>1029 Per-table value for <a class="xref" href="runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE">vacuum_freeze_table_age</a>1030 parameter.1031 </p></dd><dt id="RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-MIN-AGE"><span class="term"><code class="literal">autovacuum_multixact_freeze_min_age</code>, <code class="literal">toast.autovacuum_multixact_freeze_min_age</code> (<code class="type">integer</code>)1032 <a id="id-1.9.3.85.6.3.4.18.1.4" class="indexterm"></a>1033 </span> <a href="#RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-MIN-AGE" class="id_link">#</a></dt><dd><p>1034 Per-table value for <a class="xref" href="runtime-config-client.html#GUC-VACUUM-MULTIXACT-FREEZE-MIN-AGE">vacuum_multixact_freeze_min_age</a>1035 parameter. Note that autovacuum will ignore1036 per-table <code class="literal">autovacuum_multixact_freeze_min_age</code> parameters1037 that are larger than half the1038 system-wide <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a>1039 setting.1040 </p></dd><dt id="RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE"><span class="term"><code class="literal">autovacuum_multixact_freeze_max_age</code>, <code class="literal">toast.autovacuum_multixact_freeze_max_age</code> (<code class="type">integer</code>)1041 <a id="id-1.9.3.85.6.3.4.19.1.4" class="indexterm"></a>1042 </span> <a href="#RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>1043 Per-table value1044 for <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a> parameter.1045 Note that autovacuum will ignore1046 per-table <code class="literal">autovacuum_multixact_freeze_max_age</code> parameters1047 that are larger than the system-wide setting (it can only be set1048 smaller).1049 </p></dd><dt id="RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-TABLE-AGE"><span class="term"><code class="literal">autovacuum_multixact_freeze_table_age</code>, <code class="literal">toast.autovacuum_multixact_freeze_table_age</code> (<code class="type">integer</code>)1050 <a id="id-1.9.3.85.6.3.4.20.1.4" class="indexterm"></a>1051 </span> <a href="#RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-TABLE-AGE" class="id_link">#</a></dt><dd><p>1052 Per-table value1053 for <a class="xref" href="runtime-config-client.html#GUC-VACUUM-MULTIXACT-FREEZE-TABLE-AGE">vacuum_multixact_freeze_table_age</a> parameter.1054 </p></dd><dt id="RELOPTION-LOG-AUTOVACUUM-MIN-DURATION"><span class="term"><code class="literal">log_autovacuum_min_duration</code>, <code class="literal">toast.log_autovacuum_min_duration</code> (<code class="type">integer</code>)1055 <a id="id-1.9.3.85.6.3.4.21.1.4" class="indexterm"></a>1056 </span> <a href="#RELOPTION-LOG-AUTOVACUUM-MIN-DURATION" class="id_link">#</a></dt><dd><p>1057 Per-table value for <a class="xref" href="runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION">log_autovacuum_min_duration</a>1058 parameter.1059 </p></dd><dt id="RELOPTION-USER-CATALOG-TABLE"><span class="term"><code class="literal">user_catalog_table</code> (<code class="type">boolean</code>)1060 <a id="id-1.9.3.85.6.3.4.22.1.3" class="indexterm"></a>1061 </span> <a href="#RELOPTION-USER-CATALOG-TABLE" class="id_link">#</a></dt><dd><p>1062 Declare the table as an additional catalog table for purposes of1063 logical replication. See1064 <a class="xref" href="logicaldecoding-output-plugin.html#LOGICALDECODING-CAPABILITIES" title="49.6.2. Capabilities">Section 49.6.2</a> for details.1065 This parameter cannot be set for TOAST tables.1066 </p></dd></dl></div></div></div><div class="refsect1" id="SQL-CREATETABLE-NOTES"><h2>Notes</h2><p>1067 <span class="productname">PostgreSQL</span> automatically creates an1068 index for each unique constraint and primary key constraint to1069 enforce uniqueness. Thus, it is not necessary to create an1070 index explicitly for primary key columns. (See <a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a> for more information.)1071 </p><p>1072 Unique constraints and primary keys are not inherited in the1073 current implementation. This makes the combination of1074 inheritance and unique constraints rather dysfunctional.1075 </p><p>1076 A table cannot have more than 1600 columns. (In practice, the1077 effective limit is usually lower because of tuple-length constraints.)1078 </p></div><div class="refsect1" id="SQL-CREATETABLE-EXAMPLES"><h2>Examples</h2><p>1079 Create table <code class="structname">films</code> and table1080 <code class="structname">distributors</code>:1081 1082</p><pre class="programlisting">1083CREATE TABLE films (1084 code char(5) CONSTRAINT firstkey PRIMARY KEY,1085 title varchar(40) NOT NULL,1086 did integer NOT NULL,1087 date_prod date,1088 kind varchar(10),1089 len interval hour to minute1090);1091 1092CREATE TABLE distributors (1093 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,1094 name varchar(40) NOT NULL CHECK (name <> '')1095);1096</pre><p>1097 </p><p>1098 Create a table with a 2-dimensional array:1099 1100</p><pre class="programlisting">1101CREATE TABLE array_int (1102 vector int[][]1103);1104</pre><p>1105 </p><p>1106 Define a unique table constraint for the table1107 <code class="literal">films</code>. Unique table constraints can be defined1108 on one or more columns of the table:1109 1110</p><pre class="programlisting">1111CREATE TABLE films (1112 code char(5),1113 title varchar(40),1114 did integer,1115 date_prod date,1116 kind varchar(10),1117 len interval hour to minute,1118 CONSTRAINT production UNIQUE(date_prod)1119);1120</pre><p>1121 </p><p>1122 Define a check column constraint:1123 1124</p><pre class="programlisting">1125CREATE TABLE distributors (1126 did integer CHECK (did > 100),1127 name varchar(40)1128);1129</pre><p>1130 </p><p>1131 Define a check table constraint:1132 1133</p><pre class="programlisting">1134CREATE TABLE distributors (1135 did integer,1136 name varchar(40),1137 CONSTRAINT con1 CHECK (did > 100 AND name <> '')1138);1139</pre><p>1140 </p><p>1141 Define a primary key table constraint for the table1142 <code class="structname">films</code>:1143 1144</p><pre class="programlisting">1145CREATE TABLE films (1146 code char(5),1147 title varchar(40),1148 did integer,1149 date_prod date,1150 kind varchar(10),1151 len interval hour to minute,1152 CONSTRAINT code_title PRIMARY KEY(code,title)1153);1154</pre><p>1155 </p><p>1156 Define a primary key constraint for table1157 <code class="structname">distributors</code>. The following two examples are1158 equivalent, the first using the table constraint syntax, the second1159 the column constraint syntax:1160 1161</p><pre class="programlisting">1162CREATE TABLE distributors (1163 did integer,1164 name varchar(40),1165 PRIMARY KEY(did)1166);1167 1168CREATE TABLE distributors (1169 did integer PRIMARY KEY,1170 name varchar(40)1171);1172</pre><p>1173 </p><p>1174 Assign a literal constant default value for the column1175 <code class="literal">name</code>, arrange for the default value of column1176 <code class="literal">did</code> to be generated by selecting the next value1177 of a sequence object, and make the default value of1178 <code class="literal">modtime</code> be the time at which the row is1179 inserted:1180 1181</p><pre class="programlisting">1182CREATE TABLE distributors (1183 name varchar(40) DEFAULT 'Luso Films',1184 did integer DEFAULT nextval('distributors_serial'),1185 modtime timestamp DEFAULT current_timestamp1186);1187</pre><p>1188 </p><p>1189 Define two <code class="literal">NOT NULL</code> column constraints on the table1190 <code class="classname">distributors</code>, one of which is explicitly1191 given a name:1192 1193</p><pre class="programlisting">1194CREATE TABLE distributors (1195 did integer CONSTRAINT no_null NOT NULL,1196 name varchar(40) NOT NULL1197);1198</pre><p>1199 </p><p>1200 Define a unique constraint for the <code class="literal">name</code> column: