Re: check for primary key

2009/11/9 Tom Brown <brown@esteem.com>:
> Hey guys!
>
> I would like to know how to query for the existence of a primary key on
> a table. All of the tables have the column 'id' in them. That should be
> the primary key. However, the primary key constraint got "lost" somehow.
> Probably due to stupidity on my part.
>
> I just want to add back the primary key constraint to the tables that
> lost it. I want to query for the existance of it first, so I don't
> produce an error when I do an 'ALTER TABLE ...' statement.
>
> Any suggestions appreciated.
>
> Thanks!
> Tom

You could try:

SELECT COUNT(*)
FROM pg_class
INNER JOIN pg_constraint ON pg_class.oid = pg_constraint.conrelid
WHERE pg_constraint.contype = 'p'
AND pg_class.relname = 'my_table';

...replacing my_table with the table name.  If it returns 1, it has a
primary key.  Or use * instead of COUNT(*) and use no rows being
returned as a lack of primary key.

Regards

Thom Brown (hey, we have the same name!)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general