列出 PostgreSQL 中具有不同所有者的所有表的约束

List constraints for all tables with different owners in PostgreSQL

提问by Tomas Greif

Do I have to be owner of relation to access constraint related data in information schema? I've tested the following and it seems that I have to be the owner.

我是否必须是信息架构中访问约束相关数据的关系所有者？我已经测试了以下内容，看来我必须是所有者。

create schema rights_test;

create table rights_test.t1 (id int primary key);
create table rights_test.t2 (id int references rights_test.t1(id));

select  
        tc.constraint_name, 
        tc.constraint_schema || '.' || tc.table_name || '.' || kcu.column_name as physical_full_name,  
        tc.constraint_schema,
        tc.table_name, 
        kcu.column_name, 
        ccu.table_name as foreign_table_name, 
        ccu.column_name as foreign_column_name,
        tc.constraint_type
    from 
        information_schema.table_constraints as tc  
        join information_schema.key_column_usage as kcu on (tc.constraint_name = kcu.constraint_name and tc.table_name = kcu.table_name)
        join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
    where 
        constraint_type in ('PRIMARY KEY','FOREIGN KEY')
        and tc.constraint_schema = 'rights_test'

/*
This will produce desired output:
t1_pkey;rights_test.t1.id;rights_test;t1;id;t1;id;PRIMARY KEY
t2_id_fkey;rights_test.t2.id;rights_test;t2;id;t1;id;FOREIGN KEY
*/

create user rights_test_role with password 'password';

grant all on rights_test.t1 to rights_test_role;
grant all on rights_test.t2 to rights_test_role;

/* Now login as rights_test_role and try the same constraint select.
   For rights_test_role it returns nothing although I've added ALL privileges
*/

Is there other way how to get the same information if I am not owner of the relation?

如果我不是关系的所有者，还有其他方法可以获得相同的信息吗？

采纳答案by A.H.

Not all constraint-related data is "protected". You use three relations in your query:

并非所有与约束相关的数据都受到“保护”。您在查询中使用了三个关系：

• table_constraints
• key_column_usage
• constraint_column_usage

• table_constraints
• key_column_usage
• constraint_column_usage

The first two are not limited, but the documentation for constraint_column_usagetells you:

前两个不受限制，但文档constraint_column_usage告诉您：

The view constraint_column_usage identifies all columns in the current database that are used by some constraint. Only those columns are shown that are contained in a table owned by a currently enabled role.

视图constraint_column_usage 标识当前数据库中某些约束使用的所有列。仅显示当前启用的角色拥有的表中包含的那些列。

Since information_schema.constraint_column_usageis a view, you can see its definition using

由于information_schema.constraint_column_usage是一个视图，您可以使用查看其定义

\d+ information_schema.constraint_column_usage

in the psql shell. The result looks frightening at a first glance but it's really not so bad. The most interesting thing - for a first test - is the part in the very last line:

在 psql 外壳中。结果乍一看很吓人，但实际上并没有那么糟糕。最有趣的事情 - 对于第一次测试 - 是最后一行中的部分：

  WHERE pg_has_role(x.tblowner, 'USAGE'::text);

If you paste the definition into the psql shell which is open by the non-owner rights_test_roleand delete that last line you will get the desired result. This is good, because that means that the basic metadata is not protected by the system. So you can strip down the view definition to include only the parts you really need.

如果您将定义粘贴到非所有者打开的 psql shell 中rights_test_role并删除最后一行，您将获得所需的结果。这很好，因为这意味着基本元数据不受系统保护。因此，您可以精简视图定义以仅包含您真正需要的部分。

回答by Anupama Boorlagadda

Try using this.. gives all the constraint names and constraint description.

尝试使用 this.. 给出所有约束名称和约束描述。

• Foreign key
• Check
• Primary key
• Unique

• 外键
• 查看
• 首要的关键
• 独特的

Like:

喜欢：

select conrelid::regclass AS table_from, conname, pg_get_constraintdef(c.oid)
from   pg_constraint c
join   pg_namespace n ON n.oid = c.connamespace
where  contype in ('f', 'p','c','u') order by contype

回答by Eugen Konkov

To list relational constraints you can use next query:

要列出关系约束，您可以使用下一个查询：

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'