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

Question

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

Answer 1

I would recommend this official version:

http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

if schema is needed the query is as follows

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
WHERE 
  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

Answer 2

This will give you the list of constraints along with the definition.

SELECT
   conrelid::regclass AS table_from,
   conname,
   pg_get_constraintdef ( c.oid )
FROM
   pg_constraint c
   JOIN pg_namespace n ON n.oid = c.connamespace
WHERE
   contype IN ( 'f', 'p ' )
   AND conrelid::regclass::TEXT IN ( 'foo' )

ORDER BY
   conrelid::regclass::TEXT,
   contype DESC

Answer 3

Preserving column order using generate_subscripts (based on @Paul Draper's answer):

SELECT
  a.attname,
  format_type(a.atttypid, a.atttypmod) 
FROM
  pg_attribute a
  JOIN (SELECT *, GENERATE_SUBSCRIPTS(indkey, 1) AS indkey_subscript FROM pg_index) AS i
    ON
      i.indisprimary
      AND i.indrelid = a.attrelid
      AND a.attnum = i.indkey[i.indkey_subscript]
WHERE
  a.attrelid = 'your_table'::regclass
ORDER BY
  i.indkey_subscript

Answer 4

SELECT a.attname AS name, format_type(a.atttypid, a.atttypmod) AS type
FROM
    pg_class AS c
    JOIN pg_index AS i ON c.oid = i.indrelid AND i.indisprimary
    JOIN pg_attribute AS a ON c.oid = a.attrelid AND a.attnum = ANY(i.indkey)
WHERE c.oid = 'example'::regclass

Output:

 name |  type  
------+--------
 id   | bigint

Answer 5

\d tablename 

will give you the primary key info along with other table related information such as all columns, their types, associated indexes, constraints, rules, triggers etc. You probably don't need all that information, but it is the fastest way to get all details at a glance, see more details here.

It returns something like this:

    Table "public.tablename"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     col1   | text    | not null
     col2   | numeric | 
     col3   | text    | 
     col4   | text    | 
     col5   | numeric | 
    Indexes:
        "tablename_pkey" PRIMARY KEY, btree (col1)

Answer 6

You really only need 2 system tables for that:

• pg_constraint - tells you which columns (by number) belong to the primary key
• pg_attribute - translates the column numbers to column names

Note: the system tables might change between PostgreSQL versions, but it doesn't happen often (actually very rarely if at all). And unlike using the information_schema.table_constraints, you don't need any special permissions, just select on the table. (This was tested in Postgres 10.6)

SELECT string_agg(a.attname, ', ') AS pk
FROM
    pg_constraint AS c
    CROSS JOIN LATERAL UNNEST(c.conkey) AS cols(colnum) -- conkey is a list of the columns of the constraint; so we split it into rows so that we can join all column numbers onto their names in pg_attribute
    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum
WHERE
    c.contype = 'p' -- p = primary key constraint
    AND c.conrelid = '<schemaname>.<tablename>'::REGCLASS; -- regclass will type the name of the object to its internal oid

Answer 7

To provide a straight bit of SQL, you can list the primary key columns and their types with:

SELECT c.column_name, c.data_type
FROM information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
  AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytable';

Answer 8

The following SQL statement works for me:

SELECT a.attname
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;

It is directly taken from here.

Answer 9

Beware of indexes where the column order differs from the table's column order. (i.e. if the primary key used columns 3, 2, and 1)

The following query is much more complex, but returns the columns in the proper order. (Remove the 'indisprimary' clause to get the same info for all indexes on a table)

WITH ndx_list AS
(
    SELECT pg_index.indexrelid
      FROM pg_index, pg_class
     WHERE pg_class.relname = 'test_indices_table'
       AND pg_class.oid = pg_index.indrelid
       AND pg_index.indisprimary
), ndx_cols AS
(
   SELECT pg_class.relname AS index_name, UNNEST(i.indkey) AS col_ndx, i.indisunique, i.indisprimary
     FROM pg_class, pg_index i
    WHERE pg_class.oid = i.indexrelid
      AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)
)
  SELECT ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary,
         a.attname, format_type(a.atttypid, a.atttypmod), a.attnum
    FROM pg_class c, pg_attribute a
    JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
   WHERE c.oid = 'test_indices_table'::regclass
     AND a.attrelid = c.oid

Answer 10

Take a look at pg_constraint system table. Or information_schema.table_constraints view if you prefer to stick close to the SQL standard.

For a complete example connect to a DB using psql with the "-E" option and type \d <some_table> - you'll see the actual queries used in describing a table.