How to get a column name along with a reference type (e.g. PRIMARY KEY & FOREIGN KEY) in a single POSTGRESQL query?

Question

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

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

This is the result I get:

 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 get the reference type ( i.e. PRIMARY_KEY and FOREIGN_KEY ) for the request?

Answer 1

You can try the following:

 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