Re: BUG #14725: Partition constraint issue on multiple columns as the key of range partition

On 1 July 2017 at 07:52, <tianbing(at)highgo(dot)com> wrote:
> When I use two columns as the key of range partition to create partition
> table.
> postgres=# create table test(n1 int, n2 int) partition by range(n1, n2);
> CREATE TABLE
> postgres=# create table test_1 partition of test for values from (0,0) to
> (10,100);
> CREATE TABLE
> postgres=# create table test_2 partition of test for values from (10,100) to
> (20,50);
> CREATE TABLE
>
> In partition test_2, partition constraint of the second column seem to be
> improper(from 100 to 50). The value behind keyword "TO" should be larger
> than value behind keyword "FROM".
>

This is not a bug, it's how multi-column range partitioning works. In
a multi-column range partition, the first column has precedence and is
checked first, so the range covered by partition test_2:

FROM (10,100) TO (20,50)

is equivalent to:

(n1 > 10 OR (n1 = 10 AND n2 >= 100))
AND
(n1 < 20 OR (n1 = 20 AND n2 < 50))

rather than being a hard constraint on the second column. So, the
following values are all in this partition:

(10,n2) for n2 >= 100
(11,n2) for all n2
(12,n2) for all n2
...
(19,n2) for all n2
(20,n2) for n2 < 50

Regards,
Dean