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
