The list of all the queries

Constraint-supporting UNIQUE indexes with the same leading column

Query goal: Find indexes that support a uniqueness constraint and have the same leading column.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

WITH indexes AS (SELECT 
  n.nspname AS sch,
  c2.relname AS tbl,
  c.relname AS indx,    
  array_agg(a.attname ORDER BY a.attnum) AS clm,
  i.indisunique,
  indclass[0] AS operator_class,
  EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid) AS is_constraint
FROM 
  pg_catalog.pg_class AS c INNER JOIN pg_catalog.pg_index AS i ON i.indexrelid = c.oid
  INNER JOIN pg_catalog.pg_class AS c2  ON i.indrelid = c2.oid
  INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
  INNER JOIN pg_catalog.pg_authid AS u ON  n.nspowner = u.oid
  INNER JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid    
WHERE c.relkind = 'i'
AND i.indexprs IS NULL
AND a.attnum>=1
AND a.attisdropped = FALSE
AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres')
GROUP BY n.nspname, c2.relname, c.relname, i.indisunique, indclass[0], is_constraint),
index_with_type AS (SELECT sch, tbl, indx, clm, operator_class, is_constraint,
CASE WHEN is_constraint=TRUE THEN 'UNIQUE CONSTRAINT INDEX'
WHEN indisunique=TRUE AND is_constraint=FALSE THEN 'UNIQUE NON-CONSTRAINT INDEX' 
ELSE 'NON-UNIQUE INDEX' END AS index_type
FROM indexes)
SELECT A.sch AS table_schema, A.tbl AS table_name, A.index_type AS idx1_index_type, A.indx AS idx1, A.clm AS idx1_columns,
B.clm AS idx2_columns, B.index_type AS idx2_index_type, B.indx AS idx2
FROM index_with_type AS A, index_with_type AS B
WHERE A.sch=B.sch AND A.tbl=B.tbl AND A.indx>B.indx AND A.clm[1]=B.clm[1] AND A.operator_class=B.operator_class
AND a.is_constraint=TRUE AND b.is_constraint=TRUE
ORDER BY A.sch, A.tbl, A.indx, B.indx;

Collections where the query belongs to

Collection nameCollection description
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category nameCategory description
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.

The list of all the queries