Re: Get info about the index

[Igor Korot <ikorot01@xxxxxxxxx>]

Hi, guys,

On Mon, Jul 28, 2025 at 10:13 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
>
> On Mon, 2025-07-28 at 08:19 -0500, David Barbour wrote:
> > Couple of suggestions.  You might try ChatGPT.
>
> Please don't be insulting.  He asked for real information.

I finally formulate my google request and got this:

https://www.google.com/search?q=get+the+index+field+info+postgresql&safe=active&sca_esv=32011946fbc5f18a&ei=i7KHaLSSEK6f5NoPpO-IgQ8&ved=0ahUKEwj0g_agieCOAxWuD1kFHaQ3IvAQ4dUDCBA&uact=5&oq=get+the+index+field+info+postgresql&gs_lp=Egxnd3Mtd2l6LXNlcnAiI2dldCB0aGUgaW5kZXggZmllbGQgaW5mbyBwb3N0Z3Jlc3FsMgUQABjvBTIIEAAYogQYiQUyCBAAGIAEGKIEMgUQABjvBUjBfFCyWViwdHABeACQAQCYAYABoAH_BaoBAzMuNLgBA8gBAPgBAZgCB6ACoAXCAgoQABiwAxjWBBhHmAMA4gMFEgExIECIBgGQBgiSBwMzLjSgB-0esgcDMi40uAebBcIHBTAuMS42yAcV&sclient=gws-wiz-serp

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?

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

Thank you.


>
> Yours,
> Laurenz Albe