The comparisons for partitions are lower <= value < upper, so this works fine:
> xof=# create table t (pk bigint not null, ts timestamp not null) partition by
> range (ts);
> CREATE TABLE
> xof=# create table t1 partition of t for values from
> ('2024-01-01'::timestamp) to ('2024-02-01'::timestamp);
> CREATE TABLE
> xof=# create table t2 partition of t for values from
> ('2024-02-01'::timestamp) to ('2024-03-01'::timestamp);
> CREATE TABLE
In both cases, it's unambiguous that a record with ('2024-02-01'::timestamp)
would go into t2.
> On Sep 10, 2024, at 15:23, Brent Wood <[email protected]> wrote:
>
> I'm not sure of the less than vs less than or equals in this one, so it may
> be my ignorance... but which partition gets records with a date of
> '24-02-01", it seems that without a more explicit definition there is
> ambiguity on dates at the partition limits when those dates are common to
> multiple partitions?
>
> To avoid this shouldn't the definition be:
>
>> xof=# create table t (pk bigint not null, ts timestamp not null) partition
>> by range (ts, pk);
>> CREATE TABLE
>> xof=# create table t1 partition of t for values from
>> ('2024-01-01'::timestamp, minvalue) to ('2024-01-31'::timestamp, maxvalue);
>> CREATE TABLE
>> xof=# create table t2 partition of t for values from
>> ('2024-02-01'::timestamp, minvalue) to ('2024-02-29'::timestamp, maxvalue);
>
>
> Brent Wood
>
> Principal Technician, Fisheries
> NIWA
> DDI: +64 (4) 3860529
> From: Christophe Pettus <[email protected]>
> Sent: Wednesday, September 11, 2024 10:13
> To: pgsql-general <[email protected]>
> Subject: Overlapping values (?) in multi-column partitioned tables
> Hi,
>
> I am clearly not understanding something. Consider:
>
>> xof=# create table t (pk bigint not null, ts timestamp not null) partition
>> by range (ts, pk);
>> CREATE TABLE
>> xof=# create table t1 partition of t for values from
>> ('2024-01-01'::timestamp, minvalue) to ('2024-02-01'::timestamp, maxvalue);
>> CREATE TABLE
>> xof=# create table t2 partition of t for values from
>> ('2024-02-01'::timestamp, minvalue) to ('2024-03-01'::timestamp, maxvalue);
>> ERROR: partition "t2" would overlap partition "t1"
>> LINE 1: ...on of t for values from ('2024-02-01'::timestamp, minvalue) ...
>> ^
>> xof=#
>
> In what way do those partitions overlap? In other words, there does not
> appear to be a value of (ts, pk) having '2024-01-01'::timestamp <= ts <
> '2024-03-01'::timestamp for any pk where it would be ambiguous which
> partition that row would go into. (I'm imagining it on a cartesian plane,
> and there isn't any overlap between the rectangles those partition boundaries
> define.)
>
> I'm obviously missing something, but... what? Thank you!
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> +64-4-386-0529
>
> National Institute of Water & Atmospheric Research Ltd (NIWA)
> 301 Evans Bay Parade Hataitai Wellington New Zealand
> Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitter Instagram YouTubeTo
> ensure compliance with legal requirements and to maintain cyber security
> standards, NIWA's IT systems are subject to ongoing monitoring, activity
> logging and auditing. This monitoring and auditing service may be provided by
> third parties. Such third parties can access information transmitted to,
> processed by and stored on NIWA's IT systems.
> Note: This email is intended solely for the use of the addressee and may
> contain information that is confidential or subject to legal professional
> privilege. If you receive this email in error please immediately notify the
> sender and delete the email.
