WITH personal_code AS (SELECT c.table_schema, c.table_name, c.column_name,  c.data_type, c.character_maximum_length
FROM information_schema.columns AS c
WHERE (c.table_schema, c.table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND 
c.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)
AND c.column_name~*'^(isik[u_ ]*kood|person[_ ]*code|personal[_ ]*code|(national|nat)[_ ]*identification[_ ]*(number|nr)|(national|nat)[_ ]*identity[_ ]*(number|nr)|(national|nat)[_ ]*insurance[_ ]*(number|nr))$'
AND (c.data_type ILIKE '%char%' OR c.data_type='text')),
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,
unnest(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') and cardinality(o.conkey)=1),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, contype
from keys),
keys_with_names as (select conname, key_schema, key_table, contype, a_key.attname  as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false)
select conname, key_schema, key_table, contype, key_col, data_type, character_maximum_length
from personal_code AS p INNER JOIN keys_with_names AS k ON p.table_schema=k.key_schema AND p.table_name=k.key_table AND p.column_name=k.key_col
order by key_schema, key_table, contype, key_col;