Re: database introspection error

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Jon Nelson" <jnelson+pgsql(at)jamponi(dot)net>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: database introspection error
Date: 2011-04-22 17:19:05
Message-ID: 4DB17239020000250003CBF2@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Jon Nelson <jnelson+pgsql(at)jamponi(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bryant, Alex 2011-04-22 19:29:16 Upgrading from 1.10 to 1.12 - cannot set up server
Previous Message Jon Nelson 2011-04-22 17:00:18 Re: database introspection error