Re: Get info about the index

On Tue, Jul 29, 2025 at 7:07 AM 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 = 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.

The reason I'm asking this is because I want to have the same
interface for different DBMSes.

But it also made me curious.

If I have a database for some financial data for the year 2024 in the
mydb_2024 and I have current year financial data in the mydb_2025 how
can I compare the data?

>
> 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

Thank you.