Query goal: | Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value. |
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: | Drop the default value. |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT c.table_schema, t.table_type, c.table_name, c.column_name, c.data_type, c.domain_schema, c.domain_name, c.column_default, domain_default FROM information_schema.columns AS c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name) INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name) WHERE c.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 (column_default IS NOT NULL OR domain_default IS NOT NULL) AND (c.column_default~'^NULL::' OR d.domain_default~'^NULL::') ORDER BY t.table_type, c.table_schema, c.table_name, c.ordinal_position; |
SQL query | Description |
---|---|
SELECT format('ALTER %4$s TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', A.table_schema, A.table_name, A.column_name, CASE WHEN B.table_type='FOREIGN' THEN table_type END) AS statements FROM information_schema.columns A INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name INNER JOIN information_schema.tables B USING (table_schema, table_name) WHERE A.column_default~'^NULL::' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY A.table_schema, A.table_name, A.column_name; | Drop the default that is directly associated with the column. |
SELECT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', D.domain_schema, D.domain_name) AS statements FROM information_schema.domains D INNER JOIN information_schema.schemata S ON D.domain_schema=S.schema_name WHERE D.domain_default~'^NULL::' AND (D.domain_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY domain_schema, domain_name; | Drop the default that is associated with the domain. |
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 |
---|---|
Default value | Queries of this catergory provide information about the use of default values. |
Missing data | Queries of this category provide information about missing data (NULLs) in a database. |