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

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

On Fri, 9 Aug 2024 at 06:20, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

>
> What if the partitions aren't all rationally named?  There *must* be a
> pg_* table out there which contains the partition boundaries...
>
>
The pg_class column relpartbound contains an internal representation of the
partition boundary, when applicable.
You can decompile it into the canonical text format with pg_get_expr( expr
pg_node_tree, relation oid [, pretty boolean ] ) =E2=86=92 text.
So:
    create table t(x int primary key) partition by list(x);
    create table u partition of t for values in (0,1);
    create table v partition of t for values in (2,3,4,5,6,7,8,9);
    select oid::regclass,pg_get_expr(relpartbound,oid) from pg_class where
relkind=3D'r' and relispartition;
 oid |              pg_get_expr
-----+----------------------------------------
 u   | FOR VALUES IN (0, 1)
 v   | FOR VALUES IN (2, 3, 4, 5, 6, 7, 8, 9)
(2 rows)

Best,
Giovanni

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, 9 Aug 2024 at 06:20, Ron John=
son &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com<=
/a>&gt; wrote:</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"ltr"><div></div></div></blockquote></div><div><br=
></div><div>What if the partitions aren&#39;t all rationally named?=C2=A0 T=
here <i>must</i>=C2=A0be a pg_* table out there which contains the partitio=
n boundaries...</div><div><br></div></div></blockquote><div><br></div><div>=
The pg_class column relpartbound contains an internal representation of the=
 partition boundary, when applicable.</div><div>You can decompile it into t=
he canonical text format with pg_get_expr( expr pg_node_tree, relation oid =
[, pretty boolean ] ) =E2=86=92 text.</div><div>So:</div><div>=C2=A0 =C2=A0=
 create table t(x int primary key) partition by list(x);<br>=C2=A0 =C2=A0 c=
reate table u partition of t for values in (0,1);<br>=C2=A0 =C2=A0 create t=
able v partition of t for values in (2,3,4,5,6,7,8,9);<br>=C2=A0 =C2=A0 sel=
ect oid::regclass,pg_get_expr(relpartbound,oid) from pg_class where relkind=
=3D&#39;r&#39; and relispartition;</div>=C2=A0oid | =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_get_expr<br>-----+---------------------------=
-------------<br>=C2=A0u =C2=A0 | FOR VALUES IN (0, 1)<br>=C2=A0v =C2=A0 | =
FOR VALUES IN (2, 3, 4, 5, 6, 7, 8, 9)<br>(2 rows)</div><div class=3D"gmail=
_quote"><br></div><div class=3D"gmail_quote">Best,</div><div class=3D"gmail=
_quote">Giovanni</div><div class=3D"gmail_quote"><br><div><span class=3D"gm=
ail-ui-provider gmail-a gmail-b gmail-c gmail-d gmail-e gmail-f gmail-g gma=
il-h gmail-i gmail-j gmail-k gmail-l gmail-m gmail-n gmail-o gmail-p gmail-=
q gmail-r gmail-s gmail-t gmail-u gmail-v gmail-w gmail-x gmail-y gmail-z g=
mail-ab gmail-ac gmail-ae gmail-af gmail-ag gmail-ah gmail-ai gmail-aj gmai=
l-ak" dir=3D"ltr"><br></span></div><div>=C2=A0</div></div></div>

--00000000000036ccf4061f41e346--