Goal Find columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises.
Notes The query does not take into account a possibility that the column is declared mandatory by using a check constraint.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability High Few or no false-positive results
License MIT (opens in new tab)
Fixing Suggestion Drop either the NOT NULL constraint of the domain or the NOT NULL constraint that is directly associated with the column. If you drop the NOT NULL constraint of the domain, then do not forget that the domain could be used in case of different columns.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH columns_not_null AS (SELECT pg_namespace.nspname AS table_schema, 
pg_class.relname AS table_name, pg_attribute.attname AS column_name 
FROM pg_catalog.pg_class, pg_catalog.pg_attribute, pg_catalog.pg_namespace 
WHERE pg_class.oid = pg_attribute.attrelid AND pg_namespace.oid = pg_class.relnamespace AND pg_attribute.attnotnull = TRUE),

domains_not_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 = TRUE),

pk_columns AS (SELECT table_constraints.table_schema, table_constraints.table_name, column_name 
FROM  information_schema.table_constraints INNER JOIN information_schema.constraint_column_usage
USING (table_schema, table_name, constraint_name)
WHERE table_constraints.constraint_type='PRIMARY KEY')

SELECT table_schema, table_name, column_name, domain_schema, domain_name
FROM INFORMATION_SCHEMA.columns WHERE (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 (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM domains_not_null) 
AND (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name FROM columns_not_null) 
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns) 
ORDER BY table_schema, table_name, ordinal_position;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
WITH columns_not_null AS (SELECT pg_namespace.nspname AS table_schema, 
pg_class.relname AS table_name, pg_attribute.attname AS column_name 
FROM pg_catalog.pg_class, pg_catalog.pg_attribute, pg_catalog.pg_namespace 
WHERE pg_class.oid = pg_attribute.attrelid AND pg_namespace.oid = pg_class.relnamespace AND pg_attribute.attnotnull = TRUE),

domains_not_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 = TRUE),

pk_columns AS (SELECT table_constraints.table_schema, table_constraints.table_name, column_name 
FROM  information_schema.table_constraints INNER JOIN information_schema.constraint_column_usage
USING (table_schema, table_name, constraint_name)
WHERE table_constraints.constraint_type='PRIMARY KEY'),

duplicate_not_null AS (SELECT table_schema, table_name, column_name, domain_schema, domain_name
FROM INFORMATION_SCHEMA.columns WHERE (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 (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM domains_not_null) 
AND (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name FROM columns_not_null) 
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns))
SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I DROP NOT NULL;', domain_schema, domain_name) AS statements
FROM duplicate_not_null
ORDER BY statements;

Drop the domain NOT NULL constraint.

Fix Action #2
WITH columns_not_null AS (SELECT pg_namespace.nspname AS table_schema, 
pg_class.relname AS table_name, pg_attribute.attname AS column_name 
FROM pg_catalog.pg_class, pg_catalog.pg_attribute, pg_catalog.pg_namespace 
WHERE pg_class.oid = pg_attribute.attrelid AND pg_namespace.oid = pg_class.relnamespace AND pg_attribute.attnotnull = TRUE),

domains_not_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 = TRUE),

pk_columns AS (SELECT table_constraints.table_schema, table_constraints.table_name, column_name 
FROM  information_schema.table_constraints INNER JOIN information_schema.constraint_column_usage
USING (table_schema, table_name, constraint_name)
WHERE table_constraints.constraint_type='PRIMARY KEY'),

duplicate_not_null AS (SELECT table_schema, table_name, column_name, domain_schema, domain_name
FROM INFORMATION_SCHEMA.columns WHERE (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 (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM domains_not_null) 
AND (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name FROM columns_not_null) 
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns))
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP NOT NULL;', table_schema, table_name, column_name) AS statements
FROM duplicate_not_null
ORDER BY statements;

Drop the column NOT NULL constraint.

SQL Query to Generate FixDescription
WITH columns_not_null AS (SELECT pg_namespace.nspname AS table_schema, 
pg_class.relname AS table_name, pg_attribute.attname AS column_name 
FROM pg_catalog.pg_class, pg_catalog.pg_attribute, pg_catalog.pg_namespace 
WHERE pg_class.oid = pg_attribute.attrelid AND pg_namespace.oid = pg_class.relnamespace AND pg_attribute.attnotnull = TRUE),

domains_not_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 = TRUE),

pk_columns AS (SELECT table_constraints.table_schema, table_constraints.table_name, column_name 
FROM  information_schema.table_constraints INNER JOIN information_schema.constraint_column_usage
USING (table_schema, table_name, constraint_name)
WHERE table_constraints.constraint_type='PRIMARY KEY'),

duplicate_not_null AS (SELECT table_schema, table_name, column_name, domain_schema, domain_name
FROM INFORMATION_SCHEMA.columns WHERE (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 (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM domains_not_null) 
AND (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name FROM columns_not_null) 
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns))
SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I DROP NOT NULL;', domain_schema, domain_name) AS statements
FROM duplicate_not_null
ORDER BY statements;
Drop the domain NOT NULL constraint.
WITH columns_not_null AS (SELECT pg_namespace.nspname AS table_schema, 
pg_class.relname AS table_name, pg_attribute.attname AS column_name 
FROM pg_catalog.pg_class, pg_catalog.pg_attribute, pg_catalog.pg_namespace 
WHERE pg_class.oid = pg_attribute.attrelid AND pg_namespace.oid = pg_class.relnamespace AND pg_attribute.attnotnull = TRUE),

domains_not_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 = TRUE),

pk_columns AS (SELECT table_constraints.table_schema, table_constraints.table_name, column_name 
FROM  information_schema.table_constraints INNER JOIN information_schema.constraint_column_usage
USING (table_schema, table_name, constraint_name)
WHERE table_constraints.constraint_type='PRIMARY KEY'),

duplicate_not_null AS (SELECT table_schema, table_name, column_name, domain_schema, domain_name
FROM INFORMATION_SCHEMA.columns WHERE (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 (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM domains_not_null) 
AND (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name FROM columns_not_null) 
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name FROM pk_columns))
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP NOT NULL;', table_schema, table_name, column_name) AS statements
FROM duplicate_not_null
ORDER BY statements;
Drop the column NOT NULL constraint.

Collections

This query belongs to the following collections:

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 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:

Duplication of implementation elements

Queries of this catergory provide information about the duplication of the database objects.

Missing data

Queries of this category provide information about missing data (NULLs) in a database.

NameDescription
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.

Further reading and related materials:

https://refactoring.guru/smells/alternative-classes-with-different-interfaces
https://refactoring.guru/smells/duplicate-code
The corresponding code smell in case of cleaning code is "G5: Duplication". (Robert C. Martin, Clean Code)
Reference
https://refactoring.guru/smells/alternative-classes-with-different-interfaces
https://refactoring.guru/smells/duplicate-code
The corresponding code smell in case of cleaning code is "G5: Duplication". (Robert C. Martin, Clean Code)