On Fri, Jun 23, 2017 at 6:58 AM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2017/06/22 20:48, amul sul wrote: >> Hi, >> >> While working on the another patch, I came across the case where >> I need an auto generated partition for a mutil-column range partitioned >> table having following range bound: >> >> PARTITION p1 FROM (UNBOUNDED, UNBOUNDED) TO (10, 10) >> PARTITION p2 FROM (10, 10) TO (10, UNBOUNDED) >> PARTITION p3 FROM (10, UNBOUNDED) TO (20, 10) >> PARTITION p4 FROM (20, 10) TO (20, UNBOUNDED) >> PARTITION p5 FROM (20, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED) >> >> In this, a lower bound of the partition is an upper bound of the >> previous partition. >> >> While trying to create p3 partition with (10, UNBOUNDED) to (20, 10) bound, >> got an overlap partition error. >> >> Here is the SQL to reproduced this error: >> >> CREATE TABLE range_parted ( i1 int, i2 int ) PARTITION BY RANGE (i1, i2); >> CREATE TABLE p1 PARTITION OF range_parted FOR VALUES FROM (UNBOUNDED, >> UNBOUNDED) TO (10, 10); >> CREATE TABLE p2 PARTITION OF range_parted FOR VALUES FROM (10, 10) TO >> (10, UNBOUNDED); >> CREATE TABLE p3 PARTITION OF tab1 FOR VALUES FROM (10, UNBOUNDED) TO (20, >> 10); >> >> ERROR: partition "p3" would overlap partition "tab1_p_10_10" >> >> This happened because of UNBOUNDED handling, where it is a negative infinite >> if it is in FROM clause. Wondering can't we explicitly treat this as >> a positive infinite value, can we? > > No, we cannot. What would be greater than (or equal to) +infinite? > Nothing. So, even if you will want p3 to accept (10, 9890148), it won't > because 9890148 is not >= +infinite. It will accept only the rows where > the first column is > 10 (second column is not checked in that case). > > You will have to define p3 as follows: > > CREATE TABLE p3 PARTITION OF tab1 FOR VALUES FROM (11, UNBOUNDED) TO (20, 10); > What if the partition key column is FLOAT ?
Regards, Amul -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers