MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Fri, 9 Aug 2024 01:22:36 +0530
Message-ID: <CAB+=1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs=ek5qCgxw@mail.gmail.com>
Subject: Getting specific partition from the partition name
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000012acca061f315f0a"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs%3Dek5qCgxw%40mail.gmail.com>
Precedence: bulk

--00000000000012acca061f315f0a
Content-Type: text/plain; charset="UTF-8"

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 something
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 but
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 of
the partitions by the date and we can get hold of the specific nth
partition in that table?

Regards
Veem

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

<div dir=3D"ltr">Hi ,<br><div>We are using=C2=A0postgres version 15.4. We h=
ave a range partition table and the partition naming convention is generate=
d by pg_partman and is something like &quot;table_name&gt;_pYYYY_MM_DD&quot=
;.</div><div><br></div><div>We have a requirement=C2=A0of extracting specif=
ic partitions ordered by the date criteria and also do some operations on t=
hat 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;ta=
ble_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; curre=
nt_date<br></div><div><br></div><div><div>or is there any ready-made=C2=A0d=
ata dictionary which will give us the order of the partitions by the date a=
nd 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>

--00000000000012acca061f315f0a--