MIME-Version: 1.0
References: <CAB+=1TUgzD7CfGbgiBAgcuttN06X11787WvJSeCPs=ek5qCgxw@mail.gmail.com>
 <CAKAnmmLRnzV2C6XBzu64Let3jtABh1P2sGbHBcfAfKpT22ufVQ@mail.gmail.com>
In-Reply-To: <CAKAnmmLRnzV2C6XBzu64Let3jtABh1P2sGbHBcfAfKpT22ufVQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 9 Aug 2024 00:20:04 -0400
Message-ID: <CANzqJaCs9ZBQvHsu3AhpdoQpry7qTg8KU2JNE-XyFZfO3cq3tg@mail.gmail.com>
Subject: Re: Getting specific partition from the partition name
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d2dd45061f387500"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCs9ZBQvHsu3AhpdoQpry7qTg8KU2JNE-XyFZfO3cq3tg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Aug 8, 2024 at 4:46=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail.=
com>
wrote:

> YYYY_MM_DD is already setup for sorting, so just do:
>
> SELECT table_name FROM information_schema.tables WHERE table_name ~
> 'table_part_p' ORDER BY 1 DESC;
>
> If you need to grab the numbers:
>
> SELECT substring('table_part_p2022_03_04' from '([\d_]+)$');
>

What if the partitions aren't all rationally named?  There *must* be a pg_*
table out there which contains the partition boundaries...

--=20
Death to America, and butter sauce.
Iraq lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Aug 8, 2024 at 4:46=E2=80=AFPM Gr=
eg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.=
com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"ltr">YYYY_MM_DD is already setup =
for sorting, so just do:<div><br></div><div>SELECT table_name FROM informat=
ion_schema.tables WHERE table_name ~ &#39;table_part_p&#39; ORDER BY 1 DESC=
;<br></div><div><br></div><div>If you need to grab the numbers:</div><div><=
br></div><div>SELECT substring(&#39;table_part_p2022_03_04&#39; from &#39;(=
[\d_]+)$&#39;);<br></div><div></div></div></blockquote></div><div><br></div=
><div>What if the partitions aren&#39;t all rationally named?=C2=A0 There <=
i>must</i>=C2=A0be a pg_* table out there which contains the partition boun=
daries...</div><div><br></div><span class=3D"gmail_signature_prefix">-- </s=
pan><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death t=
o America, and butter sauce.<div>Iraq lobster!</div></div></div></div>

--000000000000d2dd45061f387500--