The list of all the queries

NOT NULL constraint is directly associated with a column instead of the domain of the column

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 statements for generating SQL statements that help us to fix the problem

SQL queryDescription
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.

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
DomainsQueries of this category provide information about reusable specifications of column properties.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.

Reference materials for further reading

Reference
https://www.postgresql.org/docs/current/sql-createdomain.html

The list of all the queries