Re: Find out the indexed columns

This is very helpful for me. Thank you very much Tom.

On Mon, May 8, 2023 at 7:08 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > You could get back the column name(s) by joining to pg_attribute,
>
> Oh, another possible approach is to use pg_describe_object,
> which'd be interesting if you also want to handle non-column
> dependencies. For example,
>
> regression=# drop table t;
> DROP TABLE
> regression=# create table t(f1 text);
> CREATE TABLE
> regression=# create index ti on t (fipshash(f1));
> CREATE INDEX
> regression=# select * from pg_depend where classid = 'pg_class'::regclass
> and objid = 'ti'::regclass;
> classid | objid | objsubid | refclassid | refobjid | refobjsubid |
> deptype
>
> ---------+-------+----------+------------+----------+-------------+---------
> 1259 | 48632 | 0 | 1259 | 48627 | 0 | a
> 1259 | 48632 | 0 | 1259 | 48627 | 1 | a
> 1259 | 48632 | 0 | 1255 | 16501 | 0 | n
> (3 rows)
>
> regression=# select pg_describe_object(refclassid, refobjid, refobjsubid)
> from pg_depend where classid = 'pg_class'::regclass and objid =
> 'ti'::regclass;
> pg_describe_object
> -------------------------
> table t
> column f1 of table t
> function fipshash(text)
> (3 rows)
>
> regards, tom lane
>