# List primary keys for all tables - Postgresql

Is there a query that will do that?

I found some queries that can do this for one table, but I wasn't able to modify it so I can see:

tablename | column | type

-

Something like this:

select tc.table_schema, tc.table_name, kc.column_name
from
information_schema.table_constraints tc
join information_schema.key_column_usage kc
on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
where
tc.constraint_type = 'PRIMARY KEY'
and kc.position_in_unique_constraint is not null
order by tc.table_schema,
tc.table_name,
kc.position_in_unique_constraint;

-

select tc.table_schema, tc.table_name, kc.column_name
from
information_schema.table_constraints tc,
information_schema.key_column_usage kc
where
tc.constraint_type = 'PRIMARY KEY'
and kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
and kc.constraint_name = tc.constraint_name
order by 1, 2;


You missed the and kc.constraint_name = tc.constraint_name part, so it lists all constraints.

-
While your query works, the more important difference is the missing and kc.position_in_unique_constraint is not null part. And you are strongly encouraged to use ANSI JOINs (while many consider it being a matter of taste). –  dezso Nov 4 '12 at 18:55