MIME-Version: 1.0
References: <CA+FnnTw2ouEQKSKzQ_SNo8efWE8wEqjr9Ux-F-wbR+y=Pn6bGg@mail.gmail.com>
 <090d8588-e74e-4436-8f69-05ce8624379b@aklaver.com> <CA+FnnTyKb0cLp9GF3YENoBZ4LVW3db_RSGR99BPFnA4BZtc8kQ@mail.gmail.com>
 <0BC8BEBF-3B1F-4E69-A1B9-CCCDF9617168@thebuild.com> <CA+FnnTy8bdXxFZBmFfm1oNMQLVxL-+UmkX8EecbLB0MehX8gFw@mail.gmail.com>
 <CAKfDxxw=4Vax5Oq0apoAE=RkzNgHwvHzKX1ejE6z7n7_vtGBUQ@mail.gmail.com>
 <CAEMHB2QQpfxjbqbyB6zy+TMv9zf42tavLuYUSxDXgyZEaq46Xg@mail.gmail.com> <7281c379994ffc70cebb68423f5b8ba339d0d75f.camel@cybertec.at>
In-Reply-To: <7281c379994ffc70cebb68423f5b8ba339d0d75f.camel@cybertec.at>
From: Igor Korot <ikorot01@gmail.com>
Date: Tue, 29 Jul 2025 06:46:53 -0500
Message-ID: <CA+FnnTyFwR_Xk8pEhasG7GGWAzFk9e4tCBdX36G6H1-=uJxrfg@mail.gmail.com>
Subject: Re: Get info about the index
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: David Barbour <david.barbour@amiralearning.com>, Jon Zeppieri <zeppieri@gmail.com>, 
	Christophe Pettus <xof@thebuild.com>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTyFwR_Xk8pEhasG7GGWAzFk9e4tCBdX36G6H1-%3DuJxrfg%40mail.gmail.com>
Precedence: bulk

Hi, guys,

On Mon, Jul 28, 2025 at 10:13=E2=80=AFAM Laurenz Albe <laurenz.albe@cyberte=
c.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=3Dget+the+index+field+info+postgresql&safe=
=3Dactive&sca_esv=3D32011946fbc5f18a&ei=3Di7KHaLSSEK6f5NoPpO-IgQ8&ved=3D0ah=
UKEwj0g_agieCOAxWuD1kFHaQ3IvAQ4dUDCBA&uact=3D5&oq=3Dget+the+index+field+inf=
o+postgresql&gs_lp=3DEgxnd3Mtd2l6LXNlcnAiI2dldCB0aGUgaW5kZXggZmllbGQgaW5mby=
Bwb3N0Z3Jlc3FsMgUQABjvBTIIEAAYogQYiQUyCBAAGIAEGKIEMgUQABjvBUjBfFCyWViwdHABe=
ACQAQCYAYABoAH_BaoBAzMuNLgBA8gBAPgBAZgCB6ACoAXCAgoQABiwAxjWBBhHmAMA4gMFEgEx=
IECIBgGQBgiSBwMzLjSgB-0esgcDMi40uAebBcIHBTAuMS42yAcV&sclient=3Dgws-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 =3D ix.indrelid AND
    i.oid =3D ix.indexrelid AND
    a.attrelid =3D t.oid AND
    a.attnum =3D ANY(ix.indkey) AND
    t.relkind =3D 'r' AND -- 'r' for regular table
    t.relname =3D '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