Query goal: | Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values. |
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: | Add values to the type by using an ALTER TYPE statement or drop the type. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT nspname AS type_schema, typname, Count(enumtypid) AS number_of_labels 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 ORDER BY Count(enumtypid), nspname, typname; |
SQL query | Description |
---|---|
WITH suspected_types 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) SELECT format('DROP TYPE %1$I.%2$I;', type_schema, typname) AS statements FROM suspected_types ORDER BY type_schema, typname; | Drop the type. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, 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 . |
Category name | Category description |
---|---|
Data types | Queries of this category provide information about the data types and their usage. |
Stubs | Queries of this catergory provide information about stubs (piece of code used to stand in for some other programming functionality). |
User-defined types | Queries of this category provide information about user-defined types in the database. |