Skip to content

Commit 0198c27

Browse files
tglsfdctglsfdc
committed
Docs: improve CREATE TABLE ref page's discussion of partition bounds.
Clarify in the syntax synopsis that partition bound values must be exactly numeric literals or string literals; previously it said "bound_literal" which was defined nowhere. Replace confusing --- and, I think, incorrect in detail --- definition of how range bounds work with a reference to row-wise comparison plus a concrete example (which I stole from Robert Haas). Minor copy-editing in the same area. Discussion: https://postgr.es/m/30475.1496005465@sss.pgh.pa.us Discussion: https://postgr.es/m/28106.1496041449@sss.pgh.pa.us
1 parent ae9bfc5 commit 0198c27

File tree

1 file changed

+41
-31
lines changed

1 file changed

+41
-31
lines changed

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

+41-31
Original file line numberDiff line numberDiff line change
@@ -86,8 +86,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
8686

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

89-
{ IN ( { <replaceable class="PARAMETER">bound_literal</replaceable> | NULL } [, ...] ) |
90-
FROM ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) }
89+
IN ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | NULL } [, ...] ) |
90+
FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | UNBOUNDED } [, ...] )
91+
TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | UNBOUNDED } [, ...] )
9192

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

@@ -252,52 +253,60 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
252253
<term><literal>PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable></literal></term>
253254
<listitem>
254255
<para>
255-
Creates the table as <firstterm>partition</firstterm> of the specified
256+
Creates the table as a <firstterm>partition</firstterm> of the specified
256257
parent table.
257258
</para>
258259

259260
<para>
260-
The partition bound specification must correspond to the partitioning
261-
method and partition key of the parent table, and must not overlap with
262-
any existing partition of that parent.
261+
The <replaceable class="PARAMETER">partition_bound_spec</replaceable>
262+
must correspond to the partitioning method and partition key of the
263+
parent table, and must not overlap with any existing partition of that
264+
parent. The form with <literal>IN</> is used for list partitioning,
265+
while the form with <literal>FROM</> and <literal>TO</> is used for
266+
range partitioning.
263267
</para>
264268

265269
<para>
266-
Each of the values specified in the partition bound specification is
270+
Each of the values specified in
271+
the <replaceable class="PARAMETER">partition_bound_spec</> is
267272
a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>.
268-
A literal is either a numeric constant or a string constant that is
269-
coercible to the corresponding partition key column's type.
273+
Each literal value must be either a numeric constant that is coercible
274+
to the corresponding partition key column's type, or a string literal
275+
that is valid input for that type.
276+
</para>
277+
278+
<para>
279+
When creating a list partition, <literal>NULL</literal> can be
280+
specified to signify that the partition allows the partition key
281+
column to be null. However, there cannot be more than one such
282+
list partition for a given parent table. <literal>NULL</literal>
283+
cannot be specified for range partitions.
270284
</para>
271285

272286
<para>
273287
When creating a range partition, the lower bound specified with
274288
<literal>FROM</literal> is an inclusive bound, whereas the upper
275289
bound specified with <literal>TO</literal> is an exclusive bound.
276290
That is, the values specified in the <literal>FROM</literal> list
277-
are accepted values of the corresponding partition key columns in a
278-
given partition, whereas those in the <literal>TO</literal> list are
279-
not. To be precise, this applies only to the first of the partition
280-
key columns for which the corresponding values in the <literal>FROM</literal>
281-
and <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.
291+
are valid values of the corresponding partition key columns for this
292+
partition, whereas those in the <literal>TO</literal> list are
293+
not. Note that this statement must be understood according to the
294+
rules of row-wise comparison (<xref linkend="row-wise-comparison">).
295+
For example, given <literal>PARTITION BY RANGE (x,y)</>, a partition
296+
bound <literal>FROM (1, 2) TO (3, 4)</literal>
297+
allows <literal>x=1</> with any <literal>y&gt;=2</>,
298+
<literal>x=2</> with any non-null <literal>y</>,
299+
and <literal>x=3</> with any <literal>y&lt;4</>.
286300
</para>
287301

288302
<para>
289-
Specifying <literal>UNBOUNDED</literal> in <literal>FROM</literal>
303+
Writing <literal>UNBOUNDED</literal> in <literal>FROM</literal>
290304
signifies <literal>-infinity</literal> as the lower bound of the
291-
corresponding column, whereas it signifies <literal>+infinity</literal>
292-
as the upper bound when specified in <literal>TO</literal>.
293-
</para>
294-
295-
<para>
296-
When creating a list partition, <literal>NULL</literal> can be
297-
specified to signify that the partition allows the partition key
298-
column to be null. However, there cannot be more than one such
299-
list partition for a given parent table. <literal>NULL</literal>
300-
cannot be specified for range partitions.
305+
corresponding column, whereas when written in <literal>TO</literal>,
306+
it signifies <literal>+infinity</literal> as the upper bound.
307+
All items following an <literal>UNBOUNDED</literal> item within
308+
a <literal>FROM</literal> or <literal>TO</literal> list must also
309+
be <literal>UNBOUNDED</literal>.
301310
</para>
302311

303312
<para>
@@ -318,8 +327,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
318327
<para>
319328
Rows inserted into a partitioned table will be automatically routed to
320329
the correct partition. If no suitable partition exists, an error will
321-
occur. Also, if updating a row in a given partition causes it to move
322-
to another partition due to the new partition key, an error will occur.
330+
occur. Also, if updating a row in a given partition would require it
331+
to move to another partition due to new partition key values, an error
332+
will occur.
323333
</para>
324334

325335
<para>

0 commit comments

Comments
 (0)