Re: Get info about the index

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 = ix.indrelid AND
>     i.oid = ix.indexrelid AND
>     a.attrelid = t.oid AND
>     a.attnum = ANY(ix.indkey) AND
>     t.relkind = 'r' AND -- 'r' for regular table
>     t.relname = '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 queries.

To add a filter for the schema, use

   AND t.relnamespace = '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