Retrieve primary key columns

From PostgreSQL wiki

Jump to: navigation, search

Snippets

Retrieve primary key columns

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing


This query returns the names and datatypes of the columns of the primary key for the TABLENAME table (note that the table name may be qualified if you need to include its schema name). 

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute 
WHERE 
  pg_class.oid = 'TABLENAME'::regclass AND
  indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary

To see all indexed columns, remove the indisprimary restriction.

Retrieved from "http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns"
Personal tools