如何查看PostgreSQL数据库系统中的所有约束

作者

digoal

日期

2022-02-10

标签

PostgreSQL , constraint

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)  

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.