MIME-Version: 1.0
References: <CA+FnnTw2ouEQKSKzQ_SNo8efWE8wEqjr9Ux-F-wbR+y=Pn6bGg@mail.gmail.com>
 <090d8588-e74e-4436-8f69-05ce8624379b@aklaver.com> <CA+FnnTyKb0cLp9GF3YENoBZ4LVW3db_RSGR99BPFnA4BZtc8kQ@mail.gmail.com>
 <0BC8BEBF-3B1F-4E69-A1B9-CCCDF9617168@thebuild.com> <CA+FnnTy8bdXxFZBmFfm1oNMQLVxL-+UmkX8EecbLB0MehX8gFw@mail.gmail.com>
 <CAKfDxxw=4Vax5Oq0apoAE=RkzNgHwvHzKX1ejE6z7n7_vtGBUQ@mail.gmail.com>
 <CAEMHB2QQpfxjbqbyB6zy+TMv9zf42tavLuYUSxDXgyZEaq46Xg@mail.gmail.com>
 <7281c379994ffc70cebb68423f5b8ba339d0d75f.camel@cybertec.at>
 <CA+FnnTyFwR_Xk8pEhasG7GGWAzFk9e4tCBdX36G6H1-=uJxrfg@mail.gmail.com> <2fad4427bff83bc73f9b4215e284a76343f046f2.camel@cybertec.at>
In-Reply-To: <2fad4427bff83bc73f9b4215e284a76343f046f2.camel@cybertec.at>
From: Igor Korot <ikorot01@gmail.com>
Date: Fri, 1 Aug 2025 19:06:13 -0500
Message-ID: <CA+FnnTzA5+78w3UbkQCTd74GPqpB=D=tu0eEccqX9301TTcynQ@mail.gmail.com>
Subject: Re: Get info about the index
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: David Barbour <david.barbour@amiralearning.com>, Jon Zeppieri <zeppieri@gmail.com>, 
	Christophe Pettus <xof@thebuild.com>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTzA5%2B78w3UbkQCTd74GPqpB%3DD%3Dtu0eEccqX9301TTcynQ%40mail.gmail.com>
Precedence: bulk

Hi, Laurenz,

On Tue, Jul 29, 2025 at 7:07=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec=
.at> wrote:
>
> On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote:
> > SELECT
> >     t.relname AS table_name,
> >     i.relname AS index_name,
> >     a.attname AS column_name
> > FROM
> >     pg_class t,
> >     pg_class i,
> >     pg_index ix,
> >     pg_attribute a
> > WHERE
> >     t.oid =3D ix.indrelid AND
> >     i.oid =3D ix.indexrelid AND
> >     a.attrelid =3D t.oid AND
> >     a.attnum =3D ANY(ix.indkey) AND
> >     t.relkind =3D 'r' AND -- 'r' for regular table
> >     t.relname =3D 'your_table_name' -- Optional: filter by table name
> > ORDER BY
> >     t.relname,
> >     i.relname,
> >     a.attnum;
> >
> > I can build on top of this query, however I have 2 issues:
> >
> > First and most important one - they are filtering by just table name.
> > How can I filter by the fully qualified name - catalog.schema.table?
>
> "catalog" is irrelevant, since PostgreSQL doesn't allow cross-database qu=
eries.
>
> To add a filter for the schema, use
>
>    AND t.relnamespace =3D 'schemaname'::regnamespace
>
> > Second - how cn I get the partial index condition? Either the whole
> > WHERE clause (which I will have to parse)
> > or the broken down one (field, condition {AND|OR} field, condition}?
>
>    SELECT pg_get_expr(ix.indpred, t.oid)

I'm looking at the pg_index table and I see it has:

[quote]
indisexclusion bool

If true, this index supports an exclusion constraint
[/quote]

If I read the docs correctly, this field indicates whether the
WHERE condition is actually present.

Am I right?

Thank you.

>
> Yours,
> Laurenz Albe