List primary keys for all tables - Postgresql

Question

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

If I were asking this, I'd like to know the ordinal position of a column in the PK (some PKs have more than 1 columns and the order may matter). — ypercubeᵀᴹ, Feb 22 '17 at 10:04

score 22 · Answer 1 · edited Nov 04 '12 at 18:53

22

This is more accurate answer:

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.

edited Nov 04 '12 at 18:53

András Váczi

31,278
13
101
147

answered Nov 04 '12 at 14:31

mikipero

343
2
5

2

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). – András Váczi Nov 04 '12 at 18:55

score 17 · Accepted Answer · edited Sep 09 '18 at 14:06

17

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 and kc.constraint_name = tc.constraint_name
where tc.constraint_type = 'PRIMARY KEY'
  and kc.ordinal_position is not null
order by tc.table_schema,
         tc.table_name,
         kc.position_in_unique_constraint;

edited Sep 09 '18 at 14:06

Community

1

answered Jan 20 '12 at 09:14

This query show not only primary keys but also unique indexes – Michał Niklas Mar 17 '16 at 11:55
@MichałNiklas it does not. – András Váczi Jul 29 '16 at 14:14
@dezso it does..i tried it pg 9.5 – Dariel Pratama Apr 27 '17 at 05:38
1

@DarielPratama: the condition tc.constraint_type = 'PRIMARY KEY' will only show primary keys. However each primary key is backed by a unique indexe – Apr 27 '17 at 06:09
2

@a_horse_with_no_name I belive this is incorrect. position_in_unique_constraint indicates position for FOREIGN key, it is always null for primary keys. The correct column is ordinal_position. Tested in PG 9.4. – greatvovan Sep 01 '17 at 16:37
1

@a_horse_with_no_name I've approved an edit suggested by anonymous user. Not sure if the edit will go through, others have rejected. In any case please check the suggestion and the comment above by greatvovan. I think they are correct and ordinal_position should be used. The position_in_unique_constraint is not null only in FKs usage. – ypercubeᵀᴹ Sep 09 '18 at 14:08
The wording differs depending on Postgres version, but the docs for key_column_usage all say something to the effect of "Only those columns are shown that the current user has access to, by way of being the owner or having some privilege", but fails to mention what, other than owner, privilege is required. tl;dr this might not return all the rows you were expecting. – Davos Apr 17 '19 at 11:47

score 2 · Answer 3 · edited Jul 29 '16 at 13:04

2

Please consider this also. This will generate the script to alter all the tables.

SELECT STRING_AGG(FORMAT('ALTER TABLE %s CLUSTER ON %s;', A.table_name, A.constraint_name), E'\n') AS SCRIPT
FROM
(
    SELECT      FORMAT('%s.%s', table_schema, table_name) AS table_name, constraint_name
    FROM        information_schema.table_constraints
    WHERE       UPPER(constraint_type) = 'PRIMARY KEY'
    ORDER BY    table_name 
) AS A;

edited Jul 29 '16 at 13:04

Philᵀᴹ

31,762
10
83
107

answered Jul 29 '16 at 12:41

Martin

21
1

The question doesn't ask for how to alter the tables. – ypercubeᵀᴹ Jul 29 '16 at 15:56
1

I second what @ypercubeᵀᴹsays. Delete this answer, but don't be discouraged - take the tour, visit the help centre and read tbe "help us to help you" blog. As for answering something that was not asked, we've all done that plenty of times :-). p.s welcome to the forum! – Vérace Jul 29 '16 at 20:03
This really helped me! You’re my hero! – Theodore R. Smith Dec 02 '21 at 02:05

score 1 · Answer 4 · answered Feb 22 '17 at 09:40

I think to get primary key and foreign key should do like this. kc.position_in_unique_constraint is not null this condition may only get foreign keys.

select tc.table_schema, tc.table_name, kc.column_name,tc.constraint_type
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 
                and kc.constraint_name = tc.constraint_name
where 
--kc.position_in_unique_constraint is not null
order by tc.table_schema,
         tc.table_name,
         kc.position_in_unique_constraint;

I'm trying to do something like this (the table names are slightly different, I'm probably on a different version of postgres). The query runs but I don't get any results back. Is it possible that I don't have the right permissions-? — szeitlin, Aug 14 '18 at 20:22

List primary keys for all tables - Postgresql

4 Answers4