所以，我一直在网上搜索，似乎找不到解决这个问题的方法......

我试图列出所有列及其在 postgresql 中的引用。在 mysql 中，查询类似于:

select table_schema, table_name, column_name, referenced_table_schema, referenced_table_name, referenced_column_name from informatioN_schema.key_column_usage where table_schema = '(Table Schema)';

结果是:

setandlog    | access      | kode_privilege  | NULL                    | NULL                  | NULL
setandlog    | access      | kode_feature    | NULL                    | NULL                  | NULL
setandlog    | access      | kode_app        | NULL                    | NULL                  | NULL
setandlog    | access      | kode_credential | NULL                    | NULL                  | NULL
setandlog    | access      | username        | NULL                    | NULL                  | NULL
setandlog    | access      | kode_credential | setandlog               | cred_access           | kode_credential
setandlog    | access      | kode_privilege  | setandlog               | cred_access           | kode_privilege
setandlog    | access      | kode_feature    | setandlog               | cred_access           | kode_feature
setandlog    | access      | kode_app        | setandlog               | cred_access           | kode_app
setandlog    | access      | username        | setandlog               | login                 | username

但是当我使用这个查询在 postgresql 中尝试它时:

select r.table_schema as table_schema, r.table_name as table_name, r.column_name as column_name, u.table_schema as referenced_table_schema, u.table_name as referenced_table_name, u.column_name as referenced_column_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

结果不像我在 MySQL 查询中预期的那样... 结果如下:

setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_app
setandlog    | access            | username                 | setandlog               | login                 | username

似乎有很多冗余数据...... 当我检查查询时，INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 表中的 UNIQUE_CONSTRAINT_NAME 似乎没有差异...

有人可以帮我解决这个问题吗...？

最佳答案

这里的问题是引用表有一个由多列组成的 PRIMARY KEY:

PRIMARY KEY(kode_credential, kode_privilege, kode_feature, kode_app)

这 4 列连接到其他表，然后您得到一个笛卡尔积:每个外键中的每一列对应 4 行。您需要使用列 ordinal_position(引用表中的约束列 - 在您的情况下为 access)和 position_in_unique_constraint(约束列中的引用表 - cred_access)，都在 key_column_usage View 中。为了实现这一点，您需要像这样的自连接:

SELECT k1.table_schema,
       k1.table_name,
       k1.column_name,
       k2.table_schema AS referenced_table_schema,
       k2.table_name AS referenced_table_name,
       k2.column_name AS referenced_column_name
FROM information_schema.key_column_usage k1
JOIN information_schema.referential_constraints fk USING (constraint_schema, constraint_name)
JOIN information_schema.key_column_usage k2
  ON k2.constraint_schema = fk.unique_constraint_schema
 AND k2.constraint_name = fk.unique_constraint_name
 AND k2.ordinal_position = k1.position_in_unique_constraint;

MySQL 可以在没有像上面那样的特定连接的情况下做到这一点，这相当“神奇”。让你想知道引擎盖下还会发生什么其他事情......

关于mysql - 如何像在 mysql 中一样在 postgresql 中列出所有列及其引用？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35029269/