WITH constraint_attributes AS (SELECT conrelid, unnest(conkey) AS attnum
FROM pg_constraint
WHERE contype in ('u', 'p', 'x')),
unique_indexes AS (SELECT indrelid, unnest(indkey) AS attnum
FROM pg_index
WHERE indisunique=TRUE),
unique_function_based_indexes AS (SELECT schemaname, tablename, indexdef
FROM (SELECT schemaname, tablename, indexdef
FROM pg_catalog.pg_indexes
WHERE indexdef~*'unique[[:space:]]+index.+(upper|lower)[(]'
union select 
(select nspname from pg_namespace where oid=c.relnamespace) as excl_schema,
c.relname as excl_table, 
pg_get_indexdef(o.conindid) as index_def
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('x')
and pg_get_indexdef(o.conindid) ~*'(upper|lower)') AS foo
WHERE schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT n.nspname AS table_schema,
c.relname AS table_name,
a.attname AS column_name
FROM pg_attribute AS a INNER JOIN pg_class AS c ON a.attrelid=c.oid
INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_authid AS au ON n.nspowner=au.oid
INNER JOIN pg_type AS t ON a.atttypid=t.oid
LEFT JOIN pg_type AS t_base ON t.typbasetype=t_base.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind='r'
AND ((t.typtype='b' AND t.typname~'(char|text)') OR (t.typtype='d' AND t_base.typname~'(char|text)'))
AND a.attname~*'(kasutajanimi|username|e_mail|e_meil|mail|meil)'
AND NOT EXISTS (SELECT *
FROM constraint_attributes AS ca
WHERE c.oid=ca.conrelid 
AND a.attnum=ca.attnum)
AND NOT EXISTS (SELECT *
FROM unique_indexes AS ui
WHERE c.oid=ui.indrelid 
AND a.attnum=ui.attnum)
AND NOT EXISTS (SELECT *
FROM unique_function_based_indexes AS ui
WHERE ui.schemaname=n.nspname
AND ui.tablename=c.relname
AND ui.indexdef ILIKE '%(' || a.attname || ')%' )
AND (c.relname~*'(user|kasutaja|person|isik|worker|tootaja|client|customer|klient|subject|subjekt|account|acct|konto)'
OR
(n.nspname, c.relname) IN (SELECT  n.nspname AS table_schema,
c.relname AS table_name
FROM pg_attribute AS a INNER JOIN pg_class AS c ON a.attrelid=c.oid
INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_authid AS au ON n.nspowner=au.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind='r'
AND a.attname~*'(passw|pwd|pswd|pwd|pw|parool|salasona)'))
ORDER BY table_schema, table_name, column_name;