Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> Would the following query be more (most?) correct, assuming the
> oid of the table is known?
It doesn't seem to work for indexes on expressions.
Try this:
create table t (id int not null primary key, txt text);
create index t_weird on t ((substring(txt, 1, id)));
create index t_txt_notnull on t (txt) where txt is not null;
SELECT
i.relname as relname,
ix.indisunique, ix.indexprs, ix.indpred,
a.attname as column_name
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.relkind = 'r'
and t.oid = (select oid from pg_class where relname = 't')
ORDER BY
t.relname,
i.relname
;
-Kevin
