主页 / dba / 问题 / 183708
Accepted
Saurabh Nanda
Asked: 2017-08-18 00:56:35 +0800 CST

无法加入 pg_constraint 中的 conrelid 和 confrelid 列

  • 772

我正在尝试以下列形式获取外键约束列表：

pk_table | pk_col | fk_table | fk_col

不使用pg_get_constraintdef（这将迫使我诉诸字符串操作）。

以下查询不起作用有什么原因吗？

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.tablename,
  fk.tablename
FROM pg_catalog.pg_constraint x 
INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND x.conrelid=(pk.tablename :: regclass :: oid)
INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
WHERE
  x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
  AND x.contype='f'

错误：

ERROR:  relation "sql_implementation_info" does not exist

我的假设是 PG 由于存在多个命名空间而变得混乱，所以我尝试将结果集限制为我已知的模式，即b2b, public，但这仍然不起作用。

postgresql information-schema

2 个回答

  1. Best Answer

    问题在于，并非pg_tables视图中OID呈现的所有“表格”都呈现在pg_class关系中：

    postgres=# select tablename::regclass from pg_catalog.pg_tables;
ERROR:  relation "sql_features" does not exist

    您应该将查询重写为更“低级”：

    SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.relname,
  fk.relname
FROM pg_catalog.pg_constraint x 
INNER JOIN pg_catalog.pg_class pk ON x.conrelid!=0 AND x.conrelid=pk.oid
INNER JOIN pg_catalog.pg_class fk ON x.confrelid!=0 AND x.confrelid=fk.oid
WHERE
  x.contype='f';

    test

    • 1

  2. 我查询了一个空数据库，您的查询很好：

    test=> SELECT
test->   x.conname,
test->   x.contype,
test->   x.conrelid,
test->   x.confrelid,
test->   pk.tablename,
test->   fk.tablename
test-> FROM pg_catalog.pg_constraint x 
test-> INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND         x.conrelid=(pk.tablename :: regclass :: oid)
test-> INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
test-> WHERE
test->   x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
test->   AND x.contype='f';
 conname | contype | conrelid | confrelid | tablename | tablename 
---------+---------+----------+-----------+-----------+-----------
(0 rows)

    但是，当我使用外键创建两个表时：

    test=> create table parent (x int not null primary key);
CREATE TABLE
test=> create table child (y int not null primary key, x int not null    references parent(x));
CREATE TABLE

    我得到以下信息：

    ERROR:  relation "sql_features" does not exist

    起初我认为这是我的用户的问题，但在 sudo 到 psql 时我得到了同样的错误。

    所以我开始研究 postgres 中的目录，我想你应该对来自 constraint_column_usage 和 information_schema.table_constraints 的信息没问题

    select * from information_schema.constraint_column_usage;
select * from information_schema.table_constraints;

    我不确定您的查询应该返回什么，所以我会把它留在那里。

    我不知道使用 information_schema vs pg_catalog 是否有任何缺点（我想一些 psql 特定的东西不在 information_schema 中），但是 information_schema 看起来很像其他 DBMS:s 中的 information_schema。因此，如果您了解 psql 中的 information_schema，您应该能够在其他 DBMS 中使用该知识。

    • 0

相关问题