diff options
Diffstat (limited to 'doc/src/sgml/ref/create_table.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 61 |
1 files changed, 47 insertions, 14 deletions
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index b15c19d3d0c..e9c2c49533e 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -87,8 +87,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI <phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase> IN ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | NULL } [, ...] ) | -FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | UNBOUNDED } [, ...] ) - TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | UNBOUNDED } [, ...] ) +FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] ) + TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replaceable class="PARAMETER">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] ) <phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase> @@ -269,10 +269,10 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace <para> Each of the values specified in the <replaceable class="PARAMETER">partition_bound_spec</> is - a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>. - 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. + a literal, <literal>NULL</literal>, <literal>MINVALUE</literal>, or + <literal>MAXVALUE</literal>. 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. </para> <para> @@ -300,13 +300,46 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace </para> <para> - Writing <literal>UNBOUNDED</literal> in <literal>FROM</literal> - signifies <literal>-infinity</literal> as the lower bound of the - corresponding column, whereas when written in <literal>TO</literal>, - it signifies <literal>+infinity</literal> as the upper bound. - All items following an <literal>UNBOUNDED</literal> item within - a <literal>FROM</literal> or <literal>TO</literal> list must also - be <literal>UNBOUNDED</literal>. + The special values <literal>MINVALUE</> and <literal>MAXVALUE</> + may be used when creating a range partition to indicate that there + is no lower or upper bound on the column's value. For example, a + partition defined using <literal>FROM (MINVALUE) TO (10)</> allows + any values less than 10, and a partition defined using + <literal>FROM (10) TO (MAXVALUE)</> allows any values greater than + or equal to 10. + </para> + + <para> + When creating a range partition involving more than one column, it + can also make sense to use <literal>MAXVALUE</> as part of the lower + bound, and <literal>MINVALUE</> as part of the upper bound. For + example, a partition defined using + <literal>FROM (0, MAXVALUE) TO (10, MAXVALUE)</> allows any rows + where the first partition key column is greater than 0 and less than + or equal to 10. Similarly, a partition defined using + <literal>FROM ('a', MINVALUE) TO ('b', MINVALUE)</> allows any rows + where the first partition key column starts with "a". + </para> + + <para> + Note that any values after <literal>MINVALUE</> or + <literal>MAXVALUE</> in a partition bound are ignored; so the bound + <literal>(10, MINVALUE, 0)</> is equivalent to + <literal>(10, MINVALUE, 10)</> and <literal>(10, MINVALUE, MINVALUE)</> + and <literal>(10, MINVALUE, MAXVALUE)</>. + </para> + + <para> + Also note that some element types, such as <literal>timestamp</>, + have a notion of "infinity", which is just another value that can + be stored. This is different from <literal>MINVALUE</> and + <literal>MAXVALUE</>, which are not real values that can be stored, + but rather they are ways of saying that the value is unbounded. + <literal>MAXVALUE</> can be thought of as being greater than any + other value, including "infinity" and <literal>MINVALUE</> as being + less than any other value, including "minus infinity". Thus the range + <literal>FROM ('infinity') TO (MAXVALUE)</> is not an empty range; it + allows precisely one value to be stored — "infinity". </para> <para> @@ -1610,7 +1643,7 @@ CREATE TABLE measurement_y2016m07 <programlisting> CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month - FOR VALUES FROM (unbounded, unbounded) TO (2016, 11); + FOR VALUES FROM (MINVALUE, 0) TO (2016, 11); CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month |