Retrieve primary key columns

Snippets

Retrieve primary key columns

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

This returns the names and data types of all columns of the primary key for the tablename table:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tablename'::regclass
AND    i.indisprimary;

Notes

• To see all indexed columns, remove the indisprimary restriction.
• The table name defaults to the first match in the current search_path unless qualified with a schema explicitly: 'public.tablename'::regclass
• The table name must be double-quoted for otherwise illegal names (upper-case letters, reserved words etc.): '"oDD table name"'::regclass
• Use quote_ident() to double-quote as needed automatically, works for column names in the result as well: quote_ident(a.attname)