PostgreSQL 制約一覧

SELECT
c.constraint_schema
,c.constraint_type
,c.constraint_name,c.table_schema,c.table_name
FROM information_schema.table_constraints c
WHERE 1=1
and constraint_schema='sooni'
order by c.constraint_type
;Copy

myposdb=# SELECT
myposdb-# c.constraint_schema
myposdb-# ,c.constraint_type
myposdb-# ,c.constraint_name,c.table_schema,c.table_name
myposdb-# FROM information_schema.table_constraints c
myposdb-# WHERE 1=1
myposdb-# and constraint_schema='sooni'
myposdb-# order by c.constraint_type
myposdb-# ;
 constraint_schema | constraint_type |    constraint_name     | table_schema | table_name
-------------------+-----------------+------------------------+--------------+------------
 sooni             | CHECK           | 16393_17625_2_not_null | sooni        | hoge
 sooni             | CHECK           | 16393_17250_1_not_null | sooni        | logging
 sooni             | CHECK           | 16393_17514_1_not_null | sooni        | addsample
 sooni             | CHECK           | 16393_17613_1_not_null | sooni        | wardcity
 sooni             | CHECK           | 16393_17625_1_not_null | sooni        | hoge
 sooni             | CHECK           | 16393_16467_1_not_null | sooni        | cable
 sooni             | CHECK           | 16393_16467_2_not_null | sooni        | cable
 sooni             | CHECK           | 16393_16483_1_not_null | sooni        | spec
 sooni             | FOREIGN KEY     | hoge_prev_id_fkey      | sooni        | hoge
 sooni             | PRIMARY KEY     | spec_pkey              | sooni        | spec
 sooni             | PRIMARY KEY     | hoge_pkey              | sooni        | hoge
 sooni             | PRIMARY KEY     | cable_pkey             | sooni        | cable
(12 行)


myposdb=#Copy

select c.constraint_type,u.table_schema,u.table_name,u.constraint_name,u.ordinal_position,u.column_name 
from  information_schema.table_constraints c inner join information_schema.key_column_usage u
on c.table_schema = u.table_schema and c.table_name = u.table_name and c.constraint_name = u.constraint_name
where c.table_schema='apuser'
and c.constraint_type='PRIMARY KEY'
order by c.constraint_type,u.constraint_schema,u.constraint_name,u.table_schema,u.table_name,u.ordinal_position,u.column_name
;Copy

select c.constraint_type,u.table_schema,u.table_name,u.constraint_name,u.ordinal_position,u.column_name 
from  information_schema.table_constraints c inner join information_schema.key_column_usage u
on c.table_schema = u.table_schema and c.table_name = u.table_name and c.constraint_name = u.constraint_name
where c.table_schema='apuser'
and c.constraint_type='FOREIGN KEY'
order by c.constraint_type,u.constraint_schema,u.constraint_name,u.table_schema,u.table_name,u.ordinal_position,u.column_name
;Copy