MIME-Version: 1.0
References: <CAB+=1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs=ek5qCgxw@mail.gmail.com>
In-Reply-To: <CAB+=1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs=ek5qCgxw@mail.gmail.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Fri, 9 Aug 2024 19:35:12 +0200
Message-ID: <CAKkG4_knN0ayoanYSzMuG5PJBkv7q+kBmBJCobxq9kFv24Z-YQ@mail.gmail.com>
Subject: Re: Getting specific partition from the partition name
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000072decb061f4391a1"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_knN0ayoanYSzMuG5PJBkv7q%2BkBmBJCobxq9kFv24Z-YQ%40mail.gmail.com>
Precedence: bulk

--00000000000072decb061f4391a1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

If you want to convert your table name into a timestamp, you don't need
substring or similar. This also works:

=3D# select to_date('table_part_p2024_08_08', '"table_part_p"YYYY"_"MM"_"DD=
');
  to_date
------------
 2024-08-08
(1 row)

But as Greg said, your strings are perfectly sortable.


On Thu, Aug 8, 2024 at 9:52=E2=80=AFPM veem v <veema0000@gmail.com> wrote:

> Hi ,
> We are using postgres version 15.4. We have a range partition table and
> the partition naming convention is generated by pg_partman and is somethi=
ng
> like "table_name>_pYYYY_MM_DD".
>
> We have a requirement of extracting specific partitions ordered by the
> date criteria and also do some operations on that specific date. But I am
> struggling and it's not working as expected.I tried something as below bu=
t
> it's not working.Can somebody guide me here please.
>
>  to_date( substring('table_part_p2024_08_08' from
> '_p(\d{4})_(\d{2})_(\d{2})'),      'YYYY_MM_DD'
>     ) < current_date
>
> or is there any ready-made data dictionary which will give us the order o=
f
> the partitions by the date and we can get hold of the specific nth
> partition in that table?
>
> Regards
> Veem
>

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

<div dir=3D"ltr"><div>If you want to convert your table name into a timesta=
mp, you don&#39;t need substring or similar. This also works:</div><div><br=
></div>=3D# select to_date(&#39;table_part_p2024_08_08&#39;, &#39;&quot;tab=
le_part_p&quot;YYYY&quot;_&quot;MM&quot;_&quot;DD&#39;);<br>=C2=A0 to_date =
=C2=A0 <br>------------<br>=C2=A02024-08-08<br>(1 row)<br><div><br></div><d=
iv>But as Greg said, your strings are perfectly sortable.</div><div><br></d=
iv></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_att=
r">On Thu, Aug 8, 2024 at 9:52=E2=80=AFPM veem v &lt;<a href=3D"mailto:veem=
a0000@gmail.com">veema0000@gmail.com</a>&gt; wrote:<br></div><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi ,<br><div>We are us=
ing=C2=A0postgres version 15.4. We have a range partition table and the par=
tition naming convention is generated by pg_partman and is something like &=
quot;table_name&gt;_pYYYY_MM_DD&quot;.</div><div><br></div><div>We have a r=
equirement=C2=A0of extracting specific partitions ordered by the date crite=
ria and also do some operations on that specific date. But I am struggling =
and it&#39;s not working as expected.I tried something as below but it&#39;=
s not working.Can somebody guide me here please.</div><div><br></div><div>=
=C2=A0to_date(=C2=A0substring(&#39;table_part_p2024_08_08&#39; from &#39;_p=
(\d{4})_(\d{2})_(\d{2})&#39;),=C2=A0 =C2=A0 =C2=A0 &#39;YYYY_MM_DD&#39;<br>=
</div><div>=C2=A0 =C2=A0 ) &lt; current_date<br></div><div><br></div><div><=
div>or is there any ready-made=C2=A0data dictionary which will give us the =
order of the partitions by the date and we can get hold of the specific=C2=
=A0nth partition in that table?</div></div><div><br></div><div>Regards</div=
><div>Veem</div></div>
</blockquote></div>

--00000000000072decb061f4391a1--