From: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning by month causing an error?
Date: Fri, 20 Dec 2019 22:31:35 -0600
Message-ID: <9fe4b503-4be4-4ed5-4299-d7c49c8921b4@gmail.com> (raw)
In-Reply-To: <19102.1576901426@sss.pgh.pa.us>
References: <CAOC+FBW=zSLyHWg2XDTp2eeEAmwzkzZLQPBqFx7_Cso=WMK3ew@mail.gmail.com>
<9226.1576897186@sss.pgh.pa.us>
<8ba56626-3b91-6ac8-34a9-ab2c9ca2b8c6@gmail.com>
<19102.1576901426@sss.pgh.pa.us>
On 12/20/19 10:10 PM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> On 12/20/19 8:59 PM, Tom Lane wrote:
>>> The rule is that a range partition from A to B covers values A <= X < B.
>> If I were to ask to count *from* 1 *to* 100, you'd count from 1 to 100,
>> *not* 1 to 99. Who decided to arbitrarily break a grammatical rule we
>> (native English speakers, and I bet everyone else, too) all learn as
>> children, causing all sorts of needless confusion and breakage?
> This rule breaks fewer things than a rule of A <= X <= B would. In
> particular, for more-or-less-continuous values like float8s or timestamps,
> you really really don't want to have to write down
> the-last-possible-value-before-the-start-of-the-next-partition.
Why not? For 20+ years (not on Postgres) I've been specifying partition
limits where the upper limits are the edge of timestamps (octaword integers
counting the number of 100ns ticks since November 17, 1858), and have
*never* had a problem:
IN ETC_TRAN_DETAIL_201909_UA WITH LIMIT OF '2019-09-30 23:59:59.99',
IN ETC_TRAN_DETAIL_201910_UA WITH LIMIT OF '2019-10-31 23:59:59.99',
IN ETC_TRAN_DETAIL_201911_UA WITH LIMIT OF '2019-11-30 23:59:59.99',
IN ETC_TRAN_DETAIL_201912_UA WITH LIMIT OF '2019-12-31 23:59:59.99',
IN ETC_TRAN_DETAIL_202001_UA WITH LIMIT OF '2020-01-31 23:59:59.99',
IN ETC_TRAN_DETAIL_202002_UA WITH LIMIT OF '2020-02-28 23:59:59.99',
etc, etc, etc
Of course, I've also never partitioned a table on a float, thinking that's
utterly daft.
--
Angular momentum makes the world go 'round.
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-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning by month causing an error?
In-Reply-To: <9fe4b503-4be4-4ed5-4299-d7c49c8921b4@gmail.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