Goal Find cases where a unique constraint (that is not case-insensitive or partial, i.e., applies only to certain rows) has been enforced by using a CREATE UNIQUE INDEX statement instead declaring a PRIMARY KEY, UNIQUE, or EXCLUDE constraint. You should try to work on as high level of abstraction as possible. According to the ANSI-SPARC Architecture indexes are a part of internal database schema whereas constraints are a part of conceptual schema, i.e., at the higher level of abstraction.
Notes The query uses a system view and returns CREATE INDEX statement.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Drop the index and declare PRIMARY KEY or UNIQUE constraint instead.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
SELECT schemaname, tablename, indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef NOT LIKE '% WHERE %' AND
indexdef NOT LIKE '%(lower(%' AND
indexdef NOT LIKE '%(upper(%' AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(schemaname, tablename, indexname) NOT IN
(select 
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name,
o.conname as constraint_name 
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p','u','x')  
and c.relkind = 'r')
ORDER BY schemaname, tablename, indexname;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
SELECT format('DROP INDEX %1$I.%2$I;', schemaname, indexname) AS statements
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef NOT LIKE '% WHERE %' AND
indexdef NOT LIKE '%(lower(%' AND
indexdef NOT LIKE '%(upper(%' AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(schemaname, tablename, indexname) NOT IN
(SELECT 
  table_constraints.table_schema, 
  table_constraints.table_name, 
  table_constraints.constraint_name
FROM 
  information_schema.table_constraints
WHERE 
  table_constraints.constraint_type IN ('PRIMARY KEY', 'UNIQUE'))
ORDER BY schemaname, tablename, indexname;
Drop the index.
Collections

This query belongs to the following collections:

NameDescription
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
PerformanceQueries of this category provide information about indexes in a database.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

Further reading and related materials:

Reference
https://en.wikipedia.org/wiki/ANSI-SPARC_Architecture