Names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name

Copy SQL

WITH index_names AS (SELECT n.nspname AS table_schema, c2.relname AS Table_Name, 
a.attname AS Column_name,
c.relname AS Index_name, 'INDEX' AS type
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c 
	ON a.attrelid = c.oid
LEFT JOIN pg_catalog.pg_type t
	ON a.atttypid = t.oid
LEFT JOIN pg_catalog.pg_index i
	ON i.indexrelid = a.attrelid
LEFT JOIN pg_catalog.pg_class c2
	ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_authid u 
ON u.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n 
ON n.oid = c.relnamespace
WHERE c.relkind = 'i'
AND n.nspname <>'pg_toast' AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres') AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_indexes AS pi
WHERE pi.schemaname=n.nspname AND 
pi.tablename=c2.relname AND 
pi.indexname=C.relname AND pi.indexdef LIKE '%UNIQUE%')),

unnested_constr_attributes AS (SELECT c.oid, conname, contype, conrelid, unnest(conkey) AS attnum
FROM pg_constraint c INNER JOIN pg_catalog.pg_namespace n 
ON n.oid = c.connamespace
INNER JOIN pg_catalog.pg_authid u ON u.oid = n.nspowner
WHERE (n.nspname = 'public'
OR u.rolname <> 'postgres')),

constraint_names AS (
select 
case when o.contype='p' then 'PRIMARY KEY'
when o.contype='u' then 'UNIQUE'
when o.contype='f' then 'FOREIGN KEY'
when o.contype='c' then 'TABLE CHECK'
when o.contype='x' then 'EXCLUDE'
when o.contype='t' then 'CONSTRAINT TRIGGER' END AS type,
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname AS table_name,
o.conname AS constraint_name,
a.attname as column_name
from unnested_constr_attributes 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.attnum and a.attisdropped = false)

SELECT type, table_schema, table_name, column_name, constraint_name
FROM (SELECT type, table_schema, table_name, column_name, constraint_name
FROM constraint_names

UNION ALL SELECT type, table_schema, table_name, column_name, index_name
FROM index_names) AS foo
WHERE constraint_name NOT ILIKE  '%' || column_name || '%'
ORDER BY table_schema, table_name, type, column_name;