Candidate key columns that have a static default value

Copy SQL

with keys as (select 
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
o.conkey as target_col,
case when o.contype='p' then 'PRIMARY KEY'
else 'UNIQUE' end AS constraint_type
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, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, constraint_type
from keys, unnest(keys. target_col) with ordinality as f(target_col_num, ordin)),

keys_with_names as (select conname, target_schema, target_table, a_target.attname as key_column, constraint_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false),

def_columns AS (SELECT  table_schema,  table_name, c.column_name, coalesce(column_default, domain_default) AS def
FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL)

SELECT d.table_schema, d.table_name, d.column_name, k.constraint_type, def
FROM def_columns as d INNER JOIN keys_with_names as k 
ON d.table_schema=k.target_schema AND d.table_name=k.target_table AND d.column_name=k.key_column
WHERE def~*'^['']' 
OR  def~*'^(true|false)$' 
OR def~*'^([[:digit:]]|[.])+$'
OR def~*'^[(]+([[:digit:]]|[.])+[)]+'
ORDER BY d.table_schema, d.table_name, d.column_name;