How relate pg_class to pg_constraint

Bill Todd wrote:

I need to join pg_class and pg_constraint to get information about
constraints on a table. It appears that pg_constraint.conrelid is the
foreign key but I do not see a relid column in pg_class. What
column(s) define the relationship between these tables? Thanks.

Bill

Is the relationship pg_constraint.conrelid = pg_class.oid? I assume the
column lists for the system tables do not include the oid column because
everyone (but us newbies) knows that every system table has an oid
column.<g>

Bill

Bill Todd <pg@dbginc.com> writes:

I need to join pg_class and pg_constraint to get information about constraints
on a table. It appears that pg_constraint.conrelid is the foreign key but I do
not see a relid column in pg_class. What column(s) define the relationship
between these tables? Thanks.

There's a system column called "oid" on all the system tables which is the
primary key. It doesn't show up unless you explicitly list it in the target
list of the select.

So you need a join like WHERE pg_class.oid = conrelid

If all you need is the name to display for users then there's a convenience
type called regclass which you can use by doing "SELECT conrelid::regclass
from pg_constraint". There are similar regtype and a few others like it too.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!