Subject: Re: Get info about the index

From: Igor Korot <[email-masked]>
Date: Fri, 1 Aug 2025 19:06:13 -0500

Hi, Laurenz,

On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe <[email-masked]> 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 = 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)

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

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 1 Aug 2025 17:13:32 -0700

> WHERE condition is actually present.

No, it refers to:

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

-- 
Adrian Klaver
[email-masked]