From: | Nikhil Ingale <niks(dot)bgm(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Find out the indexed columns |
Date: | 2023-05-08 12:23:32 |
Message-ID: | CALXkTpw1Sf_TPLjJxWc-bg--21A8wTb126G=zeGqRGh9GgT3Ww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi All,
I have a condition based index created. I see that the postgres metadata
table doesn't return columns associated with condition based indexes.
Example:
ssdb=# 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)
The below query is unable to find the indexed columns (a,ipadress,port)
through the metadata tables for the above expression based index.
SELECT ix.indisprimary as contype,pg_get_userbyid(t.relowner) as
table_owner, t.relname as table_name, pg_get_userbyid(i.relowner) as
index_owner, i.relname as index_name, a.attname as att_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type 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 i.relname = 'test_idx1';
How do I find out the associated columns for such an index?
Regards,
nik
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-05-08 13:25:53 | Re: Find out the indexed columns |
Previous Message | Holger Jakobs | 2023-05-08 11:48:30 | Re: unknown postgres ssl error "could not accept SSL connection: Success" and timeout |