20

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
1
  • 1
    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). Feb 22, 2017 at 10:04

4 Answers 4

16

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;
7
  • This query show not only primary keys but also unique indexes Mar 17, 2016 at 11:55
  • @MichałNiklas it does not.
    – dezso
    Jul 29, 2016 at 14:14
  • 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, 2017 at 6: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 1, 2017 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. Sep 9, 2018 at 14:08
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.

1
  • 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).
    – dezso
    Nov 4, 2012 at 18:55
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;
3
  • The question doesn't ask for how to alter the tables. Jul 29, 2016 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! Jul 29, 2016 at 20:03
  • This really helped me! You’re my hero! Dec 2, 2021 at 2:05
1

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;
1
  • 1
    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, 2018 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.