How do I get the primary key(s) of a table from Postgres via plpgsql?
SqlPostgresqlPlpgsqlSql Problem Overview
Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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';
Solution 3 - Sql
\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)
Solution 4 - Sql
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.
Solution 5 - Sql
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
Solution 6 - Sql
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.
Solution 7 - Sql
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
Solution 8 - Sql
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
Solution 9 - Sql
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
Solution 10 - Sql
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