Re: Get info about the index

Hi, guys,

On Mon, Jul 28, 2025 at 10:13 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 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