Re: PG 10 - Trouble with overlap of range partition of two dimensions

Hi Michael,

So if I'm reading this correctly, the proper way to do my use case is to use partitions of partitions, right?

--Stephen

-----Original Message-----
From: Michael Paquier [mailto:michael(dot)paquier(at)gmail(dot)com]
Sent: Tuesday, October 31, 2017 4:06 PM
To: Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions

On Tue, Oct 31, 2017 at 8:36 PM, Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com> wrote:
> CREATE TABLE lotsa_data_20171027_src3 PARTITION OF lotsa_data
> FOR VALUES FROM ('2017-10-26 18:00:00-06', 3) TO ('2017-10-27
> 17:59:59.999-06', 3);
> ERROR: partition " lotsa_data_20171027_src1" would overlap partition
> "lotsa_data_20171027_src3"
>
> Why am I getting this error?

The answer is in the documentation:
https://www.postgresql.org/docs/devel/static/sql-createtable.html
The rules of such partitions is bound to row-wise comparisons. "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."

So in your example and by comparing with the docs, the first partition allows start_time = '2017-10-26 18:00:00-06' with source_no >= 1, and the second partition allows source_no >= 3 which overlaps with the first one.
--
Michael