How do I return the primary column name in Postgre?

Question

I'm working on a wrapper for KnexJS and to make errors more descriptive, I would like to be able to get the name of the primary column of a table. I've looked, but can't find any answers. I'm not adept at SQL so try to break it down a bit if it's advanced.

If there's nothing in Knex.js for this then you have a duplicate of https://stackoverflow.com/q/1214576/479863 — mu is too short, Apr 04 '19 at 00:22

score 0 · Accepted Answer · answered Apr 04 '19 at 00:21

To find out the PK name and column names for the PK on given table(s):

SELECT t.relname AS table_name
     , i.relname AS index_name
     , ia.attname AS column_name
  FROM pg_class t
  JOIN pg_index ix
    ON ix.indrelid = t.oid
   AND ix.indisprimary = 't'
  JOIN pg_class i
    ON i.oid = ix.indexrelid
  JOIN pg_attribute ia 
    ON ia.attrelid = t.oid
   AND ia.attnum = ANY(ix.indkey)
 WHERE t.relkind = 'r' -- table
   AND t.relname = 'your_table_name'

Be aware that for composite PKs (multiple columns) it will list them in separate rows. If you want one row per table, just GROUP BY t.relname, i.relname and select array_to_string(array_agg(ia.attname), ', ') as columns.

Remove any references to `pg_index ix` from the query above and you'll get all the columns in a table. — MarcinJ, Apr 05 '19 at 07:03

How do I return the primary column name in Postgre?

1 Answers1