Definition of a non-minimal superkey instead of a candidate key (based on check constraints)

Copy SQL

WITH columns_with_domains_chk AS (
SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d
WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type, cc.check_clause, cc.constraint_name
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE', 'FOREIGN') 
AND cc.check_clause NOT LIKE '%IS NOT NULL'    
AND cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

simple_chk AS (SELECT 
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name, 
CASE WHEN c.relkind='r' THEN 'BASE TABLE' ELSE 'FOREIGN' END AS table_type,
a.attname AS column_name,
t.typname AS type_name,
substring(pg_get_constraintdef(o.oid),7) AS check_clause,
o.conname AS constraint_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND c.relkind IN ('r','f')
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, constraint_name
FROM columns_with_domains_chk),

one_value AS (SELECT  table_schema, table_name, table_type, array[column_name] AS column_name, type_name, constraint_name, check_clause, is_nullable
FROM simple_chk AS ch INNER JOIN INFORMATION_SCHEMA.columns AS c USING(table_schema, table_name, column_name)
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'(left|right|substring|trim|length)' 
AND check_clause!~*'[[:space:]](and|or)[[:space:]]' 
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+' || column_name || '[)]+')
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+value[)]+')
AND check_clause !~* ( '(current_date|current_timestamp|localtimestamp|now()|clock_timestamp|statement_timestamp|transaction_timestamp)')),


keys as (select 
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table, 
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p') ),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)

SELECT conname AS surrogate_key_constraint, key_schema, key_table, contype, key_col, string_agg (o.check_clause, ';<br>' ORDER BY o.check_clause) AS checks
FROM keys_with_names AS k INNER JOIN one_value AS o 
ON k.key_schema=o.table_schema 
AND k.key_table=o.table_name 
AND cardinality(k.key_col) >cardinality(o.column_name)
AND k.key_col @>o.column_name 
GROUP BY conname, key_schema, key_table, contype, key_col
ORDER BY key_schema, key_table, contype, key_col;