Include primary key on schema information in Postgres

Question

I have this query:

SELECT column_name, data_type, character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name='my_table';

It's output looks something like:

              column_name               |    data_type     | character_maximum_length 
----------------------------------------+------------------+--------------------------
 key                                    | integer          |                         
 created_date_key                       | integer          |                         
 call_scheduled_date_key                | integer          |                         
 call_completed_date_key                | integer          |                         
 enroll_date_key                        | integer          |                         
 syllabus_request_date_fst_key          | integer          |                         
 info_session_registration_date_fst_key | integer          |                         

I would like to have two more columns, one called "primary_key" and another called "foreign_key" that contain boolean values.

Is this possible?

Answer 1

You can get this information from these two views: key_column_usage and referential_constraints. The key_column_usage view lists all key columns: primary and foreign. To distinguish between the two use the referential_constraints view that tells you if the constraint is the referential (foreign key) constraint:

SELECT c.column_name, c.data_type, c.character_maximum_length,
CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.constraint_column_usage k WHERE c.table_name = k.table_name and k.column_name = c.column_name) 
     THEN true ELSE false END as primary_key, 
CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.key_column_usage k WHERE c.table_name = k.table_name and k.column_name = c.column_name) AND 
          EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.referential_constraints f INNER JOIN INFORMATION_SCHEMA.key_column_usage k ON k.constraint_name = f.constraint_name WHERE k.column_name = c.column_name) 
     THEN true ELSE false END as foreign_key 
FROM INFORMATION_SCHEMA.COLUMNS c 
WHERE c.table_name='my_table';