Query goal: | Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Fixing suggestion: | Define NOT NULL constraints to the columns, if necessary. |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT A.table_schema, A.table_name , A.column_name, CASE WHEN data_type ILIKE 'character%' AND character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length::text || ')' WHEN data_type ILIKE 'timestamp%' AND datetime_precision IS NOT NULL THEN data_type || '(' || datetime_precision || ')' WHEN data_type ILIKE 'numeric%' AND numeric_precision IS NOT NULL THEN data_type || '(' || numeric_precision::text || ',' ||coalesce(numeric_scale,0)::text || ')' WHEN data_type ILIKE 'interval%' AND interval_type IS NOT NULL THEN data_type || '(' || interval_type::text || ')' WHEN data_type='USER-DEFINED' THEN udt_schema || '.' || udt_name ELSE data_type END AS data_type FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name WHERE 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.ordinal_position; |
Collection name | Collection description |
---|---|
Find problems by overview | Queries 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 . |
Category name | Category description |
---|---|
Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
Validity and completeness | Queries 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). |
Reference |
---|
http://www.dbdebunk.com/2017/04/null-value-is-contradiction-in-terms.html |