Subject: Re: Partitioning by month causing an error?
To: pgsql-admin@lists.postgresql.org
References: 
 <CAOC+FBW=zSLyHWg2XDTp2eeEAmwzkzZLQPBqFx7_Cso=WMK3ew@mail.gmail.com>
 <9226.1576897186@sss.pgh.pa.us>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <8ba56626-3b91-6ac8-34a9-ab2c9ca2b8c6@gmail.com>
Date: Fri, 20 Dec 2019 21:42:36 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: <9226.1576897186@sss.pgh.pa.us>
Content-Type: multipart/alternative;
 boundary="------------AB7050DE567EAD0440184491"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------AB7050DE567EAD0440184491
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 12/20/19 8:59 PM, Tom Lane wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
>> I have a partition per each month, like this:
>> CREATE TABLE mmd_yr2019_3 PARTITION OF mmd FOR VALUES FROM ('2019-03-01')
>> TO ('2019-03-31');
>> CREATE TABLE mmd_yr2019_4 PARTITION OF mmd FOR VALUES FROM ('2019-04-01')
>> TO ('2019-04-30');
>> This breaks for dates at the end of the month. What's the better way to
>> write the bounds? 3/1 to 4/1 and then 4/1 to 5/1? Does that cause some
>> overlap issue?
> The rule is that a range partition from A to B covers values A <= X < B.
> So you should be using first-of-the-month dates for all of these
> values.  As you have it, there's gaps in the allowable values,
> totally aside from the PITA factor of having to identify the last
> day of each month accurately.

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?

-- 
Angular momentum makes the world go 'round.

--------------AB7050DE567EAD0440184491
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 12/20/19 8:59 PM, Tom Lane wrote:<br>
    <blockquote type="cite" cite="mid:9226.1576897186@sss.pgh.pa.us">
      <pre class="moz-quote-pre" wrap="">Wells Oliver <a class="moz-txt-link-rfc2396E" href="mailto:wells.oliver@gmail.com">&lt;wells.oliver@gmail.com&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">I have a partition per each month, like this:
CREATE TABLE mmd_yr2019_3 PARTITION OF mmd FOR VALUES FROM ('2019-03-01')
TO ('2019-03-31');
CREATE TABLE mmd_yr2019_4 PARTITION OF mmd FOR VALUES FROM ('2019-04-01')
TO ('2019-04-30');
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">This breaks for dates at the end of the month. What's the better way to
write the bounds? 3/1 to 4/1 and then 4/1 to 5/1? Does that cause some
overlap issue?
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
The rule is that a range partition from A to B covers values A &lt;= X &lt; B.
So you should be using first-of-the-month dates for all of these
values.  As you have it, there's gaps in the allowable values,
totally aside from the PITA factor of having to identify the last
day of each month accurately.</pre>
    </blockquote>
    <br>
    If I were to ask to count <b>from</b> 1 <b>to</b> 100, you'd count
    from 1 to 100, <b>not</b> 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?<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------AB7050DE567EAD0440184491--