Skip to content

Commit c1369fe

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Improve CREATE TABLE documentation of partitioning
Amit Langote, with corrections by me
1 parent 62e8b38 commit c1369fe

File tree

1 file changed

+98
-9
lines changed

1 file changed

+98
-9
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 98 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -85,8 +85,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
8585

8686
<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
8787

88-
{ IN ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) |
89-
FROM ( { <replaceable class="PARAMETER">expression</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">expression</replaceable> | UNBOUNDED } [, ...] ) }
88+
{ IN ( { <replaceable class="PARAMETER">bound_literal</replaceable> | NULL } [, ...] ) |
89+
FROM ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) }
9090

9191
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
9292

@@ -261,10 +261,48 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
261261
any existing partition of that parent.
262262
</para>
263263

264+
<note>
265+
<para>
266+
Each of the values specified in the partition bound specification is
267+
a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>.
268+
A literal is either a numeric constant or a string constant that is
269+
coercable to the corresponding partition key column's type.
270+
</para>
271+
272+
<para>
273+
When creating a range partition, the lower bound specified with
274+
<literal>FROM</literal> is an inclusive bound, whereas the upper bound
275+
specified with <literal>TO</literal> is an exclusive bound. That is,
276+
the values specified in the <literal>FROM</literal> list are accepted
277+
values of the corresponding partition key columns in a given partition,
278+
whereas those in the <literal>TO</literal> list are not. To be precise,
279+
this applies only to the first of the partition key columns for which
280+
the corresponding values in the <literal>FROM</literal> and
281+
<literal>TO</literal> lists are not equal. All rows in a given
282+
partition contain the same values for all preceding columns, equal to
283+
those specified in <literal>FROM</literal> and <literal>TO</literal>
284+
lists. On the other hand, any subsequent columns are insignificant
285+
as far as implicit partition constraint is concerned.
286+
287+
Specifying <literal>UNBOUNDED</literal> in <literal>FROM</literal>
288+
signifies <literal>-infinity</literal> as the lower bound of the
289+
corresponding column, whereas it signifies <literal>+infinity</literal>
290+
as the upper bound when specified in <literal>TO</literal>.
291+
</para>
292+
293+
<para>
294+
When creating a list partition, <literal>NULL</literal> can be specified
295+
to signify that the partition allows the partition key column to be null.
296+
However, there cannot be more than one such list partitions for a given
297+
parent table. <literal>NULL</literal> cannot specified for range
298+
partitions.
299+
</para>
300+
</note>
301+
264302
<para>
265303
A partition cannot have columns other than those inherited from the
266-
parent. That includes the <structfield>oid</> column, which can be
267-
specified using the <literal>WITH (OIDS)</literal> clause.
304+
parent. If the parent is specified <literal>WITH OIDS</literal> then
305+
the partitions must also explicitly specify <literal>WITH OIDS</literal>.
268306
Defaults and constraints can optionally be specified for each of the
269307
inherited columns. One can also specify table constraints in addition
270308
to those inherited from the parent. If a check constraint with the name
@@ -386,11 +424,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
386424
<firstterm>partitioned</firstterm> table. The parenthesized list of
387425
columns or expressions forms the <firstterm>partition key</firstterm>
388426
for the table. When using range partitioning, the partition key can
389-
include multiple columns or expressions, but for list partitioning, the
390-
partition key must consist of a single column or expression. If no
391-
btree operator class is specified when creating a partitioned table,
392-
the default btree operator class for the datatype will be used. If
393-
there is none, an error will be reported.
427+
include multiple columns or expressions (up to 32, but this limit can
428+
altered when building <productname>PostgreSQL</productname>.), but for
429+
list partitioning, the partition key must consist of a single column or
430+
expression. If no btree operator class is specified when creating a
431+
partitioned table, the default btree operator class for the datatype will
432+
be used. If there is none, an error will be reported.
394433
</para>
395434

396435
<para>
@@ -1482,6 +1521,16 @@ CREATE TABLE measurement (
14821521
peaktemp int,
14831522
unitsales int
14841523
) PARTITION BY RANGE (logdate);
1524+
</programlisting></para>
1525+
1526+
<para>
1527+
Create a range partitioned table with multiple columns in the partition key:
1528+
<programlisting>
1529+
CREATE TABLE measurement_year_month (
1530+
logdate date not null,
1531+
peaktemp int,
1532+
unitsales int
1533+
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
14851534
</programlisting></para>
14861535

14871536
<para>
@@ -1503,6 +1552,27 @@ CREATE TABLE measurement_y2016m07
15031552
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
15041553
</programlisting></para>
15051554

1555+
<para>
1556+
Create a few partitions of a range partitioned table with multiple
1557+
columns in the partition key:
1558+
<programlisting>
1559+
CREATE TABLE measurement_ym_older
1560+
PARTITION OF measurement_year_month
1561+
FOR VALUES FROM (unbounded, unbounded) TO (2016, 11);
1562+
1563+
CREATE TABLE measurement_ym_y2016m11
1564+
PARTITION OF measurement_year_month
1565+
FOR VALUES FROM (2016, 11) TO (2016, 12);
1566+
1567+
CREATE TABLE measurement_ym_y2016m12
1568+
PARTITION OF measurement_year_month
1569+
FOR VALUES FROM (2016, 12) TO (2017, 01);
1570+
1571+
CREATE TABLE measurement_ym_y2017m01
1572+
PARTITION OF measurement_year_month
1573+
FOR VALUES FROM (2017, 01) TO (2017, 02);
1574+
</programlisting></para>
1575+
15061576
<para>
15071577
Create partition of a list partitioned table:
15081578
<programlisting>
@@ -1705,6 +1775,25 @@ CREATE TABLE cities_ab_10000_to_100000
17051775
effect can be had using the OID feature.
17061776
</para>
17071777
</refsect2>
1778+
1779+
<refsect2>
1780+
<title><literal>PARTITION BY</> Clause</title>
1781+
1782+
<para>
1783+
The <literal>PARTITION BY</> clause is a
1784+
<productname>PostgreSQL</productname> extension.
1785+
</para>
1786+
</refsect2>
1787+
1788+
<refsect2>
1789+
<title><literal>PARTITION OF</> Clause</title>
1790+
1791+
<para>
1792+
The <literal>PARTITION OF</> clause is a
1793+
<productname>PostgreSQL</productname> extension.
1794+
</para>
1795+
</refsect2>
1796+
17081797
</refsect1>
17091798

17101799

0 commit comments

Comments
 (0)