Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table A (id int primary key, name varchar(20));
create table B (id int primary key, name varchar(20), pid int);
ALTER TABLE B ADD CONSTRAINT fk_a_id FOREIGN KEY (pid) REFERENCES A(id) ;  

CREATE TABLE



CREATE TABLE



ALTER TABLE


select tc.table_schema, tc.table_name,
       tc.constraint_type,tc.constraint_name, tc.is_deferrable,
       tc2.table_name as child_table,
       rc.unique_constraint_name as refer_key,
       case when rc.unique_constraint_name is not null then
            (select tc1.table_name
               from information_schema.table_constraints tc1
              where tc1.constraint_name = rc.unique_constraint_name)
       end as ref_table,
       cc.check_clause,
       kc.column_name,
       kc.ordinal_position
  from information_schema.table_constraints tc
  left join information_schema.referential_constraints rc
    on tc.constraint_name = rc.constraint_name
  left join information_schema.check_constraints cc
    on tc.constraint_name = cc.constraint_name
  left join information_schema.key_column_usage kc
    on tc.constraint_name = kc.constraint_name
  left join LATERAL (
       select tc2.table_name, rc1.unique_constraint_name
              from information_schema.referential_constraints rc1
              join information_schema.table_constraints tc2
                on rc1.constraint_name = tc2.constraint_name
             where tc.constraint_name = rc1.unique_constraint_name
             ) tc2 on true
 where tc.table_name = 'a';


table_schema
table_name
constraint_type
constraint_name
is_deferrable
child_table
refer_key
ref_table
check_clause
column_name
ordinal_position


public
a
PRIMARY KEY
a_pkey
NO
b
null
null
null
id
1

public
a
CHECK
2200_7889601_1_not_null
NO
null
null
null
id IS NOT NULL
null
null



SELECT 2

table_schema	table_name	constraint_type	constraint_name	is_deferrable	child_table	refer_key	ref_table	check_clause	column_name	ordinal_position
public	a	PRIMARY KEY	a_pkey	NO	b	null	null	null	id	1
public	a	CHECK	2200_7889601_1_not_null	NO	null	null	null	id IS NOT NULL	null	null