How can I get the column name along with its reference type (i.e PRIMARY KEY & FOREIGN KEY) in a single POSTGRESQL Query?

Question

I came up with the following query which provides me the column names along with its data-types but does not provide me the reference type(i.e whether the column is a primary_key or a foreign_key).

select column_name, data_type,character_maximum_length,is_nullable
from   information_schema.columns
where  table_name ='employee';

This is the Output that I am getting:

 column_name |     data_type     | character_maximum_length | is_nullable
-------------+-------------------+--------------------------+-------------
 empno       | character varying |                       10 | NO
 full_name   | character varying |                       30 | YES
 city        | character varying |                        9 | YES
 gender      | character         |                        7 | YES

Can someone help me out getting the reference_type (i.e PRIMARY_KEY & FOREIGN_KEY) as well for the query?

Answer 1

You can try this:

 select c.column_name, c.data_type, c.character_maximum_length, c.is_nullable, s.constraint_name, t.constraint_type
    from   information_schema.columns c
    left join information_schema.key_column_usage s on s.table_name = c.table_name and s.column_name = c.column_name
    left join information_schema.table_constraints t on t.table_name = c.table_name and t.constraint_name = s.constraint_name
    where  c.table_name ='employee'

Have a look at this link https://www.postgresql.org/docs/9.1/static/information-schema.html