summaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/catalogs.sgml129
-rw-r--r--doc/src/sgml/ref/alter_table.sgml117
-rw-r--r--doc/src/sgml/ref/create_foreign_table.sgml26
-rw-r--r--doc/src/sgml/ref/create_table.sgml154
4 files changed, 421 insertions, 5 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c4246dcd866..9d2e89523d6 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -226,6 +226,11 @@
</row>
<row>
+ <entry><link linkend="catalog-pg-partitioned-table"><structname>pg_partitioned_table</structname></link></entry>
+ <entry>information about partition key of tables</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-policy"><structname>pg_policy</structname></link></entry>
<entry>row-security policies</entry>
</row>
@@ -1723,7 +1728,8 @@
<entry><type>char</type></entry>
<entry></entry>
<entry>
- <literal>r</> = ordinary table, <literal>i</> = index,
+ <literal>r</> = ordinary table, <literal>P</> = partitioned table,
+ <literal>i</> = index
<literal>S</> = sequence, <literal>v</> = view,
<literal>m</> = materialized view,
<literal>c</> = composite type, <literal>t</> = TOAST table,
@@ -1840,6 +1846,13 @@
</row>
<row>
+ <entry><structfield>relispartition</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>True if table is a partition</entry>
+ </row>
+
+ <row>
<entry><structfield>relfrozenxid</structfield></entry>
<entry><type>xid</type></entry>
<entry></entry>
@@ -1885,6 +1898,16 @@
Access-method-specific options, as <quote>keyword=value</> strings
</entry>
</row>
+
+ <row>
+ <entry><structfield>relpartbound</structfield></entry>
+ <entry><type>pg_node_tree</type></entry>
+ <entry></entry>
+ <entry>
+ If table is a partition (see <structfield>relispartition</structfield>),
+ internal representation of the partition bound
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -4689,6 +4712,110 @@
</sect1>
+ <sect1 id="catalog-pg-partitioned-table">
+ <title><structname>pg_partitioned_table</structname></title>
+
+ <indexterm zone="catalog-pg-partitioned-table">
+ <primary>pg_partitioned_table</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_partitioned_table</structname> stores
+ information about how tables are partitioned.
+ </para>
+
+ <table>
+ <title><structname>pg_partitioned_table</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+
+ <row>
+ <entry><structfield>partrelid</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
+ <entry>The OID of the <structname>pg_class</> entry for this partitioned table</entry>
+ </row>
+
+ <row>
+ <entry><structfield>partstrat</structfield></entry>
+ <entry><type>char</type></entry>
+ <entry></entry>
+ <entry>
+ Partitioning strategy; <literal>l</> = list partitioned table,
+ <literal>r</> = range partitioned table
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>partnatts</structfield></entry>
+ <entry><type>int2</type></entry>
+ <entry></entry>
+ <entry>The number of columns in partition key</entry>
+ </row>
+
+ <row>
+ <entry><structfield>partattrs</structfield></entry>
+ <entry><type>int2vector</type></entry>
+ <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
+ <entry>
+ This is an array of <structfield>partnatts</structfield> values that
+ indicate which table columns are part of the partition key. For
+ example, a value of <literal>1 3</literal> would mean that the first
+ and the third table columns make up the partition key. A zero in this
+ array indicates that the corresponding partition key column is an
+ expression, rather than a simple column reference.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>partclass</structfield></entry>
+ <entry><type>oidvector</type></entry>
+ <entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
+ <entry>
+ For each column in the partition key, this contains the OID of the
+ operator class to use. See
+ <link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link> for details.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>partcollation</structfield></entry>
+ <entry><type>oidvector</type></entry>
+ <entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
+ <entry>
+ For each column in the partition key, this contains the OID of the
+ the collation to use for partitioning.
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>partexprs</structfield></entry>
+ <entry><type>pg_node_tree</type></entry>
+ <entry></entry>
+ <entry>
+ Expression trees (in <function>nodeToString()</function>
+ representation) for partition key columns that are not simple column
+ references. This is a list with one element for each zero
+ entry in <structfield>partattrs</>. Null if all partition key columns
+ are simple references.
+ </entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="catalog-pg-policy">
<title><structname>pg_policy</structname></title>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index e48ccf21e4f..a6a43c4b302 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -33,6 +33,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
+ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
+ ATTACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable>
+ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
+ DETACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable>
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
@@ -166,6 +170,12 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
values or to reject null values. You can only use <literal>SET
NOT NULL</> when the column contains no null values.
</para>
+
+ <para>
+ If this table is a partition, one cannot perform <literal>DROP NOT NULL</>
+ on a column if it is marked <literal>NOT NULL</literal> in the parent
+ table.
+ </para>
</listitem>
</varlistentry>
@@ -704,13 +714,63 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ATTACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable> <replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
+ <listitem>
+ <para>
+ This form attaches an existing table (which might itself be partitioned)
+ as a partition of the target table using the same syntax for
+ <replaceable class="PARAMETER">partition_bound_spec</replaceable> as
+ <xref linkend="sql-createtable">. The partition bound specification
+ must correspond to the partitioning strategy and partition key of the
+ target table. The table to be attached must have all the same columns
+ as the target table and no more; moreover, the column types must also
+ match. Also, it must have all the <literal>NOT NULL</literal> and
+ <literal>CHECK</literal> constraints of the target table. Currently
+ <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
+ <literal>FOREIGN KEY</literal> constraints are not considered.
+ If any of the <literal>CHECK</literal> constraints of the table being
+ attached is marked <literal>NO INHERIT</literal>, the command will fail;
+ such a constraint must be recreated without the <literal>NO INHERIT</literal>
+ clause.
+ </para>
+
+ <para>
+ A full table scan is performed on the table being attached to check that
+ no existing row in the table violates the partition constraint. It is
+ possible to avoid this scan by adding a valid <literal>CHECK</literal>
+ constraint to the table that would allow only the rows satisfying the
+ desired partition constraint before running this command. It will be
+ determined using such a constraint that the table need not be scanned
+ to validate the partition constraint. This does not work, however, if
+ any of the partition keys is an expression and the partition does not
+ accept <literal>NULL</literal> values. If attaching a list partition
+ that will not accept <literal>NULL</literal> values, also add
+ <literal>NOT NULL</literal> constraint to the partition key column,
+ unless it's an expression.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DETACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable></term>
+ <listitem>
+ <para>
+ This form detaches specified partition of the target table. The detached
+ partition continues to exist as a standalone table, but no longer has any
+ ties to the table from which it was detached.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
<para>
All the actions except <literal>RENAME</literal>,
- <literal>SET TABLESPACE</literal> and <literal>SET SCHEMA</literal>
- can be combined into
+ <literal>SET TABLESPACE</literal>, <literal>SET SCHEMA</literal>,
+ <literal>ATTACH PARTITION</literal>, and
+ <literal>DETACH PARTITION</literal> can be combined into
a list of multiple alterations to apply in parallel. For example, it
is possible to add several columns and/or alter the type of several
columns in a single command. This is particularly useful with large
@@ -721,8 +781,9 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
You must own the table to use <command>ALTER TABLE</>.
To change the schema or tablespace of a table, you must also have
<literal>CREATE</literal> privilege on the new schema or tablespace.
- To add the table as a new child of a parent table, you must own the
- parent table as well.
+ To add the table as a new child of a parent table, you must own the parent
+ table as well. Also, to attach a table as a new partition of the table,
+ you must own the table being attached.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have <literal>CREATE</literal> privilege on
the table's schema. (These restrictions enforce that altering the owner
@@ -938,6 +999,25 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="PARAMETER">partition_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the table to attach as a new partition or to detach from this table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
+ <listitem>
+ <para>
+ The partition bound specification for a new partition. Refer to
+ <xref linkend="sql-createtable"> for more details on the syntax of the same.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
@@ -978,6 +1058,11 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
</para>
<para>
+ Similarly, when attaching a new partition it may be scanned to verify that
+ existing rows meet the partition constraint.
+ </para>
+
+ <para>
The main reason for providing the option to specify multiple changes
in a single <command>ALTER TABLE</> is that multiple table scans or
rewrites can thereby be combined into a single pass over the table.
@@ -1047,6 +1132,9 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
COLUMN</command>) never removes any descendant columns, but
instead marks them as independently defined rather than inherited.
+ A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
+ partitioned table, because all partitions of a table must have the same
+ columns as the partitioning root.
</para>
<para>
@@ -1233,6 +1321,27 @@ ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
</programlisting></para>
+ <para>
+ Attach a partition to range partitioned table:
+<programlisting>
+ALTER TABLE measurement
+ ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
+</programlisting></para>
+
+ <para>
+ Attach a partition to list partitioned table:
+<programlisting>
+ALTER TABLE cities
+ ATTACH PARTITION cities_west FOR VALUES IN ('Los Angeles', 'San Francisco');
+</programlisting></para>
+
+ <para>
+ Detach a partition from partitioned table:
+<programlisting>
+ALTER TABLE cities
+ DETACH PARTITION measurement_y2015m12;
+</programlisting></para>
+
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 413b033cb57..5d0dcf567b5 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -27,6 +27,15 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
SERVER <replaceable class="parameter">server_name</replaceable>
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
+CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable>
+ PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable> [ (
+ { <replaceable class="PARAMETER">column_name</replaceable> WITH OPTIONS [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
+ | <replaceable>table_constraint</replaceable> }
+ [, ... ]
+) ] <replaceable class="PARAMETER">partition_bound_spec</replaceable>
+ SERVER <replaceable class="parameter">server_name</replaceable>
+[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
+
<phrase>where <replaceable class="PARAMETER">column_constraint</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
@@ -68,6 +77,12 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
</para>
<para>
+ If <literal>PARTITION OF</literal> clause is specified then the table is
+ created as a partition of <literal>parent_table</literal> with specified
+ bounds.
+ </para>
+
+ <para>
To be able to create a foreign table, you must have <literal>USAGE</literal>
privilege on the foreign server, as well as <literal>USAGE</literal>
privilege on all column types used in the table.
@@ -314,6 +329,17 @@ CREATE FOREIGN TABLE films (
SERVER film_server;
</programlisting></para>
+ <para>
+ Create foreign table <structname>measurement_y2016m07</>, which will be
+ accessed through the server <structname>server_07</>, as a partition
+ of the range partitioned table <structname>measurement</>:
+
+<programlisting>
+CREATE FOREIGN TABLE measurement_y2016m07
+ PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
+ SERVER server_07;
+</programlisting></para>
+
</refsect1>
<refsect1 id="SQL-CREATEFOREIGNTABLE-compatibility">
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index bf2ad64d66e..8bf8af302b5 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -28,6 +28,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
+[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -38,6 +39,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
| <replaceable>table_constraint</replaceable> }
[, ... ]
) ]
+[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
+[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
+[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
+
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable>
+ PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable> [ (
+ { <replaceable class="PARAMETER">column_name</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
+ | <replaceable>table_constraint</replaceable> }
+ [, ... ]
+) ] FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable>
+[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -70,6 +83,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
+<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
+
+{ IN ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) |
+ FROM ( { <replaceable class="PARAMETER">expression</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">expression</replaceable> | UNBOUNDED } [, ...] ) }
+
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
@@ -230,6 +248,51 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
+ <term><literal>PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
+ <listitem>
+ <para>
+ Creates the table as <firstterm>partition</firstterm> of the specified
+ parent table.
+ </para>
+
+ <para>
+ The partition bound specification must correspond to the partitioning
+ method and partition key of the parent table, and must not overlap with
+ any existing partition of that parent.
+ </para>
+
+ <para>
+ A partition cannot have columns other than those inherited from the
+ parent. That includes the <structfield>oid</> column, which can be
+ specified using the <literal>WITH (OIDS)</literal> clause.
+ Defaults and constraints can optionally be specified for each of the
+ inherited columns. One can also specify table constraints in addition
+ to those inherited from the parent. If a check constraint with the name
+ matching one of the parent's constraint is specified, it is merged with
+ the latter, provided the specified condition is same.
+ </para>
+
+ <para>
+ Rows inserted into a partitioned table will be automatically routed to
+ the correct partition. If no suitable partition exists, an error will
+ occur.
+ </para>
+
+ <para>
+ A partition must have the same column names and types as the table of
+ which it is a partition. Therefore, modifications to the column names
+ or types of the partitioned table will automatically propagate to all
+ children, as will operations such as TRUNCATE which normally affect a
+ table and all of its inheritance children. It is also possible to
+ TRUNCATE a partition individually, just as for an inheritance child.
+ Note that dropping a partition with <literal>DROP TABLE</literal>
+ requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the
+ parent table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">column_name</replaceable></term>
<listitem>
<para>
@@ -314,6 +377,46 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
+ <term><literal>PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
+ <listitem>
+ <para>
+ The optional <literal>PARTITION BY</literal> clause specifies a strategy
+ of partitioning the table. The table thus created is called a
+ <firstterm>partitioned</firstterm> table. The parenthesized list of
+ columns or expressions forms the <firstterm>partition key</firstterm>
+ for the table. When using range partitioning, the partition key can
+ include multiple columns or expressions, but for list partitioning, the
+ partition key must consist of a single column or expression. If no
+ btree operator class is specified when creating a partitioned table,
+ the default btree operator class for the datatype will be used. If
+ there is none, an error will be reported.
+ </para>
+
+ <para>
+ A partitioned table is divided into sub-tables (called partitions),
+ which are created using separate <literal>CREATE TABLE</> commands.
+ The partitioned table is itself empty. A data row inserted into the
+ table is routed to a partition based on the value of columns or
+ expressions in the partition key. If no existing partition matches
+ the values in the new row, an error will be reported.
+ </para>
+
+ <para>
+ Partitioned tables do not support <literal>UNIQUE</literal>,
+ <literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or
+ <literal>FOREIGN KEY</literal> constraints; however, you can define
+ these constraints on individual partitions.
+ </para>
+
+ <para>
+ When using range partitioning, a <literal>NOT NULL</literal> constraint
+ is added to each non-expression column in the partition key.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
<listitem>
<para>
@@ -1369,6 +1472,57 @@ CREATE TABLE employees OF employee_type (
salary WITH OPTIONS DEFAULT 1000
);
</programlisting></para>
+
+ <para>
+ Create a range partitioned table:
+<programlisting>
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+</programlisting></para>
+
+ <para>
+ Create a list partitioned table:
+<programlisting>
+CREATE TABLE cities (
+ name text not null,
+ population int,
+) PARTITION BY LIST (initcap(name));
+</programlisting></para>
+
+ <para>
+ Create partition of a range partitioned table:
+<programlisting>
+CREATE TABLE measurement_y2016m07
+ PARTITION OF measurement (
+ unitsales WITH OPTIONS DEFAULT 0
+) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
+</programlisting></para>
+
+ <para>
+ Create partition of a list partitioned table:
+<programlisting>
+CREATE TABLE cities_west
+ PARTITION OF cities (
+ CONSTRAINT city_id_nonzero CHECK (city_id != 0)
+) FOR VALUES IN ('Los Angeles', 'San Francisco');
+</programlisting></para>
+
+ <para>
+ Create partition of a list partitioned table that is itself further
+ partitioned and then add a partition to it:
+<programlisting>
+CREATE TABLE cities_west
+ PARTITION OF cities (
+ CONSTRAINT city_id_nonzero CHECK (city_id != 0)
+) FOR VALUES IN ('Los Angeles', 'San Francisco') PARTITION BY RANGE (population);
+
+CREATE TABLE cities_west_10000_to_100000
+ PARTITION OF cities_west FOR VALUES FROM (10000) TO (100000);
+</programlisting></para>
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
generated by cgit v1.2.3 (git 2.46.0) at 2025-11-18 16:39:56 +0000