Re: Partitioning by month causing an error?

--000000000000bc43fc059a2ee35f
Content-Type: text/plain; charset="UTF-8"

This is really interesting to me: why? Running select '2019-04-30'::date
between '2019-04-01'::date and '2019-04-30'::date; yields true, and
generally the lower and upper bounds are inclusive in any kind of between
check for languages I am familiar with.

Appreciate the answer, just curious. Thanks as always Tom.

On Fri, Dec 20, 2019 at 6:59 PM Tom Lane <[email protected]> wrote:

> Wells Oliver <[email protected]> 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
>


-- 
Wells Oliver
[email protected] <[email protected]>

--000000000000bc43fc059a2ee35f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>This is really interesting to me: why? Running <span =
style=3D"font-family:monospace">select &#39;2019-04-30&#39;::date between &=
#39;2019-04-01&#39;::date and &#39;2019-04-30&#39;::date</span>; yields tru=
e, and generally the lower and upper bounds are inclusive in any kind of be=
tween check for languages I am familiar with.</div><div><br></div><div>Appr=
eciate the answer, just curious. Thanks as always Tom.<br></div></div><br><=
div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Dec=
 20, 2019 at 6:59 PM Tom Lane &lt;<a href=3D"mailto:[email protected]">tgl@=
sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">Wells Oliver &lt;<a href=3D"mailto:[email protected]" tar=
get=3D"_blank">[email protected]</a>&gt; writes:<br>
&gt; I have a partition per each month, like this:<br>
&gt; CREATE TABLE mmd_yr2019_3 PARTITION OF mmd FOR VALUES FROM (&#39;2019-=
03-01&#39;)<br>
&gt; TO (&#39;2019-03-31&#39;);<br>
&gt; CREATE TABLE mmd_yr2019_4 PARTITION OF mmd FOR VALUES FROM (&#39;2019-=
04-01&#39;)<br>
&gt; TO (&#39;2019-04-30&#39;);<br>
<br>
&gt; This breaks for dates at the end of the month. What&#39;s the better w=
ay to<br>
&gt; write the bounds? 3/1 to 4/1 and then 4/1 to 5/1? Does that cause some=
<br>
&gt; overlap issue?<br>
<br>
The rule is that a range partition from A to B covers values A &lt;=3D X &l=
t; B.<br>
So you should be using first-of-the-month dates for all of these<br>
values.=C2=A0 As you have it, there&#39;s gaps in the allowable values,<br>
totally aside from the PITA factor of having to identify the last<br>
day of each month accurately.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div><br clear=3D"all"><br>-- <br><div dir=3D"ltr" class=3D"g=
mail_signature"><div dir=3D"ltr"><div>Wells Oliver<br><a href=3D"mailto:wel=
[email protected]" target=3D"_blank">[email protected]</a></div></div=
></div>

--000000000000bc43fc059a2ee35f--