Use MINVALUE/MAXVALUE instead of UNBOUNDED for range partition bounds.

Previously, UNBOUNDED meant no lower bound when used in the FROM list,
and no upper bound when used in the TO list, which was OK for
single-column range partitioning, but problematic with multiple
columns. For example, an upper bound of (10.0, UNBOUNDED) would not be
collocated with a lower bound of (10.0, UNBOUNDED), thus making it
difficult or impossible to define contiguous multi-column range
partitions in some cases.

Fix this by using MINVALUE and MAXVALUE instead of UNBOUNDED to
represent a partition column that is unbounded below or above
respectively. This syntax removes any ambiguity, and ensures that if
one partition's lower bound equals another partition's upper bound,
then the partitions are contiguous.

Also drop the constraint prohibiting finite values after an unbounded
column, and just document the fact that any values after MINVALUE or
MAXVALUE are ignored. Previously it was necessary to repeat UNBOUNDED
multiple times, which was needlessly verbose.

Note: Forces a post-PG 10 beta2 initdb.

Report by Amul Sul, original patch by Amit Langote with some
additional hacking by me.

Discussion: https://postgr.es/m/CAAJ_b947mowpLdxL3jo3YLKngRjrq9+Ej4ymduQTfYR+8=YAYQ@mail.gmail.com

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 b15c19d3d0..e9c2c49533 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 &mdash; "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