0

I am trying to learn a new schema of a PostgreSQL database, but it's unclear from looking at pgAdmin which columns per the tables constitute the primary keys. It appears that when the tables were created, they were created with the default PRIMARY KEY (primary_key_column_name)

This results in the default naming convention when checking under the constraints in pgAdmin. How can I check to see what the actual names of the columns are for the primary key for a table?

1

I'm not sure I understood what you're asking, but if your question is how to determine what columns constitute a table's primary key you can query the catalog like so:

select c.conname constraint_name, o.relname table_name, a.attname column_name
from pg_constraint c 
inner join pg_class o
   on c.conrelid = o.oid
inner join pg_attribute a 
  on c.conrelid = a.attrelid 
-- pg_constraint.conkey is an array of pg_attribute.attnum values
-- "with ordinality" clause helps keep proper column ordering
inner join lateral unnest (c.conkey) with ordinality as cols (attnum, pos)
  on a.attnum = cols.attnum
where c.contype = 'p' 
order by conname, relname, pos
4
  • Yes, I am trying to determine the column names of the primary keys for the tables. Checking under the constraints of the schema only shows PK_table_name but doesn't indicate what/which that actual column is unfortunately. – SkyB Mar 23 at 0:02
  • You'll have to run a query like mustaccio suggested. – Laurenz Albe Mar 23 at 6:52
  • Thank you for the assist, mustaccio. Is the above script generalizable enough as is, or do I need to make any modifications based on schema or table names? – SkyB Mar 23 at 17:44
  • I guess you could run the query and check if the result is suitable; as you can see, there's no reference to schema or table names in it. – mustaccio Mar 24 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.