jsight jsight - 10 months ago 57
SQL Question

How do I get the primary key(s) of a table from Postgres via plpgsql?

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?

Answer Source

The query above is very bad as it is really slow.

I would recommend this official version:


if schema is needed the query is as follows

  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
  pg_class.oid = 'foo'::regclass AND 
  indrelid = pg_class.oid AND 
  nspname = 'public' AND 
  pg_class.relnamespace = pg_namespace.oid AND 
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
 AND indisprimary