查看PostgreSQL所有约束

最新推荐文章于 2023-09-17 00:11:09 发布

熊出陌

最新推荐文章于 2023-09-17 00:11:09 发布

阅读量2.6k

点赞数 1

分类专栏： PostgreSQL 文章标签： postgresql 数据库 database

原文链接：https://github.com/digoal/blog/blob/master/202202/20220210_01.md

版权

PostgreSQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

背景

https://blog.crunchydata.com/blog/postgres-constraints-for-newbies

SELECT * FROM (  
    SELECT  
       c.connamespace::regnamespace::text as table_schema,  
       c.conrelid::regclass::text as table_name,  
       con.column_name,  
       c.conname as constraint_name,  
       pg_get_constraintdef(c.oid)  
    FROM  
        pg_constraint c  
    JOIN  
        pg_namespace ON pg_namespace.oid = c.connamespace  
    JOIN  
        pg_class ON c.conrelid = pg_class.oid  
    LEFT JOIN  
        information_schema.constraint_column_usage con ON  
        c.conname = con.constraint_name AND pg_namespace.nspname = con.constraint_schema     
    UNION ALL  
    SELECT  
        table_schema, table_name, column_name, NULL, 'NOT NULL'  
    FROM information_schema.columns  
    WHERE  
        is_nullable = 'NO'  
) all_constraints  
WHERE  
    table_schema NOT IN ('pg_catalog', 'information_schema')  
ORDER BY table_schema, table_name, column_name, constraint_name  
;

 table_schema |    table_name    | column_name |    constraint_name    | pg_get_constraintdef   
--------------+------------------+-------------+-----------------------+----------------------  
 public       | pgbench_accounts | aid         | pgbench_accounts_pkey | PRIMARY KEY (aid)  
 public       | pgbench_accounts | aid         |                       | NOT NULL  
 public       | pgbench_branches | bid         | pgbench_branches_pkey | PRIMARY KEY (bid)  
 public       | pgbench_branches | bid         |                       | NOT NULL  
 public       | pgbench_tellers  | tid         | pgbench_tellers_pkey  | PRIMARY KEY (tid)  
 public       | pgbench_tellers  | tid         |                       | NOT NULL  
 public       | t1               | id          | t1_pkey               | PRIMARY KEY (id)  
 public       | t1               | id          |                       | NOT NULL  
 public       | t2               | id          | t2_pkey               | PRIMARY KEY (id)  
 public       | t2               | id          |                       | NOT NULL  
 public       | u                | id          | u_pkey                | PRIMARY KEY (id)  
 public       | u                | id          |                       | NOT NULL  
(12 rows)