| Query goal: | Find base table columns that have an enumerated type that seems to emulate Boolean type. |
| Notes about the query: | Query checks as to whether an enumerated type has at least one label that name contains true,false,yes,no,on,off,jah,ei,unkn and whether the type has two or three labels. The query returns labels (as an array) as well as an array of identifiers of columns (schema_name.table_name.column_name) that have the type. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | High (Few or no false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Specify for each column a right data type that takes into account expected values in the column. Use BOOLEAN type instead of emulating it based on an enumerated type. Change the type of the column to BOOLEAN. Drop the enumerated type. |
| Data source: | INFORMATION_SCHEMA+system catalog |
| SQL query: | Click on query to copy it
WITH bool_enum AS (SELECT nspname AS type_schema, typname
FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid
INNER JOIN pg_catalog.pg_namespace n ON n.oid=t.typnamespace
WHERE enumlabel~*'^(true|false|yes|no|on|off|jah|ei|unkn)$' AND 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 nspname AS type_schema, typname, array_agg(enumlabel ORDER BY enumsortorder) AS values,
(SELECT array_agg(table_schema || '.' || table_name || '.' || column_name ORDER BY table_schema, table_name) AS cols
FROM information_schema.columns AS c WHERE c.udt_schema=n.nspname AND c.udt_name=t.typname) AS cols
FROM pg_catalog.pg_type t INNER 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 (nspname, typname) IN (SELECT type_schema, typname
FROM bool_enum)
GROUP BY nspname, typname
HAVING Count(*) IN (2, 3); |