Goal Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has an enumeration type that permits only one value in the column. The candidate key should involve only columns without such type.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH one_value AS (SELECT nspname AS type_schema, typname
FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid
INNER JOIN pg_catalog.pg_namespace n ON n.oid=t.typnamespace
WHERE nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND 
t.typtype='e'
GROUP BY nspname, typname
HAVING Count(enumtypid)=1),

cols as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name, 
a.attname AS column_name,
n.nspname AS type_schema,
t.typname AS column_type,
CASE WHEN c.relkind ='r' THEN 'BASE TABLE' ELSE 'FOREIGN TABLE' END AS table_type
from pg_class c inner join pg_attribute a ON a.attrelid = c.oid AND a.attisdropped = FALSE
inner join pg_type AS t ON a.atttypid=t.oid
inner join pg_namespace as n ON t.typnamespace =n.oid
where c.relkind in ('r','f')),

cols_with_one_value AS (SELECT table_schema, table_name, table_type, ARRAY[column_name] AS column_name, co.type_schema, co.column_type
FROM cols AS co INNER JOIN one_value AS o ON co.type_schema=o.type_schema AND co.column_type=o.typname
INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name)),

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,
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') ),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)

SELECT conname AS superkey_constraint, key_schema, key_table, table_type, contype, key_col, o.column_name AS column_that_permits_one_value, o.type_schema, o.column_type
FROM keys_with_names AS k INNER JOIN cols_with_one_value AS o 
ON k.key_schema=o.table_schema 
AND k.key_table=o.table_name 
AND cardinality(k.key_col) >cardinality(o.column_name)
AND k.key_col @>o.column_name 
ORDER BY key_schema, key_table, contype, key_col;

Collections

This query belongs to the following collections:

NameDescription
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
Data typesQueries of this category provide information about the data types and their usage.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.