Query goal: | Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. |
Notes about the query: | Columns that belong to the primary keys are not considered because NOT NULL constraint is added automatically to these columns. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. |
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: | Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring NOT NULL at the level of the domain and not at the level of base table columns. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH domains_null AS (SELECT pg_namespace.nspname AS domain_schema, pg_type.typname AS domain_name FROM pg_catalog.pg_type, pg_catalog.pg_namespace WHERE pg_namespace.oid = pg_type.typnamespace AND pg_type.typnotnull = FALSE 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)), pk_columns AS (SELECT table_schema, table_name, c.column_name, c.is_nullable FROM (information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name)) INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name) WHERE tc.constraint_type IN ('PRIMARY KEY')), col_nulls AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.is_nullable FROM domains_null AS d INNER JOIN Information_schema.column_domain_usage AS du USING (domain_schema, domain_name) INNER JOIN Information_schema.columns AS c USING (table_schema, table_name, column_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 (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns)), col_nulls_agg AS (SELECT domain_schema, domain_name, Count(*) FILTER (WHERE is_nullable='YES') AS cnt_is_null, Count(*) FILTER (WHERE is_nullable='NO') AS cnt_is_not_null FROM col_nulls GROUP BY domain_schema, domain_name) SELECT domain_schema, domain_name, string_agg(table_schema || '.' || table_name || '.' || column_name,';<br>' ORDER BY table_schema, table_name, column_name) AS columns FROM col_nulls WHERE (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM col_nulls_agg WHERE cnt_is_null=0 AND cnt_is_not_null>0) GROUP BY domain_schema, domain_name ORDER BY domain_schema, domain_name; |
SQL query | Description |
---|---|
WITH domains_null AS (SELECT pg_namespace.nspname AS domain_schema, pg_type.typname AS domain_name FROM pg_catalog.pg_type, pg_catalog.pg_namespace WHERE pg_namespace.oid = pg_type.typnamespace AND pg_type.typnotnull = FALSE AND nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres')), pk_columns AS (SELECT table_schema, table_name, c.column_name FROM information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name) INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name) WHERE tc.constraint_type IN ('PRIMARY KEY')), col_nulls AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.is_nullable FROM domains_null AS d INNER JOIN Information_schema.columns AS c USING (domain_schema, domain_name) WHERE c.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres') AND (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns)), col_nulls_agg AS (SELECT domain_schema, domain_name, Count(*) FILTER (WHERE is_nullable='YES') AS cnt_is_null, Count(*) FILTER (WHERE is_nullable='NO') AS cnt_is_not_null FROM col_nulls GROUP BY domain_schema, domain_name) SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP NOT NULL;', table_schema, table_name, column_name) AS statements FROM col_nulls WHERE (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM col_nulls_agg WHERE cnt_is_null=0 AND cnt_is_not_null>0) ORDER BY table_schema, table_name, column_name; | Drop the NOT NULL constraint to the column. |
WITH domains_null AS (SELECT pg_namespace.nspname AS domain_schema, pg_type.typname AS domain_name FROM pg_catalog.pg_type, pg_catalog.pg_namespace WHERE pg_namespace.oid = pg_type.typnamespace AND pg_type.typnotnull = FALSE AND nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres')), pk_columns AS (SELECT table_schema, table_name, c.column_name FROM information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name) INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name) WHERE tc.constraint_type IN ('PRIMARY KEY')), col_nulls AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.is_nullable FROM domains_null AS d INNER JOIN Information_schema.columns AS c USING (domain_schema, domain_name) WHERE c.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres') AND (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns)), col_nulls_agg AS (SELECT domain_schema, domain_name, Count(*) FILTER (WHERE is_nullable='YES') AS cnt_is_null, Count(*) FILTER (WHERE is_nullable='NO') AS cnt_is_not_null FROM col_nulls GROUP BY domain_schema, domain_name) SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;', domain_schema, domain_name) AS statements FROM col_nulls WHERE (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM col_nulls_agg WHERE cnt_is_null=0 AND cnt_is_not_null>0) ORDER BY statements; | Add the NOT NULL constraint to 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 |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Domains | Queries of this category provide information about reusable specifications of column properties. |
Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
Reference |
---|
https://www.postgresql.org/docs/current/sql-createdomain.html |