代码之家 › 专栏 › 技术社区 › dave paola

在Postgres中包含架构信息的主键

primary-key postgresql sql

dave paola · 技术社区 · 6 年前

我有这个问题:

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

其输出类似于:

              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          |

我想再增加两列,一列名为“primary\u key”,另一列名为“foreign\u key”,其中包含布尔值。

这可能吗?

2 回复 | 直到 6 年前

cha 6 年前

您可以从以下两个视图中获得此信息: key_column_usage 和 referential_constraints 这个 key\u column\u用法 视图列出所有关键列:主列和外部列。要区分两者,请使用 referential\u约束 显示约束是否为引用(外键)约束的视图:

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

klin 6 年前

您可以使用系统目录 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)