How do I get the primary key(s) of a table from Po

问题:

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

回答1:

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

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

回答2:

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';

回答3:

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.

回答4:

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.

回答5:

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

回答6:

Preserving column order using generate_subscripts:

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

回答7:

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