Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-createtable.html1494 linesDownload Raw Back to html
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&gt;=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&lt;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">&amp;&amp;</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 &lt;&gt; '')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 &gt; 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 &gt; 100 AND name &lt;&gt; '')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:

Showing the first 1,200 of 1494 lines. Download the file for the rest.

codekingpro/portable-devtools · Team Ai