From: Justin Pryzby <pryzby@telsasoft.com>
To: James Robertson <james@jsrobertson.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: multicolumn partitioning help
Date: Tue, 14 Mar 2023 12:54:35 -0500
Message-ID: <ZBC029JYqZFbsIHH@telsasoft.com> (raw)
In-Reply-To: <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
References: <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
On Sun, Mar 12, 2023 at 01:59:32PM -0400, James Robertson wrote:
> Hey folks,
> I am having issues with multicolumn partitioning. For reference I am using
> the following link as my guide:
> https://www.postgresql.org/docs/devel/sql-createtable.html
>
> Reading the documentation: "When creating a range partition, the lower
> bound specified with FROM is an inclusive bound, whereas the upper bound
> specified with TO is an exclusive bound".
>
> However I can't insert any of the following after the first one, because it
> says it overlaps. Do I need to do anything different when defining
> multi-column partitions?
The bounds are compared like rows:
When creating a range partition, the lower bound specified with FROM is
an inclusive bound, whereas the upper bound specified with TO is an
exclusive bound. That is, the values specified in the FROM list are
valid values of the corresponding partition key columns for this
partition, whereas those in the TO list are not. Note that this
statement must be understood according to the rules of row-wise
comparison (Section 9.24.5). 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.
https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON> This works:
> CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0')
> TO (1969, '1');
This table is everything from 1968 (starting with '0') to 1969
> These fail:
> CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1')
> TO (1969, '2');
Which is why these are overlapping.
> CREATE TABLE humans_1969_1 PARTITION OF humans FOR VALUES FROM (1969, '1')
> TO (1970, '2');
This one doesn't fail, but it "occupies" / subjugates all of 1969
starting with 1.
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, james@jsrobertson.net
Subject: Re: multicolumn partitioning help
In-Reply-To: <ZBC029JYqZFbsIHH@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox