From 0198c277a29a035aa8a4e6767967201135f6caa9 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Wed, 7 Jun 2017 17:23:38 -0400
Subject: 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
---
doc/src/sgml/ref/create_table.sgml | 72 ++++++++++++++++++++++----------------
1 file changed, 41 insertions(+), 31 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 0478e40447..bc014d0879 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -86,8 +86,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
and partition_bound_spec is:
-{ IN ( { bound_literal | NULL } [, ...] ) |
- FROM ( { bound_literal | UNBOUNDED } [, ...] ) TO ( { bound_literal | UNBOUNDED } [, ...] ) }
+IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
+FROM ( { numeric_literal | string_literal | UNBOUNDED } [, ...] )
+ TO ( { numeric_literal | string_literal | UNBOUNDED } [, ...] )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
@@ -252,21 +253,34 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
PARTITION OF parent_table FOR VALUES partition_bound_spec
- Creates the table as partition of the specified
+ Creates the table as a partition of the specified
parent table.
- 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.
+ The partition_bound_spec
+ must correspond to the partitioning method and partition key of the
+ parent table, and must not overlap with any existing partition of that
+ parent. The form with IN is used for list partitioning,
+ while the form with FROM and TO is used for
+ range partitioning.
- Each of the values specified in the partition bound specification is
+ Each of the values specified in
+ the partition_bound_spec is
a literal, NULL, or UNBOUNDED.
- A literal is either a numeric constant or a string constant that is
- coercible to the corresponding partition key column's type.
+ Each literal value must be either a numeric constant that is coercible
+ to the corresponding partition key column's type, or a string literal
+ that is valid input for that type.
+
+
+
+ When creating a list partition, NULL can be
+ specified to signify that the partition allows the partition key
+ column to be null. However, there cannot be more than one such
+ list partition for a given parent table. NULL
+ cannot be specified for range partitions.
@@ -274,30 +288,25 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
FROM is an inclusive bound, whereas the upper
bound specified with TO is an exclusive bound.
That is, the values specified in the FROM list
- are accepted values of the corresponding partition key columns in a
- given partition, whereas those in the TO list are
- not. To be precise, this applies only to the first of the partition
- key columns for which the corresponding values in the FROM
- and TO lists are not equal. All rows in a given
- partition contain the same values for all preceding columns, equal to
- those specified in FROM and TO
- lists. On the other hand, any subsequent columns are insignificant
- as far as implicit partition constraint is concerned.
+ are valid values of the corresponding partition key columns for this
+ partition, whereas those in the TO list are
+ not. Note that this statement must be understood according to the
+ rules of row-wise comparison ().
+ For example, given PARTITION BY RANGE (x,y), a partition
+ bound FROM (1, 2) TO (3, 4)
+ allows x=1 with any y>=2,
+ x=2 with any non-null y,
+ and x=3 with any y<4.
- Specifying UNBOUNDED in FROM
+ Writing UNBOUNDED in FROM
signifies -infinity as the lower bound of the
- corresponding column, whereas it signifies +infinity
- as the upper bound when specified in TO.
-
-
-
- When creating a list partition, NULL can be
- specified to signify that the partition allows the partition key
- column to be null. However, there cannot be more than one such
- list partition for a given parent table. NULL
- cannot be specified for range partitions.
+ corresponding column, whereas when written in TO,
+ it signifies +infinity as the upper bound.
+ All items following an UNBOUNDED item within
+ a FROM or TO list must also
+ be UNBOUNDED.
@@ -318,8 +327,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
Rows inserted into a partitioned table will be automatically routed to
the correct partition. If no suitable partition exists, an error will
- occur. Also, if updating a row in a given partition causes it to move
- to another partition due to the new partition key, an error will occur.
+ occur. Also, if updating a row in a given partition would require it
+ to move to another partition due to new partition key values, an error
+ will occur.
--
cgit v1.2.3