2009/11/9 Tom Brown <brown@xxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
