如何在PostgreSQL服务器中查看表关系?
我曾多次尝试在PostgreSQL服务器中查找表关系,但两年前始终得不到帮助。是否有类似于SQL Server或Access中查找表关系的方法?或者是否可以在PostgreSQL服务器中查看表关系?
如果你想列出一个表开始的所有关系,你必须搜索外键。在Postgresql中,这意味着搜索约束条件。
可以通过查看以下问题来解决:SQL to list table foreign keys
从答案中可以得到:
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
我知道这个问题并不完全涉及此事,但我认为很多人会来到这里寻找一种简单的方法来查看使用不同数据类型(如具有bigint的父ID和具有int的子外键,反之亦然)的表之间的关系。
以下是一种快速发现父子表之间所有具有不同类型列的方法:
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
cf.data_type AS child_data_type,
cp.data_type AS parent_data_type
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.tables as t
on t.table_name = tc.table_name and t.table_catalog = tc.table_catalog and t.table_schema = tc.table_schema
JOIN information_schema.columns as cf
on cf.table_name = tc.table_name and cf.column_name = kcu.column_name and cf.table_catalog = tc.table_catalog and cf.table_schema = tc.table_schema
JOIN information_schema.columns as cp
on cp.table_name = ccu.table_name and cp.column_name = ccu.column_name and cp.table_catalog = ccu.table_catalog and cp.table_schema = ccu.table_schema
WHERE constraint_type = 'FOREIGN KEY'
and cf.data_type <> cp.data_type;
尽情享受!