Message-ID: <2fad4427bff83bc73f9b4215e284a76343f046f2.camel@cybertec.at>
Subject: Re: Get info about the index
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Igor Korot <ikorot01@gmail.com>
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>
Date: Tue, 29 Jul 2025 14:07:25 +0200
In-Reply-To: <CA+FnnTyFwR_Xk8pEhasG7GGWAzFk9e4tCBdX36G6H1-=uJxrfg@mail.gmail.com>
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/2fad4427bff83bc73f9b4215e284a76343f046f2.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote:
> SELECT
> =C2=A0=C2=A0=C2=A0 t.relname AS table_name,
> =C2=A0=C2=A0=C2=A0 i.relname AS index_name,
> =C2=A0=C2=A0=C2=A0 a.attname AS column_name
> FROM
> =C2=A0=C2=A0=C2=A0 pg_class t,
> =C2=A0=C2=A0=C2=A0 pg_class i,
> =C2=A0=C2=A0=C2=A0 pg_index ix,
> =C2=A0=C2=A0=C2=A0 pg_attribute a
> WHERE
> =C2=A0=C2=A0=C2=A0 t.oid =3D ix.indrelid AND
> =C2=A0=C2=A0=C2=A0 i.oid =3D ix.indexrelid AND
> =C2=A0=C2=A0=C2=A0 a.attrelid =3D t.oid AND
> =C2=A0=C2=A0=C2=A0 a.attnum =3D ANY(ix.indkey) AND
> =C2=A0=C2=A0=C2=A0 t.relkind =3D 'r' AND -- 'r' for regular table
> =C2=A0=C2=A0=C2=A0 t.relname =3D 'your_table_name' -- Optional: filter by=
 table name
> ORDER BY
> =C2=A0=C2=A0=C2=A0 t.relname,
> =C2=A0=C2=A0=C2=A0 i.relname,
> =C2=A0=C2=A0=C2=A0 a.attnum;
>=20
> I can build on top of this query, however I have 2 issues:
>=20
> 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 quer=
ies.

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)

Yours,
Laurenz Albe