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).
Type
Problem detection (Each row in the result could represent a flaw in the design)
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
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 || ' ',';' 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
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Default value
Queries of this catergory provide information about the use of default values.
Domains
Queries of this category provide information about reusable specifications of column properties.