The list of all the queries

Default should be declared at the level of domain not at the level of base table columns

Query goal: Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values).
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 values that are associated directly with columns and add a default value to the domain that is used in case of all the columns.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH fk as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
o.conkey AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f'),
fk_unnest as (select target_schema, target_table, target_table_oid, target_col, target_col_num, ordin
from fk, unnest(fk. target_col) with ordinality as f(target_col_num, ordin)),
fk_with_names as (select target_schema, target_table, a_target.attname as target_col
from fk_unnest fk inner join  pg_attribute a_target on fk.target_col_num = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false),
col_defs AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.column_default
FROM Information_schema.domains 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 NOT EXISTS (SELECT 1 FROM fk_with_names AS fk WHERE fk.target_schema=c.table_schema AND fk.target_table=c.table_name AND fk.target_col=c.column_name) AND 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 domain_default IS NULL),
defaults_on_columns AS (SELECT domain_schema, domain_name, string_agg(table_schema || '.' || table_name || '.' || column_name || ' DEFAULT: ' || column_default || '<br>',';' ORDER BY table_schema, table_name, column_name) AS columns
FROM col_defs
GROUP BY domain_schema, domain_name
HAVING Count(DISTINCT column_default)=1)
SELECT domain_schema, domain_name, columns
FROM defaults_on_columns AS dc
WHERE NOT EXISTS (SELECT 1
FROM col_defs AS cd
WHERE dc.domain_schema=cd.domain_schema 
AND dc.domain_name=cd.domain_name
AND cd.column_default IS NULL)
ORDER BY domain_schema, domain_name;

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.
Default valueQueries of this catergory provide information about the use of default values.
DomainsQueries of this category provide information about reusable specifications of column properties.

The list of all the queries