在Postgres中包含模式信息的主键

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.

我希望有两个更多的列，一个名为“primary_key”，另一个名为“foreign_key”，其中包含布尔值。

Is this possible?

这是可能的吗?

2 个解决方案

#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:

您可以从这两个视图中获取信息:key_column_usage和referential_constraint。key_column_usage视图列出所有关键列:主列和外列。要区分两者，请使用referential_constraints视图，该视图告诉您该约束是否是引用(外键)约束:

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';

#2

You can use the system catalog pg_constraint to get the desired data. Example:

您可以使用系统编目pg_constraint来获取所需的数据。例子:

create table my_table(
    id serial primary key, 
    fid int references my_other_table(id), 
    str varchar(10));

select 
    column_name, data_type, character_maximum_length, 
    bool_or(contype is not distinct from 'p') as primary_key, 
    bool_or(contype is not distinct from 'f') as foreign_key
from information_schema.columns
left join pg_constraint 
    on conrelid = table_name::regclass and ordinal_position = any(conkey)
where table_name='my_table'
group by 1, 2, 3;

 column_name |     data_type     | character_maximum_length | primary_key | foreign_key 
-------------+-------------------+--------------------------+-------------+-------------
 id          | integer           |                          | t           | f
 fid         | integer           |                          | f           | t
 str         | character varying |                       10 | f           | f
(3 rows)