Re: Find out the indexed columns

Hi Tom and All,

Below query is not listing the particular expression based index created.

SELECT distinct i.relname as index_name, ix.indisprimary as contype 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.relname = 'test' and ix.indisunique = 't' AND
ix.indisprimary = 'f' AND a.attname <> 'sequence_number'

Example:
prod=# SELECT indexdef FROM pg_indexes WHERE indexname = 'test_u01';
indexdef
------------------------------------------------------------------
CREATE UNIQUE INDEX test_idx1 ON test USING btree ((+
CASE inet_type +
WHEN 2 THEN a +
WHEN 4 THEN a +
ELSE NULL::character varying +
END), ( +
CASE inet_type +
WHEN 2 THEN ipaddress +
WHEN 4 THEN ipaddress +
ELSE NULL::character varying +
END), ( +
CASE inet_type +
WHEN 2 THEN port +
WHEN 4 THEN port +
ELSE NULL::bigint +
END))
(1 row)

How do I list all the indexes created based on CASE statements or
expressions?

On Mon, May 8, 2023 at 9:52 PM Nikhil Ingale <niks(dot)bgm(at)gmail(dot)com> wrote:

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