Goal Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain.
Notes The query takes into account constraints that are associated directly with the column as well as constraints that are associated with the column through a domain. The query excludes constraints where subconditions are connected by the OR operator because in this case a rule could be that if a condition P holds a condition Q must also hold. For instance, if in the column X are values 'A' or 'B', then in the column Y there must be value 'C'.
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 (opens in new tab)
Fixing Suggestion Drop the constraint and create a separate (classifier) table and a foreign key constraint that references to it. In case of the column that uses an enumerated type start also using a separate classifier table instead.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH table_check AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, ccu.constraint_schema, ccu.constraint_name, cc.check_clause
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE cc.check_clause~*'^.+=.*ANY.*[(].*ARRAY[[].+[])].*$' 
AND cc.check_clause!~*'[[:space:]]OR[[:space:]]' 
AND t.table_type IN ('BASE TABLE', 'FOREIGN')
AND ccu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

domain_check AS (SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, dc.constraint_schema, dc.constraint_name, cc.check_clause
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE', 'FOREIGN') 
AND cc.check_clause~*'^.+=.*ANY.*[(].*ARRAY[[].+[])].*$' 
AND cc.check_clause!~*'[[:space:]]OR[[:space:]]' 
AND cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

enum_types AS (SELECT nspname AS type_schema, typname AS type_name, typtype
FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid
WHERE typtype='e' 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)),

enum_cols AS (SELECT table_schema, table_name, table_type, column_name, data_type,udt_schema, udt_name
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE (udt_schema, udt_name) IN (SELECT type_schema, type_name FROM enum_types) 
AND table_type IN ('BASE TABLE', 'FOREIGN') 
AND table_schema 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, constraint_schema, constraint_name, check_clause
FROM table_check
UNION SELECT table_schema, table_name, table_type, column_name, constraint_schema, constraint_name, check_clause
FROM domain_check
UNION SELECT table_schema, table_name, table_type, column_name, udt_schema, udt_name, 'ENUMERATION TYPE' AS check_clause
FROM enum_cols
ORDER BY table_schema, table_name;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('ALTER %4$s TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', ccu.table_schema, ccu.table_name, ccu.constraint_name, CASE WHEN table_type='FOREIGN' THEN table_type END) AS statements
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE cc.check_clause~*'^.+=.*ANY.*[(].*ARRAY[[].+[])].*$' 
AND table_type IN ('BASE TABLE','FOREIGN')
AND ccu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY ccu.table_schema, ccu.table_name, ccu.constraint_name;

Drop the check constraint that is directly associated with the table.

Fix Action #2
SELECT format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', dc.domain_schema, dc.domain_name, dc.constraint_name) AS statements
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' AND cc.check_clause~*'^.+=.*ANY.*[(].*ARRAY[[].+[])].*$' AND 
cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND catalog_name IS NOT NULL AND schema_name IS NOT NULL)
ORDER BY dc.domain_schema, dc.domain_name, dc.constraint_name;

Drop the domain check constraint.

SQL Query to Generate FixDescription
SELECT format('ALTER %4$s TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', ccu.table_schema, ccu.table_name, ccu.constraint_name, CASE WHEN table_type='FOREIGN' THEN table_type END) AS statements
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE cc.check_clause~*'^.+=.*ANY.*[(].*ARRAY[[].+[])].*$' 
AND table_type IN ('BASE TABLE','FOREIGN')
AND ccu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY ccu.table_schema, ccu.table_name, ccu.constraint_name;
Drop the check constraint that is directly associated with the table.
SELECT format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', dc.domain_schema, dc.domain_name, dc.constraint_name) AS statements
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' AND cc.check_clause~*'^.+=.*ANY.*[(].*ARRAY[[].+[])].*$' AND 
cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND catalog_name IS NOT NULL AND schema_name IS NOT NULL)
ORDER BY dc.domain_schema, dc.domain_name, dc.constraint_name;
Drop the domain check constraint.

Collections

This query belongs to the following collections:

Find problems about integrity constraints

A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview

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 .

NameDescription
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
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:

CHECK constraints

Queries of this category provide information about CHECK constraints.

Comfortability of database evolution

Queries of this category provide information about the means that influence database evolution.

Database design antipatterns

Queries of this category provide information about possible occurrences of SQL database design antipatterns.

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Database design antipatternsQueries of this category provide information about possible occurrences of SQL database design antipatterns.

Further reading and related materials:

This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 11: 31 Flavours.
Mistake (2): https://www.red-gate.com/simple-talk/sql/database-administration/five-simple--database-design-errors-you-should-avoid/
Blaha, M.R., Premerlani, W.J.: Observed idiosyncracies of relational database designs. In: 2nd Working Conference on Reverse Engineering, pp. 116–125. IEEE, (1995). https://doi.org/10.1109/WCRE.1995.514700 (Encoded enumerations without deciphering)
Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345. (2020). https://doi.org/10.1145/3318464.3389754 (Enumerated Types)
Balogh, G., Gergely, T., Beszédes, Á., Szarka, A., Fábián, Z.: Capturing expert knowledge to guide data flow and structure analysis of large corporate databases. Acta Polytechnica Hungarica 16(4), 7–26 (2019). (Restrict column values with constraints)
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Using constraints to restrict values in a column)
Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55–64. ACM, (2018). https://doi.org/10.1145/3183519.3183529 (Values in attribute definition)
Reference
This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 11: 31 Flavours.
Mistake (2): https://www.red-gate.com/simple-talk/sql/database-administration/five-simple--database-design-errors-you-should-avoid/
Blaha, M.R., Premerlani, W.J.: Observed idiosyncracies of relational database designs. In: 2nd Working Conference on Reverse Engineering, pp. 116–125. IEEE, (1995). https://doi.org/10.1109/WCRE.1995.514700 (Encoded enumerations without deciphering)
Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345. (2020). https://doi.org/10.1145/3318464.3389754 (Enumerated Types)
Balogh, G., Gergely, T., Beszédes, Á., Szarka, A., Fábián, Z.: Capturing expert knowledge to guide data flow and structure analysis of large corporate databases. Acta Polytechnica Hungarica 16(4), 7–26 (2019). (Restrict column values with constraints)
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Using constraints to restrict values in a column)
Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55–64. ACM, (2018). https://doi.org/10.1145/3183519.3183529 (Values in attribute definition)