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)
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.bgm@xxxxxxxxx> wrote:
This is very helpful for me. Thank you very much Tom.On Mon, May 8, 2023 at 7:08 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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 = "" 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 = "" and objid = 'ti'::regclass;
pg_describe_object
-------------------------
table t
column f1 of table t
function fipshash(text)
(3 rows)
regards, tom lane
