Query goal: | Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the columns mandatory. |
Notes about the query: | The query considers both column names in English and Estonian. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Make the column mandatory by declaring NOT NULL constraint to it. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH fk_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype = 'f') t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false) SELECT c.table_schema, c.table_name, c.column_name, c.data_type, c.character_maximum_length, coalesce(c.column_default, d.domain_default) AS column_default FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name) WHERE column_name~*'^(is|has|on)_' AND (c.data_type<>'boolean') AND c.is_nullable='YES' AND (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') 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) AND NOT EXISTS (SELECT * FROM fk_columns WHERE fk_columns.table_schema=c.table_schema AND fk_columns.table_name=c.table_name AND fk_columns.column_name=c.column_name) ORDER BY c.table_schema, c.table_name, c.column_name; |
SQL query | Description |
---|---|
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL', A.table_schema, A.table_name , A.column_name) AS statements FROM information_schema.columns A INNER JOIN information_schema.tables T ON A.table_schema = T.table_schema AND A.table_name = T.table_name INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name WHERE A.data_type<>'boolean' AND column_name~*'^(is|has|on)_' AND is_nullable='YES' AND T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY A.table_schema, A.table_name, A.column_name; | Apply the NOT NUL L constraint directly to the column. |
Collection name | Collection description |
---|---|
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 . |
Category name | Category description |
---|---|
Boolean data | Queries of this category provide information about truth-values data that is kept in the database. |
Comfortability of data management | Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient. |
Data types | Queries of this category provide information about the data types and their usage. |
Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |