Re: check for primary key

2009/11/9 Tom Brown <brown(at)esteem(dot)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!)