The list of all the queries

At most one row is permitted in a table (based on enumeration types)

Query goal: Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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),

simple_keys as (select 
n_t.nspname 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_constraint 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.conkey[1] 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
inner join pg_namespace n_t on c.relnamespace=n_t.oid
where o.contype in ('p','u')
and c.relkind in ('r','f')
and cardinality(o.conkey)=1
and n_t.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))

SELECT table_schema, table_name, table_type, column_name AS key_column_name, s.type_schema AS key_column_type_schema, typname AS key_column_type_name
FROM simple_keys AS s inner join one_value AS o ON s.type_schema=o.type_schema AND s.column_type=o.typname
AND EXISTS (SELECT *
FROM INFORMATION_SCHEMA.columns AS c
WHERE c.table_schema=s.table_schema
AND c.table_name=s.table_name
AND c.column_name=s.column_name
AND c.is_nullable='NO')
ORDER BY table_schema, table_name;

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
Data at the database logical levelQueries of this category provide information about data in base tables.
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.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

The list of all the queries