Postgres Pro
Downloads
Home   >   mailing lists

Thread: check for primary key

check for primary key

From
Tom Brown
Date:
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

Re: check for primary key

From
Thom Brown
Date:
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

Re: check for primary key

From
Thom Brown
Date:
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