From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wells Oliver <wells.oliver@gmail.com>
cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Partitioning by month causing an error?
In-reply-to: 
 <CAOC+FBW=zSLyHWg2XDTp2eeEAmwzkzZLQPBqFx7_Cso=WMK3ew@mail.gmail.com>
References: 
 <CAOC+FBW=zSLyHWg2XDTp2eeEAmwzkzZLQPBqFx7_Cso=WMK3ew@mail.gmail.com>
Comments: In-reply-to Wells Oliver <wells.oliver@gmail.com>
	message dated "Fri, 20 Dec 2019 16:49:12 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9225.1576897186.1@sss.pgh.pa.us>
Date: Fri, 20 Dec 2019 21:59:46 -0500
Message-ID: <9226.1576897186@sss.pgh.pa.us>
Precedence: bulk

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.

			regards, tom lane