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 18.0 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">
3 CREATE [ [ 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> ... ] }
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> ]
16 CREATE [ [ 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> }
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> ]
28 CREATE [ [ 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> }
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> ]
40 <span class="phrase">where <em class="replaceable"><code>column_constraint</code></em> is:</span>
42 [ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
43 { NOT NULL [ NO INHERIT ] |
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 | VIRTUAL ] |
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 ] [ ENFORCED | NOT ENFORCED ]
55 <span class="phrase">and <em class="replaceable"><code>table_constraint</code></em> is:</span>
57 [ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
58 { CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] |
59 NOT NULL <em class="replaceable"><code>column_name</code></em> [ NO INHERIT ] |
60 UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <em class="replaceable"><code>column_name</code></em> [, ... ] [, <em class="replaceable"><code>column_name</code></em> WITHOUT OVERLAPS ] ) <em class="replaceable"><code>index_parameters</code></em> |
61 PRIMARY KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] [, <em class="replaceable"><code>column_name</code></em> WITHOUT OVERLAPS ] ) <em class="replaceable"><code>index_parameters</code></em> |
62 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> ) ] |
63 FOREIGN KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] [, PERIOD <em class="replaceable"><code>column_name</code></em> ] ) REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] [, PERIOD <em class="replaceable"><code>refcolumn</code></em> ] ) ]
64 [ 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> ] }
65 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
67 <span class="phrase">and <em class="replaceable"><code>like_option</code></em> is:</span>
69 { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
71 <span class="phrase">and <em class="replaceable"><code>partition_bound_spec</code></em> is:</span>
73 IN ( <em class="replaceable"><code>partition_bound_expr</code></em> [, ...] ) |
74 FROM ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] )
75 TO ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] ) |
76 WITH ( MODULUS <em class="replaceable"><code>numeric_literal</code></em>, REMAINDER <em class="replaceable"><code>numeric_literal</code></em> )
78 <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>
80 [ INCLUDE ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ]
81 [ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
82 [ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ]
84 <span class="phrase"><em class="replaceable"><code>exclude_element</code></em> in an <code class="literal">EXCLUDE</code> constraint is:</span>
86 { <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 } ]
88 <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>
90 { NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] | SET DEFAULT [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] }
91 </pre></div><div class="refsect1" id="SQL-CREATETABLE-DESCRIPTION"><h2>Description</h2><p>
92 <code class="command">CREATE TABLE</code> will create a new, initially empty table
93 in the current database. The table will be owned by the user issuing the
96 If a schema name is given (for example, <code class="literal">CREATE TABLE
97 myschema.mytable ...</code>) then the table is created in the specified
98 schema. Otherwise it is created in the current schema. Temporary
99 tables exist in a special schema, so a schema name cannot be given
100 when creating a temporary table. The name of the table must be
101 distinct from the name of any other relation (table, sequence, index, view,
102 materialized view, or foreign table) in the same schema.
104 <code class="command">CREATE TABLE</code> also automatically creates a data
105 type that represents the composite type corresponding
106 to one row of the table. Therefore, tables cannot have the same
107 name as any existing data type in the same schema.
109 The optional constraint clauses specify constraints (tests) that
110 new or updated rows must satisfy for an insert or update operation
111 to succeed. A constraint is an SQL object that helps define the
112 set of valid values in the table in various ways.
114 There are two ways to define constraints: table constraints and
115 column constraints. A column constraint is defined as part of a
116 column definition. A table constraint definition is not tied to a
117 particular column, and it can encompass more than one column.
118 Every column constraint can also be written as a table constraint;
119 a column constraint is only a notational convenience for use when the
120 constraint only affects one column.
122 To be able to create a table, you must have <code class="literal">USAGE</code>
123 privilege on all column types or the type in the <code class="literal">OF</code>
124 clause, respectively.
125 </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>
126 If specified, the table is created as a temporary table.
127 Temporary tables are automatically dropped at the end of a
128 session, or optionally at the end of the current transaction
129 (see <code class="literal">ON COMMIT</code> below). The default
130 search_path includes the temporary schema first and so identically
131 named existing permanent tables are not chosen for new plans
132 while the temporary table exists, unless they are referenced
133 with schema-qualified names. Any indexes created on a temporary
134 table are automatically temporary as well.
136 The <a class="link" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">autovacuum daemon</a> cannot
137 access and therefore cannot vacuum or analyze temporary tables.
138 For this reason, appropriate vacuum and analyze operations should be
139 performed via session SQL commands. For example, if a temporary
140 table is going to be used in complex queries, it is wise to run
141 <code class="command">ANALYZE</code> on the temporary table after it is populated.
143 Optionally, <code class="literal">GLOBAL</code> or <code class="literal">LOCAL</code>
144 can be written before <code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code>.
145 This presently makes no difference in <span class="productname">PostgreSQL</span>
146 and is deprecated; see
147 <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY" title="Compatibility">Compatibility</a> below.
148 </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>
149 If specified, the table is created as an unlogged table. Data written
150 to unlogged tables is not written to the write-ahead log (see <a class="xref" href="wal.html" title="Chapter 28. Reliability and the Write-Ahead Log">Chapter 28</a>), which makes them considerably faster than ordinary
151 tables. However, they are not crash-safe: an unlogged table is
152 automatically truncated after a crash or unclean shutdown. The contents
153 of an unlogged table are also not replicated to standby servers.
154 Any indexes created on an unlogged table are automatically unlogged as
157 If this is specified, any sequences created together with the unlogged
158 table (for identity or serial columns) are also created as unlogged.
160 This form is not supported for partitioned tables.
161 </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>
162 Do not throw an error if a relation with the same name already exists.
163 A notice is issued in this case. Note that there is no guarantee that
164 the existing relation is anything like the one that would have been
166 </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>
167 The name (optionally schema-qualified) of the table to be created.
168 </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>
169 Creates a <em class="firstterm">typed table</em>, which takes its
170 structure from the specified stand-alone composite type (that is,
171 one created using <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a>) though it still
172 produces a new composite type as well. The table will have a
173 dependency on the referenced type, meaning that cascaded alter and
174 drop actions on that type will propagate to the table.
176 A typed table always has the same column names and data types as the
177 type it is derived from, so you cannot specify additional columns.
178 But the <code class="literal">CREATE TABLE</code> command can add defaults
179 and constraints to the table, as well as specify storage parameters.
180 </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>
181 The name of a column to be created in the new table.
182 </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>
183 The data type of the column. This can include array
184 specifiers. For more information on the data types supported by
185 <span class="productname">PostgreSQL</span>, refer to <a class="xref" href="datatype.html" title="Chapter 8. Data Types">Chapter 8</a>.
186 </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>
187 The <code class="literal">COLLATE</code> clause assigns a collation to
188 the column (which must be of a collatable data type).
189 If not specified, the column data type's default collation is used.
190 </p></dd><dt id="SQL-CREATETABLE-PARMS-STORAGE"><span class="term">
191 <code class="literal">STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }</code>
192 <a id="id-1.9.3.85.6.2.9.1.2" class="indexterm"></a>
193 </span> <a href="#SQL-CREATETABLE-PARMS-STORAGE" class="id_link">#</a></dt><dd><p>
194 This form sets the storage mode for the column. This controls whether this
195 column is held inline or in a secondary <acronym class="acronym">TOAST</acronym> table,
196 and whether the data should be compressed or not. <code class="literal">PLAIN</code>
197 must be used for fixed-length values such as <code class="type">integer</code> and is
198 inline, uncompressed. <code class="literal">MAIN</code> is for inline, compressible
199 data. <code class="literal">EXTERNAL</code> is for external, uncompressed data, and
200 <code class="literal">EXTENDED</code> is for external, compressed data.
201 Writing <code class="literal">DEFAULT</code> sets the storage mode to the default
202 mode for the column's data type. <code class="literal">EXTENDED</code> is the
203 default for most data types that support non-<code class="literal">PLAIN</code>
205 Use of <code class="literal">EXTERNAL</code> will make substring operations on
206 very large <code class="type">text</code> and <code class="type">bytea</code> values run faster,
207 at the penalty of increased storage space.
208 See <a class="xref" href="storage-toast.html" title="66.2. TOAST">Section 66.2</a> for more information.
209 </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>
210 The <code class="literal">COMPRESSION</code> clause sets the compression method
211 for the column. Compression is supported only for variable-width data
212 types, and is used only when the column's storage mode
213 is <code class="literal">main</code> or <code class="literal">extended</code>.
214 (See <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> for information on
215 column storage modes.) Setting this property for a partitioned table
216 has no direct effect, because such tables have no storage of their own,
217 but the configured value will be inherited by newly-created partitions.
218 The supported compression methods are <code class="literal">pglz</code> and
219 <code class="literal">lz4</code>. (<code class="literal">lz4</code> is available only if
220 <code class="option">--with-lz4</code> was used when building
221 <span class="productname">PostgreSQL</span>.) In addition,
222 <em class="replaceable"><code>compression_method</code></em>
223 can be <code class="literal">default</code> to explicitly specify the default
224 behavior, which is to consult the
225 <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION">default_toast_compression</a> setting at the time of
226 data insertion to determine the method to use.
227 </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>
228 The optional <code class="literal">INHERITS</code> clause specifies a list of
229 tables from which the new table automatically inherits all
230 columns. Parent tables can be plain tables or foreign tables.
232 Use of <code class="literal">INHERITS</code> creates a persistent relationship
233 between the new child table and its parent table(s). Schema
234 modifications to the parent(s) normally propagate to children
235 as well, and by default the data of the child table is included in
236 scans of the parent(s).
238 If the same column name exists in more than one parent
239 table, an error is reported unless the data types of the columns
240 match in each of the parent tables. If there is no conflict,
241 then the duplicate columns are merged to form a single column in
242 the new table. If the column name list of the new table
243 contains a column name that is also inherited, the data type must
244 likewise match the inherited column(s), and the column
245 definitions are merged into one. If the
246 new table explicitly specifies a default value for the column,
247 this default overrides any defaults from inherited declarations
248 of the column. Otherwise, any parents that specify default
249 values for the column must all specify the same default, or an
250 error will be reported.
252 <code class="literal">CHECK</code> constraints are merged in essentially the same way as
253 columns: if multiple parent tables and/or the new table definition
254 contain identically-named <code class="literal">CHECK</code> constraints, these
255 constraints must all have the same check expression, or an error will be
256 reported. Constraints having the same name and expression will
257 be merged into one copy. A constraint marked <code class="literal">NO INHERIT</code> in a
258 parent will not be considered. Notice that an unnamed <code class="literal">CHECK</code>
259 constraint in the new table will never be merged, since a unique name
260 will always be chosen for it.
262 Column <code class="literal">STORAGE</code> settings are also copied from parent tables.
264 If a column in the parent table is an identity column, that property is
265 not inherited. A column in the child table can be declared identity
267 </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>
268 The optional <code class="literal">PARTITION BY</code> clause specifies a strategy
269 of partitioning the table. The table thus created is called a
270 <em class="firstterm">partitioned</em> table. The parenthesized list of
271 columns or expressions forms the <em class="firstterm">partition key</em>
272 for the table. When using range or hash partitioning, the partition key
273 can include multiple columns or expressions (up to 32, but this limit can
274 be altered when building <span class="productname">PostgreSQL</span>), but for
275 list partitioning, the partition key must consist of a single column or
278 Range and list partitioning require a btree operator class, while hash
279 partitioning requires a hash operator class. If no operator class is
280 specified explicitly, the default operator class of the appropriate
281 type will be used; if no default operator class exists, an error will
282 be raised. When hash partitioning is used, the operator class used
283 must implement support function 2 (see <a class="xref" href="xindex.html#XINDEX-SUPPORT" title="36.16.3. Index Method Support Routines">Section 36.16.3</a>
286 A partitioned table is divided into sub-tables (called partitions),
287 which are created using separate <code class="literal">CREATE TABLE</code> commands.
288 The partitioned table is itself empty. A data row inserted into the
289 table is routed to a partition based on the value of columns or
290 expressions in the partition key. If no existing partition matches
291 the values in the new row, an error will be reported.
293 See <a class="xref" href="ddl-partitioning.html" title="5.12. Table Partitioning">Section 5.12</a> for more discussion on table
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 specified
297 parent table. The table can be created either as a partition for specific
298 values using <code class="literal">FOR VALUES</code> or as a default partition
299 using <code class="literal">DEFAULT</code>. Any indexes, constraints and
300 user-defined row-level triggers that exist in the parent table are cloned
301 on the new partition.
303 The <em class="replaceable"><code>partition_bound_spec</code></em>
304 must correspond to the partitioning method and partition key of the
305 parent table, and must not overlap with any existing partition of that
306 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 used
308 for range partitioning, and the form with <code class="literal">WITH</code> is used
309 for hash partitioning.
311 <em class="replaceable"><code>partition_bound_expr</code></em> is
312 any variable-free expression (subqueries, window functions, aggregate
313 functions, and set-returning functions are not allowed). Its data type
314 must match the data type of the corresponding partition key column.
315 The expression is evaluated once at table creation time, so it can
316 even contain volatile expressions such as
317 <code class="literal"><code class="function">CURRENT_TIMESTAMP</code></code>.
319 When creating a list partition, <code class="literal">NULL</code> can be
320 specified to signify that the partition allows the partition key
321 column to be null. However, there cannot be more than one such
322 list partition for a given parent table. <code class="literal">NULL</code>
323 cannot be specified for range partitions.
325 When creating a range partition, the lower bound specified with
326 <code class="literal">FROM</code> is an inclusive bound, whereas the upper
327 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> list
329 are valid values of the corresponding partition key columns for this
330 partition, whereas those in the <code class="literal">TO</code> list are
331 not. Note that this statement must be understood according to the
332 rules of row-wise comparison (<a class="xref" href="functions-comparisons.html#ROW-WISE-COMPARISON" title="9.25.5. Row Constructor Comparison">Section 9.25.5</a>).
333 For example, given <code class="literal">PARTITION BY RANGE (x,y)</code>, a partition
334 bound <code class="literal">FROM (1, 2) TO (3, 4)</code>
335 allows <code class="literal">x=1</code> with any <code class="literal">y>=2</code>,
336 <code class="literal">x=2</code> with any non-null <code class="literal">y</code>,
337 and <code class="literal">x=3</code> with any <code class="literal">y<4</code>.
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 there
341 is no lower or upper bound on the column's value. For example, a
342 partition defined using <code class="literal">FROM (MINVALUE) TO (10)</code> allows
343 any values less than 10, and a partition defined using
344 <code class="literal">FROM (10) TO (MAXVALUE)</code> allows any values greater than
347 When creating a range partition involving more than one column, it
348 can also make sense to use <code class="literal">MAXVALUE</code> as part of the lower
349 bound, and <code class="literal">MINVALUE</code> as part of the upper bound. For
350 example, a partition defined using
351 <code class="literal">FROM (0, MAXVALUE) TO (10, MAXVALUE)</code> allows any rows
352 where the first partition key column is greater than 0 and less than
353 or equal to 10. Similarly, a partition defined using
354 <code class="literal">FROM ('a', MINVALUE) TO ('b', MINVALUE)</code> allows any rows
355 where the first partition key column starts with "a".
357 Note that if <code class="literal">MINVALUE</code> or <code class="literal">MAXVALUE</code> is used for
358 one column of a partitioning bound, the same value must be used for all
359 subsequent columns. For example, <code class="literal">(10, MINVALUE, 0)</code> is not
360 a valid bound; you should write <code class="literal">(10, MINVALUE, MINVALUE)</code>.
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 can
364 be stored. This is different from <code class="literal">MINVALUE</code> and
365 <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 any
368 other value, including "infinity" and <code class="literal">MINVALUE</code> as being
369 less than any other value, including "minus infinity". Thus the range
370 <code class="literal">FROM ('infinity') TO (MAXVALUE)</code> is not an empty range; it
371 allows precisely one value to be stored — "infinity".
373 If <code class="literal">DEFAULT</code> is specified, the table will be
374 created as the default partition of the parent table. This option
375 is not available for hash-partitioned tables. A partition key value
376 not fitting into any other partition of the given parent will be
377 routed to the default partition.
379 When a table has an existing <code class="literal">DEFAULT</code> partition and
380 a new partition is added to it, the default partition must
381 be scanned to verify that it does not contain any rows which properly
382 belong in the new partition. If the default partition contains a
383 large number of rows, this may be slow. The scan will be skipped if
384 the default partition is a foreign table or if it has a constraint which
385 proves that it cannot contain rows which should be placed in the new
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 a
390 non-negative integer less than the modulus. Typically, when initially
391 setting up a hash-partitioned table, you should choose a modulus equal to
392 the number of partitions and assign every table the same modulus and a
393 different remainder (see examples, below). However, it is not required
394 that every partition have the same modulus, only that every modulus which
395 occurs among the partitions of a hash-partitioned table is a factor of the
396 next larger modulus. This allows the number of partitions to be increased
397 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 which
399 has modulus 8, but find it necessary to increase the number of partitions
400 to 16. You can detach one of the modulus-8 partitions, create two new
401 modulus-16 partitions covering the same portion of the key space (one with
402 a remainder equal to the remainder of the detached partition, and the
403 other with a remainder equal to that value plus 8), and repopulate them
404 with data. You can then repeat this -- perhaps at a later time -- for
405 each modulus-8 partition until none remain. While this may still involve
406 a large amount of data movement at each step, it is still better than
407 having to create a whole new table and move all the data at once.
409 A partition must have the same column names and types as the partitioned
410 table to which it belongs. Modifications to the column names or types of
411 a partitioned table will automatically propagate to all partitions.
412 <code class="literal">CHECK</code> constraints will be inherited automatically by
413 every partition, but an individual partition may specify additional
414 <code class="literal">CHECK</code> constraints; additional constraints with the
415 same name and condition as in the parent will be merged with the parent
416 constraint. Defaults may be specified separately for each partition.
417 But note that a partition's default value is not applied when inserting
418 a tuple through a partitioned table.
420 Rows inserted into a partitioned table will be automatically routed to
421 the correct partition. If no suitable partition exists, an error will
424 Operations such as <code class="command">TRUNCATE</code>
425 which normally affect a table and all of its
426 inheritance children will cascade to all partitions, but may also be
427 performed on an individual partition.
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 the
431 parent partitioned table. Likewise, dropping a partition
432 with <code class="command">DROP TABLE</code> requires taking
433 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">ALTER
435 TABLE ATTACH/DETACH PARTITION</code></a> to perform these
436 operations with a weaker lock, thus reducing interference with
437 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 which
440 the new table automatically copies all column names, their data types,
441 and their not-null constraints.
443 Unlike <code class="literal">INHERITS</code>, the new table and original table
444 are completely decoupled after creation is complete. Changes to the
445 original table will not be applied to the new table, and it is not
446 possible to include data of the new table in scans of the original
449 Also unlike <code class="literal">INHERITS</code>, columns and
450 constraints copied by <code class="literal">LIKE</code> are not merged with similarly
451 named columns and constraints.
452 If the same name is specified explicitly or in another
453 <code class="literal">LIKE</code> clause, an error is signaled.
455 The optional <em class="replaceable"><code>like_option</code></em> clauses specify
456 which additional properties of the original table to copy. Specifying
457 <code class="literal">INCLUDING</code> copies the property, specifying
458 <code class="literal">EXCLUDING</code> omits the property.
459 <code class="literal">EXCLUDING</code> is the default. If multiple specifications
460 are made for the same kind of object, the last one is used. The
461 available options are:
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 be
465 copied. The default behavior is to exclude comments, resulting in
466 the copied columns and constraints in the new table having no
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 default
470 behavior is to exclude compression methods, resulting in columns
471 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 distinction
474 is made between column constraints and table constraints. Not-null
475 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 be
478 copied. Otherwise, default expressions are not copied, resulting in
479 the copied columns in the new table having null defaults. Note that
480 copying defaults that call database-modification functions, such as
481 <code class="function">nextval</code>, may create a functional linkage
482 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 as well as the stored/virtual choice of
485 copied column definitions will be copied. By default, new columns
486 will be regular base columns.
487 </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>
488 Any identity specifications of copied column definitions will be
489 copied. A new sequence is created for each identity column of the
490 new table, separate from the sequences associated with the old
492 </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>
493 Indexes, <code class="literal">PRIMARY KEY</code>, <code class="literal">UNIQUE</code>,
494 and <code class="literal">EXCLUDE</code> constraints on the original table
495 will be created on the new table. Names for the new indexes and
496 constraints are chosen according to the default rules, regardless of
497 how the originals were named. (This behavior avoids possible
498 duplicate-name failures for the new indexes.)
499 </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>
500 Extended statistics are copied to the new table.
501 </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>
502 <code class="literal">STORAGE</code> settings for the copied column
503 definitions will be copied. The default behavior is to exclude
504 <code class="literal">STORAGE</code> settings, resulting in the copied columns
505 in the new table having type-specific default settings. For more on
506 <code class="literal">STORAGE</code> settings, see <a class="xref" href="storage-toast.html" title="66.2. TOAST">Section 66.2</a>.
507 </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>
508 <code class="literal">INCLUDING ALL</code> is an abbreviated form selecting
509 all the available individual options. (It could be useful to write
510 individual <code class="literal">EXCLUDING</code> clauses after
511 <code class="literal">INCLUDING ALL</code> to select all but some specific
513 </p></dd></dl></div><p>
515 The <code class="literal">LIKE</code> clause can also be used to copy column
516 definitions from views, foreign tables, or composite types.
517 Inapplicable options (e.g., <code class="literal">INCLUDING INDEXES</code> from
519 </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>
520 An optional name for a column or table constraint. If the
521 constraint is violated, the constraint name is present in error messages,
522 so constraint names like <code class="literal">col must be positive</code> can be used
523 to communicate helpful constraint information to client applications.
524 (Double-quotes are needed to specify constraint names that contain spaces.)
525 If a constraint name is not specified, the system generates a name.
526 </p></dd><dt id="SQL-CREATETABLE-PARMS-NOT-NULL"><span class="term"><code class="literal">NOT NULL [ NO INHERIT ] </code></span> <a href="#SQL-CREATETABLE-PARMS-NOT-NULL" class="id_link">#</a></dt><dd><p>
527 The column is not allowed to contain null values.
529 A constraint marked with <code class="literal">NO INHERIT</code> will not propagate to
531 </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>
532 The column is allowed to contain null values. This is the default.
534 This clause is only provided for compatibility with
535 non-standard SQL databases. Its use is discouraged in new
537 </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>
538 The <code class="literal">CHECK</code> clause specifies an expression producing a
539 Boolean result which new or updated rows must satisfy for an
540 insert or update operation to succeed. Expressions evaluating
541 to TRUE or UNKNOWN succeed. Should any row of an insert or
542 update operation produce a FALSE result, an error exception is
543 raised and the insert or update does not alter the database. A
544 check constraint specified as a column constraint should
545 reference that column's value only, while an expression
546 appearing in a table constraint can reference multiple columns.
548 Currently, <code class="literal">CHECK</code> expressions cannot contain
549 subqueries nor refer to variables other than columns of the
550 current row (see <a class="xref" href="ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS" title="5.5.1. Check Constraints">Section 5.5.1</a>).
551 The system column <code class="literal">tableoid</code>
552 may be referenced, but not any other system column.
554 A constraint marked with <code class="literal">NO INHERIT</code> will not propagate to
557 When a table has multiple <code class="literal">CHECK</code> constraints,
558 they will be tested for each row in alphabetical order by name,
559 after checking <code class="literal">NOT NULL</code> constraints.
560 (<span class="productname">PostgreSQL</span> versions before 9.5 did not honor any
561 particular firing order for <code class="literal">CHECK</code> constraints.)
562 </p></dd><dt id="SQL-CREATETABLE-PARMS-DEFAULT"><span class="term"><code class="literal">DEFAULT
563 <em class="replaceable"><code>default_expr</code></em></code></span> <a href="#SQL-CREATETABLE-PARMS-DEFAULT" class="id_link">#</a></dt><dd><p>
564 The <code class="literal">DEFAULT</code> clause assigns a default data value for
565 the column whose column definition it appears within. The value
566 is any variable-free expression (in particular, cross-references
567 to other columns in the current table are not allowed). Subqueries
568 are not allowed either. The data type of the default expression must
569 match the data type of the column.
571 The default expression will be used in any insert operation that
572 does not specify a value for the column. If there is no default
573 for a column, then the default is null.
574 </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 | VIRTUAL ]</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>
575 This clause creates the column as a <em class="firstterm">generated
576 column</em>. The column cannot be written to, and when read the
577 result of the specified expression will be returned.
579 When <code class="literal">VIRTUAL</code> is specified, the column will be
580 computed when it is read, and it will not occupy any storage. When
581 <code class="literal">STORED</code> is specified, the column will be computed on
582 write and will be stored on disk. <code class="literal">VIRTUAL</code> is the
585 The generation expression can refer to other columns in the table, but
586 not other generated columns. Any functions and operators used must be
587 immutable. References to other tables are not allowed.
589 A virtual generated column cannot have a user-defined type, and the
590 generation expression of a virtual generated column must not reference
591 user-defined functions or types, that is, it can only use built-in
592 functions or types. This applies also indirectly, such as for functions
593 or types that underlie operators or casts. (This restriction does not
594 exist for stored generated columns.)
595 </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>
596 This clause creates the column as an <em class="firstterm">identity
597 column</em>. It will have an implicit sequence attached to it
598 and in newly-inserted rows the column will automatically have values
599 from the sequence assigned to it.
600 Such a column is implicitly <code class="literal">NOT NULL</code>.
602 The clauses <code class="literal">ALWAYS</code> and <code class="literal">BY DEFAULT</code>
603 determine how explicitly user-specified values are handled in
604 <code class="command">INSERT</code> and <code class="command">UPDATE</code> commands.
606 In an <code class="command">INSERT</code> command, if <code class="literal">ALWAYS</code> is
607 selected, a user-specified value is only accepted if the
608 <code class="command">INSERT</code> statement specifies <code class="literal">OVERRIDING SYSTEM
609 VALUE</code>. If <code class="literal">BY DEFAULT</code> is selected, then the
610 user-specified value takes precedence. See <a class="xref" href="sql-insert.html" title="INSERT"><span class="refentrytitle">INSERT</span></a>
611 for details. (In the <code class="command">COPY</code> command, user-specified
612 values are always used regardless of this setting.)
614 In an <code class="command">UPDATE</code> command, if <code class="literal">ALWAYS</code> is
615 selected, any update of the column to any value other than
616 <code class="literal">DEFAULT</code> will be rejected. If <code class="literal">BY
617 DEFAULT</code> is selected, the column can be updated normally.
618 (There is no <code class="literal">OVERRIDING</code> clause for the
619 <code class="command">UPDATE</code> command.)
621 The optional <em class="replaceable"><code>sequence_options</code></em> clause can
622 be used to override the parameters of the sequence. The available
623 options include those shown for <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>,
624 plus <code class="literal">SEQUENCE NAME <em class="replaceable"><code>name</code></em></code>,
625 <code class="literal">LOGGED</code>, and <code class="literal">UNLOGGED</code>, which
626 allow selection of the name and persistence level of the
627 sequence. Without <code class="literal">SEQUENCE NAME</code>, the system
628 chooses an unused name for the sequence.
629 Without <code class="literal">LOGGED</code> or <code class="literal">UNLOGGED</code>,
630 the sequence will have the same persistence level as the table.
631 </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> [, ... ] [, <em class="replaceable"><code>column_name</code></em> WITHOUT OVERLAPS ] )</code>
632 [<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>
633 The <code class="literal">UNIQUE</code> constraint specifies that a
634 group of one or more columns of a table can contain
635 only unique values. The behavior of a unique table constraint
636 is the same as that of a unique column constraint, with the
637 additional capability to span multiple columns. The constraint
638 therefore enforces that any two rows must differ in at least one
641 If the <code class="literal">WITHOUT OVERLAPS</code> option is specified for the
642 last column, then that column is checked for overlaps instead of
643 equality. In that case, the other columns of the constraint will allow
644 duplicates so long as the duplicates don't overlap in the
645 <code class="literal">WITHOUT OVERLAPS</code> column. (This is sometimes called a
646 temporal key, if the column is a range of dates or timestamps, but
647 <span class="productname">PostgreSQL</span> allows ranges over any base type.)
648 In effect, such a constraint is enforced with an <code class="literal">EXCLUDE</code>
649 constraint rather than a <code class="literal">UNIQUE</code> constraint. So for
650 example <code class="literal">UNIQUE (id, valid_at WITHOUT OVERLAPS)</code> behaves
651 like <code class="literal">EXCLUDE USING GIST (id WITH =, valid_at WITH
652 &&)</code>. The <code class="literal">WITHOUT OVERLAPS</code> column
653 must have a range or multirange type. Empty ranges/multiranges are
654 not permitted. The non-<code class="literal">WITHOUT OVERLAPS</code> columns of
655 the constraint can be any type that can be compared for equality in a
656 GiST index. By default, only range types are supported, but you can use
657 other types by adding the <a class="xref" href="btree-gist.html" title="F.8. btree_gist — GiST operator classes with B-tree behavior">btree_gist</a> extension (which
658 is the expected way to use this feature).
660 For the purpose of a unique constraint, null values are not
661 considered equal, unless <code class="literal">NULLS NOT DISTINCT</code> is
664 Each unique constraint should name a set of columns that is
665 different from the set of columns named by any other unique or
666 primary key constraint defined for the table. (Otherwise, redundant
667 unique constraints will be discarded.)
669 When establishing a unique constraint for a multi-level partition
670 hierarchy, all the columns in the partition key of the target
671 partitioned table, as well as those of all its descendant partitioned
672 tables, must be included in the constraint definition.
674 Adding a unique constraint will automatically create a unique btree
675 index on the column or group of columns used in the constraint. But if
676 the constraint includes a <code class="literal">WITHOUT OVERLAPS</code> clause, it
677 will use a GiST index. The created index has the same name as the
680 The optional <code class="literal">INCLUDE</code> clause adds to that index
681 one or more columns that are simply <span class="quote">“<span class="quote">payload</span>”</span>: uniqueness
682 is not enforced on them, and the index cannot be searched on the basis
683 of those columns. However they can be retrieved by an index-only scan.
684 Note that although the constraint is not enforced on included columns,
685 it still depends on them. Consequently, some operations on such columns
686 (e.g., <code class="literal">DROP COLUMN</code>) can cause cascaded constraint and
688 </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> [, ... ] [, <em class="replaceable"><code>column_name</code></em> WITHOUT OVERLAPS ] )</code>
689 [<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>
690 The <code class="literal">PRIMARY KEY</code> constraint specifies that a column or
691 columns of a table can contain only unique (non-duplicate), nonnull
692 values. Only one primary key can be specified for a table, whether as a
693 column constraint or a table constraint.
695 The primary key constraint should name a set of columns that is
696 different from the set of columns named by any unique
697 constraint defined for the same table. (Otherwise, the unique
698 constraint is redundant and will be discarded.)
700 <code class="literal">PRIMARY KEY</code> enforces the same data constraints as
701 a combination of <code class="literal">UNIQUE</code> and <code class="literal">NOT
702 NULL</code>. However,
703 identifying a set of columns as the primary key also provides metadata
704 about the design of the schema, since a primary key implies that other
705 tables can rely on this set of columns as a unique identifier for rows.
707 When placed on a partitioned table, <code class="literal">PRIMARY KEY</code>
708 constraints share the restrictions previously described
709 for <code class="literal">UNIQUE</code> constraints.
711 Adding a <code class="literal">PRIMARY KEY</code> constraint will automatically
712 create a unique btree index on the column or group of columns used in
713 the constraint, or GiST if <code class="literal">WITHOUT OVERLAPS</code> was
716 The optional <code class="literal">INCLUDE</code> clause adds to that index
717 one or more columns that are simply <span class="quote">“<span class="quote">payload</span>”</span>: uniqueness
718 is not enforced on them, and the index cannot be searched on the basis
719 of those columns. However they can be retrieved by an index-only scan.
720 Note that although the constraint is not enforced on included columns,
721 it still depends on them. Consequently, some operations on such columns
722 (e.g., <code class="literal">DROP COLUMN</code>) can cause cascaded constraint and
724 </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>
725 The <code class="literal">EXCLUDE</code> clause defines an exclusion
726 constraint, which guarantees that if
727 any two rows are compared on the specified column(s) or
728 expression(s) using the specified operator(s), not all of these
729 comparisons will return <code class="literal">TRUE</code>. If all of the
730 specified operators test for equality, this is equivalent to a
731 <code class="literal">UNIQUE</code> constraint, although an ordinary unique constraint
732 will be faster. However, exclusion constraints can specify
733 constraints that are more general than simple equality.
734 For example, you can specify a constraint that
735 no two rows in the table contain overlapping circles
736 (see <a class="xref" href="datatype-geometric.html" title="8.8. Geometric Types">Section 8.8</a>) by using the
737 <code class="literal">&&</code> operator.
738 The operator(s) are required to be commutative.
740 Exclusion constraints are implemented using
741 an index that has the same name as the constraint, so each specified
742 operator must be associated with an appropriate operator class
743 (see <a class="xref" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families">Section 11.10</a>) for the index access
744 method <em class="replaceable"><code>index_method</code></em>.
745 Each <em class="replaceable"><code>exclude_element</code></em>
746 defines a column of the index, so it can optionally specify a collation,
747 an operator class, operator class parameters, and/or ordering options;
748 these are described fully under <a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a>.
750 The access method must support <code class="literal">amgettuple</code> (see <a class="xref" href="indexam.html" title="Chapter 63. Index Access Method Interface Definition">Chapter 63</a>); at present this means <acronym class="acronym">GIN</acronym>
751 cannot be used. Although it's allowed, there is little point in using
752 B-tree or hash indexes with an exclusion constraint, because this
753 does nothing that an ordinary unique constraint doesn't do better.
754 So in practice the access method will always be <acronym class="acronym">GiST</acronym> or
755 <acronym class="acronym">SP-GiST</acronym>.
757 The <em class="replaceable"><code>predicate</code></em> allows you to specify an
758 exclusion constraint on a subset of the table; internally this creates a
759 partial index. Note that parentheses are required around the predicate.
761 When establishing an exclusion constraint for a multi-level partition
762 hierarchy, all the columns in the partition key of the target
763 partitioned table, as well as those of all its descendant partitioned
764 tables, must be included in the constraint definition. Additionally,
765 those columns must be compared using the equality operator. These
766 restrictions ensure that potentially-conflicting rows will exist in the
767 same partition. The constraint may also refer to other columns which
768 are not a part of any partition key, which can be compared using any
769 appropriate operator.
770 </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> [, ... ] [, PERIOD <em class="replaceable"><code>column_name</code></em> ] )
771 REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] [, PERIOD <em class="replaceable"><code>refcolumn</code></em> ] ) ]
772 [ MATCH <em class="replaceable"><code>matchtype</code></em> ]
773 [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ]
774 [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ]</code>
775 (table constraint)</span> <a href="#SQL-CREATETABLE-PARMS-REFERENCES" class="id_link">#</a></dt><dd><p>
776 These clauses specify a foreign key constraint, which requires
777 that a group of one or more columns of the new table must only
778 contain values that match values in the referenced
779 column(s) of some row of the referenced table. If the <em class="replaceable"><code>refcolumn</code></em> list is omitted, the
780 primary key of the <em class="replaceable"><code>reftable</code></em>
781 is used. Otherwise, the <em class="replaceable"><code>refcolumn</code></em>
782 list must refer to the columns of a non-deferrable unique or primary key
783 constraint or be the columns of a non-partial unique index.
785 If the last column is marked with <code class="literal">PERIOD</code>, it is
786 treated in a special way. While the non-<code class="literal">PERIOD</code>
787 columns are compared for equality (and there must be at least one of
788 them), the <code class="literal">PERIOD</code> column is not. Instead, the
789 constraint is considered satisfied if the referenced table has matching
790 records (based on the non-<code class="literal">PERIOD</code> parts of the key)
791 whose combined <code class="literal">PERIOD</code> values completely cover the
792 referencing record's. In other words, the reference must have a
793 referent for its entire duration. This column must be a range or
794 multirange type. In addition, the referenced table must have a primary
795 key or unique constraint declared with <code class="literal">WITHOUT
796 OVERLAPS</code>. Finally, if the foreign key has a PERIOD
797 <em class="replaceable"><code>column_name</code></em> specification
798 the corresponding <em class="replaceable"><code>refcolumn</code></em>,
799 if present, must also be marked <code class="literal">PERIOD</code>. If the
800 <em class="replaceable"><code>refcolumn</code></em> clause is omitted,
801 and thus the reftable's primary key constraint chosen, the primary key
802 must have its final column marked <code class="literal">WITHOUT OVERLAPS</code>.
804 For each pair of referencing and referenced column, if they are of a
805 collatable data type, then the collations must either be both
806 deterministic or else both the same. This ensures that both columns
807 have a consistent notion of equality.
810 must have <code class="literal">REFERENCES</code> permission on the referenced
811 table (either the whole table, or the specific referenced columns). The
812 addition of a foreign key constraint requires a
813 <code class="literal">SHARE ROW EXCLUSIVE</code> lock on the referenced table.
814 Note that foreign key constraints cannot be defined between temporary
815 tables and permanent tables.
817 A value inserted into the referencing column(s) is matched against the
818 values of the referenced table and referenced columns using the
819 given match type. There are three match types: <code class="literal">MATCH
820 FULL</code>, <code class="literal">MATCH PARTIAL</code>, and <code class="literal">MATCH
821 SIMPLE</code> (which is the default). <code class="literal">MATCH
822 FULL</code> will not allow one column of a multicolumn foreign key
823 to be null unless all foreign key columns are null; if they are all
824 null, the row is not required to have a match in the referenced table.
825 <code class="literal">MATCH SIMPLE</code> allows any of the foreign key columns
826 to be null; if any of them are null, the row is not required to have a
827 match in the referenced table.
828 <code class="literal">MATCH PARTIAL</code> is not yet implemented.
829 (Of course, <code class="literal">NOT NULL</code> constraints can be applied to the
830 referencing column(s) to prevent these cases from arising.)
832 In addition, when the data in the referenced columns is changed,
833 certain actions are performed on the data in this table's
834 columns. The <code class="literal">ON DELETE</code> clause specifies the
835 action to perform when a referenced row in the referenced table is
836 being deleted. Likewise, the <code class="literal">ON UPDATE</code>
837 clause specifies the action to perform when a referenced column
838 in the referenced table is being updated to a new value. If the
839 row is updated, but the referenced column is not actually
840 changed, no action is done. Referential actions are executed as part of
841 the data changing command, even if the constraint is deferred. There
842 are the following possible actions for each clause:
844 </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>
845 Produce an error if the deletion or update
846 would create a foreign key constraint violation.
847 If the constraint is deferred, this
848 error will be produced at constraint check time if there still
849 exist any referencing rows. This is the default action.
850 </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>
851 Produce an error if a row to be deleted or updated matches a row in
852 the referencing table. This prevents the action even if the state
853 after the action would not violate the foreign key constraint. In
854 particular, it prevents updates of referenced rows to values that
855 are distinct but compare as equal. (But it does not prevent
856 <span class="quote">“<span class="quote">no-op</span>”</span> updates that update a column to the same
859 In a temporal foreign key, this option is not supported.
860 </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>
861 Delete any rows referencing the deleted row, or update the
862 values of the referencing column(s) to the new values of the
863 referenced columns, respectively.
865 In a temporal foreign key, this option is not supported.
866 </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>
867 Set all of the referencing columns, or a specified subset of the
868 referencing columns, to null. A subset of columns can only be
869 specified for <code class="literal">ON DELETE</code> actions.
871 In a temporal foreign key, this option is not supported.
872 </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>
873 Set all of the referencing columns, or a specified subset of the
874 referencing columns, to their default values. A subset of columns
875 can only be specified for <code class="literal">ON DELETE</code> actions.
876 (There must be a row in the referenced table matching the default
877 values, if they are not null, or the operation will fail.)
879 In a temporal foreign key, this option is not supported.
880 </p></dd></dl></div><p>
882 If the referenced column(s) are changed frequently, it might be wise to
883 add an index to the referencing column(s) so that referential actions
884 associated with the foreign key constraint can be performed more
886 </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>
887 This controls whether the constraint can be deferred. A
888 constraint that is not deferrable will be checked immediately
889 after every command. Checking of constraints that are
890 deferrable can be postponed until the end of the transaction
891 (using the <a class="link" href="sql-set-constraints.html" title="SET CONSTRAINTS"><code class="command">SET CONSTRAINTS</code></a> command).
892 <code class="literal">NOT DEFERRABLE</code> is the default.
893 Currently, only <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>,
894 <code class="literal">EXCLUDE</code>, and
895 <code class="literal">REFERENCES</code> (foreign key) constraints accept this
896 clause. <code class="literal">NOT NULL</code> and <code class="literal">CHECK</code> constraints are not
897 deferrable. Note that deferrable constraints cannot be used as
898 conflict arbitrators in an <code class="command">INSERT</code> statement that
899 includes an <code class="literal">ON CONFLICT DO UPDATE</code> clause.
900 </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>
901 If a constraint is deferrable, this clause specifies the default
902 time to check the constraint. If the constraint is
903 <code class="literal">INITIALLY IMMEDIATE</code>, it is checked after each
904 statement. This is the default. If the constraint is
905 <code class="literal">INITIALLY DEFERRED</code>, it is checked only at the
906 end of the transaction. The constraint check time can be
907 altered with the <a class="link" href="sql-set-constraints.html" title="SET CONSTRAINTS"><code class="command">SET CONSTRAINTS</code></a> command.
908 </p></dd><dt id="SQL-CREATETABLE-PARMS-ENFORCED"><span class="term"><code class="literal">ENFORCED</code><br /></span><span class="term"><code class="literal">NOT ENFORCED</code></span> <a href="#SQL-CREATETABLE-PARMS-ENFORCED" class="id_link">#</a></dt><dd><p>
909 When the constraint is <code class="literal">ENFORCED</code>, then the database
910 system will ensure that the constraint is satisfied, by checking the
911 constraint at appropriate times (after each statement or at the end of
912 the transaction, as appropriate). That is the default. If the
913 constraint is <code class="literal">NOT ENFORCED</code>, the database system will
914 not check the constraint. It is then up to the application code to
915 ensure that the constraints are satisfied. The database system might
916 still assume that the data actually satisfies the constraint for
917 optimization decisions where this does not affect the correctness of the
920 <code class="literal">NOT ENFORCED</code> constraints can be useful as
921 documentation if the actual checking of the constraint at run time is
924 This is currently only supported for foreign key and <code class="literal">CHECK</code>
926 </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>
927 This optional clause specifies the table access method to use to store
928 the contents for the new table; the method needs be an access method of
929 type <code class="literal">TABLE</code>. See <a class="xref" href="tableam.html" title="Chapter 62. Table Access Method Interface Definition">Chapter 62</a> for more
930 information. If this option is not specified, the default table access
931 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.
933 When creating a partition, the table access method is the access method
934 of its partitioned table, if set.
935 </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>
936 This clause specifies optional storage parameters for a table or index;
937 see <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS" title="Storage Parameters">Storage Parameters</a> below for more
938 information. For backward-compatibility the <code class="literal">WITH</code>
939 clause for a table can also include <code class="literal">OIDS=FALSE</code> to
940 specify that rows of the new table should not contain OIDs (object
941 identifiers), <code class="literal">OIDS=TRUE</code> is not supported anymore.
942 </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>
943 This is backward-compatible syntax for declaring a table
944 <code class="literal">WITHOUT OIDS</code>, creating a table <code class="literal">WITH
945 OIDS</code> is not supported anymore.
946 </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>
947 The behavior of temporary tables at the end of a transaction
948 block can be controlled using <code class="literal">ON COMMIT</code>.
949 The three options are:
951 </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>
952 No special action is taken at the ends of transactions.
953 This is the default behavior.
954 </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>
955 All rows in the temporary table will be deleted at the end
956 of each transaction block. Essentially, an automatic <a class="link" href="sql-truncate.html" title="TRUNCATE"><code class="command">TRUNCATE</code></a> is done
957 at each commit. When used on a partitioned table, this
958 is not cascaded to its partitions.
959 </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>
960 The temporary table will be dropped at the end of the current
961 transaction block. When used on a partitioned table, this action
962 drops its partitions and when used on tables with inheritance
963 children, it drops the dependent children.
964 </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>
965 The <em class="replaceable"><code>tablespace_name</code></em> is the name
966 of the tablespace in which the new table is to be created.
968 <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLESPACE">default_tablespace</a> is consulted, or
969 <a class="xref" href="runtime-config-client.html#GUC-TEMP-TABLESPACES">temp_tablespaces</a> if the table is temporary. For
970 partitioned tables, since no storage is required for the table itself,
971 the tablespace specified overrides <code class="literal">default_tablespace</code>
972 as the default tablespace to use for any newly created partitions when no
973 other tablespace is explicitly specified.
974 </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>
975 This clause allows selection of the tablespace in which the index
976 associated with a <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY
977 KEY</code>, or <code class="literal">EXCLUDE</code> constraint will be created.
979 <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLESPACE">default_tablespace</a> is consulted, or
980 <a class="xref" href="runtime-config-client.html#GUC-TEMP-TABLESPACES">temp_tablespaces</a> if the table is temporary.
981 </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>
982 The <code class="literal">WITH</code> clause can specify <em class="firstterm">storage parameters</em>
983 for tables, and for indexes associated with a <code class="literal">UNIQUE</code>,
984 <code class="literal">PRIMARY KEY</code>, or <code class="literal">EXCLUDE</code> constraint.
985 Storage parameters for
986 indexes are documented in <a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a>.
987 The storage parameters currently
988 available for tables are listed below. For many of these parameters, as
989 shown, there is an additional parameter with the same name prefixed with
990 <code class="literal">toast.</code>, which controls the behavior of the
991 table's secondary <acronym class="acronym">TOAST</acronym> table, if any
992 (see <a class="xref" href="storage-toast.html" title="66.2. TOAST">Section 66.2</a> for more information about TOAST).
993 If a table parameter value is set and the
994 equivalent <code class="literal">toast.</code> parameter is not, the TOAST table
995 will use the table's parameter value.
996 Specifying these parameters for partitioned tables is not supported,
997 but you may specify them for individual leaf partitions.
998 </p><div class="variablelist"><dl class="variablelist"><dt id="RELOPTION-FILLFACTOR"><span class="term"><code class="varname">fillfactor</code> (<code class="type">integer</code>)
999 <a id="id-1.9.3.85.6.3.4.1.1.3" class="indexterm"></a>
1000 </span> <a href="#RELOPTION-FILLFACTOR" class="id_link">#</a></dt><dd><p>
1001 The fillfactor for a table is a percentage between 10 and 100.
1002 100 (complete packing) is the default. When a smaller fillfactor
1003 is specified, <code class="command">INSERT</code> operations pack table pages only
1004 to the indicated percentage; the remaining space on each page is
1005 reserved for updating rows on that page. This gives <code class="command">UPDATE</code>
1006 a chance to place the updated copy of a row on the same page as the
1007 original, which is more efficient than placing it on a different
1008 page, and makes <a class="link" href="storage-hot.html" title="66.7. Heap-Only Tuples (HOT)">heap-only tuple
1009 updates</a> more likely.
1010 For a table whose entries are never updated, complete packing is the
1011 best choice, but in heavily updated tables smaller fillfactors are
1012 appropriate. This parameter cannot be set for TOAST tables.
1013 </p></dd><dt id="RELOPTION-TOAST-TUPLE-TARGET"><span class="term"><code class="literal">toast_tuple_target</code> (<code class="type">integer</code>)
1014 <a id="id-1.9.3.85.6.3.4.2.1.3" class="indexterm"></a>
1015 </span> <a href="#RELOPTION-TOAST-TUPLE-TARGET" class="id_link">#</a></dt><dd><p>
1016 The toast_tuple_target specifies the minimum tuple length required before
1017 we try to compress and/or move long column values into TOAST tables, and
1018 is also the target length we try to reduce the length below once toasting
1019 begins. This affects columns marked as External (for move),
1020 Main (for compression), or Extended (for both) and applies only to new
1021 tuples. There is no effect on existing rows.
1022 By default this parameter is set to allow at least 4 tuples per block,
1023 which with the default block size will be 2040 bytes. Valid values are
1024 between 128 bytes and the (block size - header), by default 8160 bytes.
1025 Changing this value may not be useful for very short or very long rows.
1026 Note that the default setting is often close to optimal, and
1027 it is possible that setting this parameter could have negative
1028 effects in some cases.
1029 This parameter cannot be set for TOAST tables.
1030 </p></dd><dt id="RELOPTION-PARALLEL-WORKERS"><span class="term"><code class="literal">parallel_workers</code> (<code class="type">integer</code>)
1031 <a id="id-1.9.3.85.6.3.4.3.1.3" class="indexterm"></a>
1032 </span> <a href="#RELOPTION-PARALLEL-WORKERS" class="id_link">#</a></dt><dd><p>
1033 This sets the number of workers that should be used to assist a parallel
1034 scan of this table. If not set, the system will determine a value based
1035 on the relation size. The actual number of workers chosen by the planner
1036 or by utility statements that use parallel scans may be less, for example
1037 due to the setting of <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>.
1038 </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>)
1039 <a id="id-1.9.3.85.6.3.4.4.1.4" class="indexterm"></a>
1040 </span> <a href="#RELOPTION-AUTOVACUUM-ENABLED" class="id_link">#</a></dt><dd><p>
1041 Enables or disables the autovacuum daemon for a particular table.
1042 If true, the autovacuum daemon will perform automatic <code class="command">VACUUM</code>
1043 and/or <code class="command">ANALYZE</code> operations on this table following the rules
1044 discussed in <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a>.
1045 If false, this table will not be autovacuumed, except to prevent
1046 transaction ID wraparound. See <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a> for
1047 more about wraparound prevention.
1048 Note that the autovacuum daemon does not run at all (except to prevent
1049 transaction ID wraparound) if the <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM">autovacuum</a>
1050 parameter is false; setting individual tables' storage parameters does
1051 not override that. Therefore there is seldom much point in explicitly
1052 setting this storage parameter to <code class="literal">true</code>, only
1053 to <code class="literal">false</code>.
1054 </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>)
1055 <a id="id-1.9.3.85.6.3.4.5.1.4" class="indexterm"></a>
1056 </span> <a href="#RELOPTION-VACUUM-INDEX-CLEANUP" class="id_link">#</a></dt><dd><p>
1057 Forces or disables index cleanup when <code class="command">VACUUM</code>
1058 is run on this table. The default value is
1059 <code class="literal">AUTO</code>. With <code class="literal">OFF</code>, index
1060 cleanup is disabled, with <code class="literal">ON</code> it is enabled,
1061 and with <code class="literal">AUTO</code> a decision is made dynamically,
1062 each time <code class="command">VACUUM</code> runs. The dynamic behavior
1063 allows <code class="command">VACUUM</code> to avoid needlessly scanning
1064 indexes to remove very few dead tuples. Forcibly disabling all
1065 index cleanup can speed up <code class="command">VACUUM</code> very
1066 significantly, but may also lead to severely bloated indexes if
1067 table modifications are frequent. The
1068 <code class="literal">INDEX_CLEANUP</code> parameter of <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a>, if
1069 specified, overrides the value of this option.
1070 </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>)
1071 <a id="id-1.9.3.85.6.3.4.6.1.4" class="indexterm"></a>
1072 </span> <a href="#RELOPTION-VACUUM-TRUNCATE" class="id_link">#</a></dt><dd><p>
1073 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-TRUNCATE">vacuum_truncate</a> parameter. The
1074 <code class="literal">TRUNCATE</code> parameter of
1075 <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a>, if
1076 specified, overrides the value of this option.
1077 </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>)
1078 <a id="id-1.9.3.85.6.3.4.7.1.4" class="indexterm"></a>
1079 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-THRESHOLD" class="id_link">#</a></dt><dd><p>
1080 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD">autovacuum_vacuum_threshold</a>
1082 </p></dd><dt id="RELOPTION-AUTOVACUUM-VACUUM-MAX-THRESHOLD"><span class="term"><code class="literal">autovacuum_vacuum_max_threshold</code>, <code class="literal">toast.autovacuum_vacuum_max_threshold</code> (<code class="type">integer</code>)
1083 <a id="id-1.9.3.85.6.3.4.8.1.4" class="indexterm"></a>
1084 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-MAX-THRESHOLD" class="id_link">#</a></dt><dd><p>
1085 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-MAX-THRESHOLD">autovacuum_vacuum_max_threshold</a>
1087 </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>)
1088 <a id="id-1.9.3.85.6.3.4.9.1.4" class="indexterm"></a>
1089 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>
1090 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR">autovacuum_vacuum_scale_factor</a>
1092 </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>)
1093 <a id="id-1.9.3.85.6.3.4.10.1.4" class="indexterm"></a>
1094 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-INSERT-THRESHOLD" class="id_link">#</a></dt><dd><p>
1095 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD">autovacuum_vacuum_insert_threshold</a>
1096 parameter. The special value of -1 may be used to disable insert vacuums on the table.
1097 </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>)
1098 <a id="id-1.9.3.85.6.3.4.11.1.4" class="indexterm"></a>
1099 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>
1100 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR">autovacuum_vacuum_insert_scale_factor</a>
1102 </p></dd><dt id="RELOPTION-AUTOVACUUM-ANALYZE-THRESHOLD"><span class="term"><code class="literal">autovacuum_analyze_threshold</code> (<code class="type">integer</code>)
1103 <a id="id-1.9.3.85.6.3.4.12.1.3" class="indexterm"></a>
1104 </span> <a href="#RELOPTION-AUTOVACUUM-ANALYZE-THRESHOLD" class="id_link">#</a></dt><dd><p>
1105 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD">autovacuum_analyze_threshold</a>
1107 </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>)
1108 <a id="id-1.9.3.85.6.3.4.13.1.3" class="indexterm"></a>
1109 </span> <a href="#RELOPTION-AUTOVACUUM-ANALYZE-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>
1110 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR">autovacuum_analyze_scale_factor</a>
1112 </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>)
1113 <a id="id-1.9.3.85.6.3.4.14.1.4" class="indexterm"></a>
1114 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-COST-DELAY" class="id_link">#</a></dt><dd><p>
1115 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY">autovacuum_vacuum_cost_delay</a>
1117 </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>)
1118 <a id="id-1.9.3.85.6.3.4.15.1.4" class="indexterm"></a>
1119 </span> <a href="#RELOPTION-AUTOVACUUM-VACUUM-COST-LIMIT" class="id_link">#</a></dt><dd><p>
1120 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT">autovacuum_vacuum_cost_limit</a>
1122 </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>)
1123 <a id="id-1.9.3.85.6.3.4.16.1.4" class="indexterm"></a>
1124 </span> <a href="#RELOPTION-AUTOVACUUM-FREEZE-MIN-AGE" class="id_link">#</a></dt><dd><p>
1125 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-FREEZE-MIN-AGE">vacuum_freeze_min_age</a>
1126 parameter. Note that autovacuum will ignore
1127 per-table <code class="literal">autovacuum_freeze_min_age</code> parameters that are
1128 larger than half the
1129 system-wide <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a> setting.
1130 </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>)
1131 <a id="id-1.9.3.85.6.3.4.17.1.4" class="indexterm"></a>
1132 </span> <a href="#RELOPTION-AUTOVACUUM-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>
1133 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a>
1134 parameter. Note that autovacuum will ignore
1135 per-table <code class="literal">autovacuum_freeze_max_age</code> parameters that are
1136 larger than the system-wide setting (it can only be set smaller).
1137 </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>)
1138 <a id="id-1.9.3.85.6.3.4.18.1.4" class="indexterm"></a>
1139 </span> <a href="#RELOPTION-AUTOVACUUM-FREEZE-TABLE-AGE" class="id_link">#</a></dt><dd><p>
1140 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-FREEZE-TABLE-AGE">vacuum_freeze_table_age</a>
1142 </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>)
1143 <a id="id-1.9.3.85.6.3.4.19.1.4" class="indexterm"></a>
1144 </span> <a href="#RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-MIN-AGE" class="id_link">#</a></dt><dd><p>
1145 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-MULTIXACT-FREEZE-MIN-AGE">vacuum_multixact_freeze_min_age</a>
1146 parameter. Note that autovacuum will ignore
1147 per-table <code class="literal">autovacuum_multixact_freeze_min_age</code> parameters
1148 that are larger than half the
1149 system-wide <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a>
1151 </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>)
1152 <a id="id-1.9.3.85.6.3.4.20.1.4" class="indexterm"></a>
1153 </span> <a href="#RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>
1155 for <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a> parameter.
1156 Note that autovacuum will ignore
1157 per-table <code class="literal">autovacuum_multixact_freeze_max_age</code> parameters
1158 that are larger than the system-wide setting (it can only be set
1160 </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>)
1161 <a id="id-1.9.3.85.6.3.4.21.1.4" class="indexterm"></a>
1162 </span> <a href="#RELOPTION-AUTOVACUUM-MULTIXACT-FREEZE-TABLE-AGE" class="id_link">#</a></dt><dd><p>
1164 for <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-MULTIXACT-FREEZE-TABLE-AGE">vacuum_multixact_freeze_table_age</a> parameter.
1165 </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>)
1166 <a id="id-1.9.3.85.6.3.4.22.1.4" class="indexterm"></a>
1167 </span> <a href="#RELOPTION-LOG-AUTOVACUUM-MIN-DURATION" class="id_link">#</a></dt><dd><p>
1168 Per-table value for <a class="xref" href="runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION">log_autovacuum_min_duration</a>
1170 </p></dd><dt id="RELOPTION-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE"><span class="term"><code class="literal">vacuum_max_eager_freeze_failure_rate</code>, <code class="literal">toast.vacuum_max_eager_freeze_failure_rate</code> (<code class="type">floating point</code>)
1171 <a id="id-1.9.3.85.6.3.4.23.1.4" class="indexterm"></a>
1172 </span> <a href="#RELOPTION-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE" class="id_link">#</a></dt><dd><p>
1173 Per-table value for <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE">vacuum_max_eager_freeze_failure_rate</a>
1175 </p></dd><dt id="RELOPTION-USER-CATALOG-TABLE"><span class="term"><code class="literal">user_catalog_table</code> (<code class="type">boolean</code>)
1176 <a id="id-1.9.3.85.6.3.4.24.1.3" class="indexterm"></a>
1177 </span> <a href="#RELOPTION-USER-CATALOG-TABLE" class="id_link">#</a></dt><dd><p>
1178 Declare the table as an additional catalog table for purposes of
1179 logical replication. See
1180 <a class="xref" href="logicaldecoding-output-plugin.html#LOGICALDECODING-CAPABILITIES" title="47.6.2. Capabilities">Section 47.6.2</a> for details.
1181 This parameter cannot be set for TOAST tables.
1182 </p></dd></dl></div></div></div><div class="refsect1" id="SQL-CREATETABLE-NOTES"><h2>Notes</h2><p>
1183 <span class="productname">PostgreSQL</span> automatically creates an
1184 index for each unique constraint and primary key constraint to
1185 enforce uniqueness. Thus, it is not necessary to create an
1186 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.)
1188 Unique constraints and primary keys are not inherited in the
1189 current implementation. This makes the combination of
1190 inheritance and unique constraints rather dysfunctional.
1192 A table cannot have more than 1600 columns. (In practice, the
1193 effective limit is usually lower because of tuple-length constraints.)
1194 </p></div><div class="refsect1" id="SQL-CREATETABLE-EXAMPLES"><h2>Examples</h2><p>
1195 Create table <code class="structname">films</code> and table
1196 <code class="structname">distributors</code>:
1198 </p><pre class="programlisting">
1199 CREATE TABLE films (
1200 code char(5) CONSTRAINT firstkey PRIMARY KEY,
1201 title varchar(40) NOT NULL,
1202 did integer NOT NULL,
1205 len interval hour to minute
1208 CREATE TABLE distributors (
1209 did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1210 name varchar(40) NOT NULL CHECK (name <> '')
1214 Create a table with a 2-dimensional array:
1216 </p><pre class="programlisting">
1217 CREATE TABLE array_int (
1222 Define a unique table constraint for the table
1223 <code class="literal">films</code>. Unique table constraints can be defined
1224 on one or more columns of the table:
1226 </p><pre class="programlisting">
1227 CREATE TABLE films (
1233 len interval hour to minute,
1234 CONSTRAINT production UNIQUE(date_prod)
1238 Define a check column constraint:
1240 </p><pre class="programlisting">
1241 CREATE TABLE distributors (
1242 did integer CHECK (did > 100),
1247 Define a check table constraint:
1249 </p><pre class="programlisting">
1250 CREATE TABLE distributors (
1253 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
1257 Define a primary key table constraint for the table
1258 <code class="structname">films</code>:
1260 </p><pre class="programlisting">
1261 CREATE TABLE films (
1267 len interval hour to minute,
1268 CONSTRAINT code_title PRIMARY KEY(code,title)
1272 Define a primary key constraint for table
1273 <code class="structname">distributors</code>. The following two examples are
1274 equivalent, the first using the table constraint syntax, the second
1275 the column constraint syntax:
1277 </p><pre class="programlisting">
1278 CREATE TABLE distributors (
1284 CREATE TABLE distributors (
1285 did integer PRIMARY KEY,
1290 Assign a literal constant default value for the column
1291 <code class="literal">name</code>, arrange for the default value of column
1292 <code class="literal">did</code> to be generated by selecting the next value
1293 of a sequence object, and make the default value of
1294 <code class="literal">modtime</code> be the time at which the row is
1297 </p><pre class="programlisting">
1298 CREATE TABLE distributors (
1299 name varchar(40) DEFAULT 'Luso Films',
1300 did integer DEFAULT nextval('distributors_serial'),
1301 modtime timestamp DEFAULT current_timestamp
1305 Define two <code class="literal">NOT NULL</code> column constraints on the table
1306 <code class="classname">distributors</code>, one of which is explicitly
1309 </p><pre class="programlisting">
1310 CREATE TABLE distributors (
1311 did integer CONSTRAINT no_null NOT NULL,
1312 name varchar(40) NOT NULL
1316 Define a unique constraint for the <code class="literal">name</code> column:
1318 </p><pre class="programlisting">
1319 CREATE TABLE distributors (
1321 name varchar(40) UNIQUE
1325 The same, specified as a table constraint:
1327 </p><pre class="programlisting">
1328 CREATE TABLE distributors (
1335 Create the same table, specifying 70% fill factor for both the table
1336 and its unique index:
1338 </p><pre class="programlisting">
1339 CREATE TABLE distributors (
1342 UNIQUE(name) WITH (fillfactor=70)
1344 WITH (fillfactor=70);
1347 Create table <code class="structname">circles</code> with an exclusion
1348 constraint that prevents any two circles from overlapping:
1350 </p><pre class="programlisting">
1351 CREATE TABLE circles (
1353 EXCLUDE USING gist (c WITH &&)
1357 Create table <code class="structname">cinemas</code> in tablespace <code class="structname">diskvol1</code>:
1359 </p><pre class="programlisting">
1360 CREATE TABLE cinemas (
1364 ) TABLESPACE diskvol1;
1367 Create a composite type and a typed table:
1368 </p><pre class="programlisting">
1369 CREATE TYPE employee_type AS (name text, salary numeric);
1371 CREATE TABLE employees OF employee_type (
1373 salary WITH OPTIONS DEFAULT 1000
1376 Create a range partitioned table:
1377 </p><pre class="programlisting">
1378 CREATE TABLE measurement (
1379 logdate date not null,
1382 ) PARTITION BY RANGE (logdate);
1384 Create a range partitioned table with multiple columns in the partition key:
1385 </p><pre class="programlisting">
1386 CREATE TABLE measurement_year_month (
1387 logdate date not null,
1390 ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
1392 Create a list partitioned table:
1393 </p><pre class="programlisting">
1394 CREATE TABLE cities (
1395 city_id bigserial not null,
1398 ) PARTITION BY LIST (left(lower(name), 1));
1400 Create a hash partitioned table:
1401 </p><pre class="programlisting">
1402 CREATE TABLE orders (
1403 order_id bigint not null,
1404 cust_id bigint not null,
1406 ) PARTITION BY HASH (order_id);
1408 Create partition of a range partitioned table:
1409 </p><pre class="programlisting">
1410 CREATE TABLE measurement_y2016m07
1411 PARTITION OF measurement (
1413 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1415 Create a few partitions of a range partitioned table with multiple
1416 columns in the partition key:
1417 </p><pre class="programlisting">
1418 CREATE TABLE measurement_ym_older
1419 PARTITION OF measurement_year_month
1420 FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
1422 CREATE TABLE measurement_ym_y2016m11
1423 PARTITION OF measurement_year_month
1424 FOR VALUES FROM (2016, 11) TO (2016, 12);
1426 CREATE TABLE measurement_ym_y2016m12
1427 PARTITION OF measurement_year_month
1428 FOR VALUES FROM (2016, 12) TO (2017, 01);
1430 CREATE TABLE measurement_ym_y2017m01
1431 PARTITION OF measurement_year_month
1432 FOR VALUES FROM (2017, 01) TO (2017, 02);
1434 Create partition of a list partitioned table:
1435 </p><pre class="programlisting">
1436 CREATE TABLE cities_ab
1437 PARTITION OF cities (
1438 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1439 ) FOR VALUES IN ('a', 'b');
1441 Create partition of a list partitioned table that is itself further
1442 partitioned and then add a partition to it:
1443 </p><pre class="programlisting">
1444 CREATE TABLE cities_ab
1445 PARTITION OF cities (
1446 CONSTRAINT city_id_nonzero CHECK (city_id != 0)
1447 ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
1449 CREATE TABLE cities_ab_10000_to_100000
1450 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
1452 Create partitions of a hash partitioned table:
1453 </p><pre class="programlisting">
1454 CREATE TABLE orders_p1 PARTITION OF orders
1455 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1456 CREATE TABLE orders_p2 PARTITION OF orders
1457 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1458 CREATE TABLE orders_p3 PARTITION OF orders
1459 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1460 CREATE TABLE orders_p4 PARTITION OF orders
1461 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1463 Create a default partition:
1464 </p><pre class="programlisting">
1465 CREATE TABLE cities_partdef
1466 PARTITION OF cities DEFAULT;
1467 </pre></div><div class="refsect1" id="SQL-CREATETABLE-COMPATIBILITY"><h2>Compatibility</h2><p>
1468 The <code class="command">CREATE TABLE</code> command conforms to the
1469 <acronym class="acronym">SQL</acronym> standard, with exceptions listed below.
1470 </p><div class="refsect2" id="id-1.9.3.85.9.3"><h3>Temporary Tables</h3><p>
1471 Although the syntax of <code class="literal">CREATE TEMPORARY TABLE</code>
1472 resembles that of the SQL standard, the effect is not the same. In the
1474 temporary tables are defined just once and automatically exist (starting
1475 with empty contents) in every session that needs them.
1476 <span class="productname">PostgreSQL</span> instead
1477 requires each session to issue its own <code class="literal">CREATE TEMPORARY
1478 TABLE</code> command for each temporary table to be used. This allows
1479 different sessions to use the same temporary table name for different
1480 purposes, whereas the standard's approach constrains all instances of a
1481 given temporary table name to have the same table structure.
1483 The standard's definition of the behavior of temporary tables is
1484 widely ignored. <span class="productname">PostgreSQL</span>'s behavior
1485 on this point is similar to that of several other SQL databases.
1487 The SQL standard also distinguishes between global and local temporary
1488 tables, where a local temporary table has a separate set of contents for
1489 each SQL module within each session, though its definition is still shared
1490 across sessions. Since <span class="productname">PostgreSQL</span> does not
1491 support SQL modules, this distinction is not relevant in
1492 <span class="productname">PostgreSQL</span>.
1494 For compatibility's sake, <span class="productname">PostgreSQL</span> will
1495 accept the <code class="literal">GLOBAL</code> and <code class="literal">LOCAL</code> keywords
1496 in a temporary table declaration, but they currently have no effect.
1497 Use of these keywords is discouraged, since future versions of
1498 <span class="productname">PostgreSQL</span> might adopt a more
1499 standard-compliant interpretation of their meaning.
1501 The <code class="literal">ON COMMIT</code> clause for temporary tables
1502 also resembles the SQL standard, but has some differences.
1503 If the <code class="literal">ON COMMIT</code> clause is omitted, SQL specifies that the
1504 default behavior is <code class="literal">ON COMMIT DELETE ROWS</code>. However, the
1505 default behavior in <span class="productname">PostgreSQL</span> is
1506 <code class="literal">ON COMMIT PRESERVE ROWS</code>. The <code class="literal">ON COMMIT
1507 DROP</code> option does not exist in SQL.
1508 </p></div><div class="refsect2" id="id-1.9.3.85.9.4"><h3>Non-Deferred Uniqueness Constraints</h3><p>
1509 When a <code class="literal">UNIQUE</code> or <code class="literal">PRIMARY KEY</code> constraint is
1510 not deferrable, <span class="productname">PostgreSQL</span> checks for
1511 uniqueness immediately whenever a row is inserted or modified.
1512 The SQL standard says that uniqueness should be enforced only at
1513 the end of the statement; this makes a difference when, for example,
1514 a single command updates multiple key values. To obtain
1515 standard-compliant behavior, declare the constraint as
1516 <code class="literal">DEFERRABLE</code> but not deferred (i.e., <code class="literal">INITIALLY
1517 IMMEDIATE</code>). Be aware that this can be significantly slower than
1518 immediate uniqueness checking.
1519 </p></div><div class="refsect2" id="id-1.9.3.85.9.5"><h3>Column Check Constraints</h3><p>
1520 The SQL standard says that <code class="literal">CHECK</code> column constraints
1521 can only refer to the column they apply to; only <code class="literal">CHECK</code>
1522 table constraints can refer to multiple columns.
1523 <span class="productname">PostgreSQL</span> does not enforce this
1524 restriction; it treats column and table check constraints alike.
1525 </p></div><div class="refsect2" id="id-1.9.3.85.9.6"><h3><code class="literal">EXCLUDE</code> Constraint</h3><p>
1526 The <code class="literal">EXCLUDE</code> constraint type is a
1527 <span class="productname">PostgreSQL</span> extension.
1528 </p></div><div class="refsect2" id="id-1.9.3.85.9.7"><h3>Foreign Key Constraints</h3><p>
1529 The ability to specify column lists in the foreign key actions
1530 <code class="literal">SET DEFAULT</code> and <code class="literal">SET NULL</code> is a
1531 <span class="productname">PostgreSQL</span> extension.
1533 It is a <span class="productname">PostgreSQL</span> extension that a
1534 foreign key constraint may reference columns of a unique index instead of
1535 columns of a primary key or unique constraint.
1536 </p></div><div class="refsect2" id="id-1.9.3.85.9.8"><h3><code class="literal">NULL</code> <span class="quote">“<span class="quote">Constraint</span>”</span></h3><p>
1537 The <code class="literal">NULL</code> <span class="quote">“<span class="quote">constraint</span>”</span> (actually a
1538 non-constraint) is a <span class="productname">PostgreSQL</span>
1539 extension to the SQL standard that is included for compatibility with some
1540 other database systems (and for symmetry with the <code class="literal">NOT
1541 NULL</code> constraint). Since it is the default for any
1542 column, its presence is simply noise.
1543 </p></div><div class="refsect2" id="id-1.9.3.85.9.9"><h3>Constraint Naming</h3><p>
1544 The SQL standard says that table and domain constraints must have names
1545 that are unique across the schema containing the table or domain.
1546 <span class="productname">PostgreSQL</span> is laxer: it only requires
1547 constraint names to be unique across the constraints attached to a
1548 particular table or domain. However, this extra freedom does not exist
1549 for index-based constraints (<code class="literal">UNIQUE</code>,
1550 <code class="literal">PRIMARY KEY</code>, and <code class="literal">EXCLUDE</code>
1551 constraints), because the associated index is named the same as the
1552 constraint, and index names must be unique across all relations within
1554 </p></div><div class="refsect2" id="id-1.9.3.85.9.10"><h3>Inheritance</h3><p>
1555 Multiple inheritance via the <code class="literal">INHERITS</code> clause is
1556 a <span class="productname">PostgreSQL</span> language extension.
1557 SQL:1999 and later define single inheritance using a
1558 different syntax and different semantics. SQL:1999-style
1559 inheritance is not yet supported by
1560 <span class="productname">PostgreSQL</span>.
1561 </p></div><div class="refsect2" id="id-1.9.3.85.9.11"><h3>Zero-Column Tables</h3><p>
1562 <span class="productname">PostgreSQL</span> allows a table of no columns
1563 to be created (for example, <code class="literal">CREATE TABLE foo();</code>). This
1564 is an extension from the SQL standard, which does not allow zero-column
1565 tables. Zero-column tables are not in themselves very useful, but
1566 disallowing them creates odd special cases for <code class="command">ALTER TABLE
1567 DROP COLUMN</code>, so it seems cleaner to ignore this spec restriction.
1568 </p></div><div class="refsect2" id="id-1.9.3.85.9.12"><h3>Multiple Identity Columns</h3><p>
1569 <span class="productname">PostgreSQL</span> allows a table to have more than one
1570 identity column. The standard specifies that a table can have at most one
1571 identity column. This is relaxed mainly to give more flexibility for
1572 doing schema changes or migrations. Note that
1573 the <code class="command">INSERT</code> command supports only one override clause
1574 that applies to the entire statement, so having multiple identity columns
1575 with different behaviors is not well supported.
1576 </p></div><div class="refsect2" id="id-1.9.3.85.9.13"><h3>Generated Columns</h3><p>
1577 The options <code class="literal">STORED</code> and <code class="literal">VIRTUAL</code> are
1578 not standard but are also used by other SQL implementations. The SQL
1579 standard does not specify the storage of generated columns.
1580 </p></div><div class="refsect2" id="id-1.9.3.85.9.14"><h3><code class="literal">LIKE</code> Clause</h3><p>
1581 While a <code class="literal">LIKE</code> clause exists in the SQL standard, many of the
1582 options that <span class="productname">PostgreSQL</span> accepts for it are not
1583 in the standard, and some of the standard's options are not implemented
1584 by <span class="productname">PostgreSQL</span>.
1585 </p></div><div class="refsect2" id="id-1.9.3.85.9.15"><h3><code class="literal">WITH</code> Clause</h3><p>
1586 The <code class="literal">WITH</code> clause is a <span class="productname">PostgreSQL</span>
1587 extension; storage parameters are not in the standard.
1588 </p></div><div class="refsect2" id="id-1.9.3.85.9.16"><h3>Tablespaces</h3><p>
1589 The <span class="productname">PostgreSQL</span> concept of tablespaces is not
1590 part of the standard. Hence, the clauses <code class="literal">TABLESPACE</code>
1591 and <code class="literal">USING INDEX TABLESPACE</code> are extensions.
1592 </p></div><div class="refsect2" id="id-1.9.3.85.9.17"><h3>Typed Tables</h3><p>
1593 Typed tables implement a subset of the SQL standard. According to
1594 the standard, a typed table has columns corresponding to the
1595 underlying composite type as well as one other column that is
1596 the <span class="quote">“<span class="quote">self-referencing column</span>”</span>.
1597 <span class="productname">PostgreSQL</span> does not support self-referencing
1599 </p></div><div class="refsect2" id="id-1.9.3.85.9.18"><h3><code class="literal">PARTITION BY</code> Clause</h3><p>
1600 The <code class="literal">PARTITION BY</code> clause is a
1601 <span class="productname">PostgreSQL</span> extension.
1602 </p></div><div class="refsect2" id="id-1.9.3.85.9.19"><h3><code class="literal">PARTITION OF</code> Clause</h3><p>
1603 The <code class="literal">PARTITION OF</code> clause is a
1604 <span class="productname">PostgreSQL</span> extension.
1605 </p></div></div><div class="refsect1" id="id-1.9.3.85.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>, <a class="xref" href="sql-droptable.html" title="DROP TABLE"><span class="refentrytitle">DROP TABLE</span></a>, <a class="xref" href="sql-createtableas.html" title="CREATE TABLE AS"><span class="refentrytitle">CREATE TABLE AS</span></a>, <a class="xref" href="sql-createtablespace.html" title="CREATE TABLESPACE"><span class="refentrytitle">CREATE TABLESPACE</span></a>, <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtableas.html" title="CREATE TABLE AS">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE SUBSCRIPTION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TABLE AS</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob