Goal Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value.
Notes The query takes into account that the default value may be assigned to the column through a domain. 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.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Drop the default value.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
with keys as (select 
o.conname, 
(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,
case when o.contype='p' then 'PRIMARY KEY'
else 'UNIQUE' end AS constraint_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p')),
keys_unnest as (select conname, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, constraint_type
from keys, unnest(keys. target_col) with ordinality as f(target_col_num, ordin)),
keys_with_names as (select conname, target_schema, target_table, array_agg(a_target.attname order by a_target.attname)::text[] as key_columns, constraint_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false
group by conname, target_schema, target_table, constraint_type),
def_columns AS (SELECT  table_schema,  table_name, c.column_name, coalesce(column_default, domain_default) AS def
FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL),
def_columns_with_names AS (
SELECT table_schema, table_name, array_agg(column_name ORDER BY column_name)::text[] AS def_columns, string_agg(column_name || ' DEFAULT:' || def, ';<br>' ORDER BY column_name) AS def_columns_extended
FROM def_columns
WHERE def~*'^['']' 
OR  def~*'^(true|false)$' 
OR def~*'^([[:digit:]]|[.])+$'
OR def~*'^[(]+([[:digit:]]|[.])+[)]+'
GROUP BY table_schema, table_name)
select d.table_schema, d.table_name, d.def_columns_extended as def_columns, k.key_columns, k.constraint_type
from def_columns_with_names as d, keys_with_names as k
where d.table_schema=k.target_schema and d.table_name=k.target_table and d.def_columns@>k.key_columns
order by target_schema, target_table;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
with keys as (select 
o.conname, 
(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,
case when o.contype='p' then 'PRIMARY KEY'
else 'UNIQUE' end AS constraint_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p')),
keys_unnest as (select conname, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, constraint_type
from keys, unnest(keys. target_col) with ordinality as f(target_col_num, ordin)),

keys_with_names as (select conname, target_schema, target_table, a_target.attname as key_column, constraint_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false),

def_columns AS (SELECT  table_schema,  table_name, c.column_name, d.domain_schema, d.domain_name
FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE domain_default IS NOT NULL
AND (domain_default~*'^['']' 
OR  domain_default~*'^(true|false)$' 
OR domain_default~*'^([[:digit:]]|[.])+$'
OR domain_default~*'^[(]+([[:digit:]]|[.])+[)]+'))

SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', d.domain_schema, d.domain_name) AS statements
FROM def_columns as d INNER JOIN keys_with_names as k 
ON d.table_schema=k.target_schema AND d.table_name=k.target_table AND d.column_name=k.key_column
ORDER BY statements;

Drop the default value associated with the domain.

Fix Action #2
with keys as (select 
o.conname, 
(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,
case when o.contype='p' then 'PRIMARY KEY'
else 'UNIQUE' end AS constraint_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p')),
keys_unnest as (select conname, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, constraint_type
from keys, unnest(keys. target_col) with ordinality as f(target_col_num, ordin)),

keys_with_names as (select conname, target_schema, target_table, a_target.attname as key_column, constraint_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false),

def_columns AS (SELECT  table_schema,  table_name, c.column_name
FROM INFORMATION_SCHEMA.columns AS c
WHERE column_default IS NOT NULL
AND (column_default~*'^['']' 
OR  column_default~*'^(true|false)$' 
OR column_default~*'^([[:digit:]]|[.])+$'
OR column_default~*'^[(]+([[:digit:]]|[.])+[)]+'))

SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', d.table_schema, d.table_name, d.column_name) AS statements
FROM def_columns as d INNER JOIN keys_with_names as k 
ON d.table_schema=k.target_schema AND d.table_name=k.target_table AND d.column_name=k.key_column
ORDER BY d.table_schema, d.table_name, d.column_name;

Drop the default value associated with the column.

SQL Query to Generate FixDescription
with keys as (select 
o.conname, 
(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,
case when o.contype='p' then 'PRIMARY KEY'
else 'UNIQUE' end AS constraint_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p')),
keys_unnest as (select conname, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, constraint_type
from keys, unnest(keys. target_col) with ordinality as f(target_col_num, ordin)),

keys_with_names as (select conname, target_schema, target_table, a_target.attname as key_column, constraint_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false),

def_columns AS (SELECT  table_schema,  table_name, c.column_name, d.domain_schema, d.domain_name
FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE domain_default IS NOT NULL
AND (domain_default~*'^['']' 
OR  domain_default~*'^(true|false)$' 
OR domain_default~*'^([[:digit:]]|[.])+$'
OR domain_default~*'^[(]+([[:digit:]]|[.])+[)]+'))

SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', d.domain_schema, d.domain_name) AS statements
FROM def_columns as d INNER JOIN keys_with_names as k 
ON d.table_schema=k.target_schema AND d.table_name=k.target_table AND d.column_name=k.key_column
ORDER BY statements;
Drop the default value associated with the domain.
with keys as (select 
o.conname, 
(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,
case when o.contype='p' then 'PRIMARY KEY'
else 'UNIQUE' end AS constraint_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p')),
keys_unnest as (select conname, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, constraint_type
from keys, unnest(keys. target_col) with ordinality as f(target_col_num, ordin)),

keys_with_names as (select conname, target_schema, target_table, a_target.attname as key_column, constraint_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false),

def_columns AS (SELECT  table_schema,  table_name, c.column_name
FROM INFORMATION_SCHEMA.columns AS c
WHERE column_default IS NOT NULL
AND (column_default~*'^['']' 
OR  column_default~*'^(true|false)$' 
OR column_default~*'^([[:digit:]]|[.])+$'
OR column_default~*'^[(]+([[:digit:]]|[.])+[)]+'))

SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', d.table_schema, d.table_name, d.column_name) AS statements
FROM def_columns as d INNER JOIN keys_with_names as k 
ON d.table_schema=k.target_schema AND d.table_name=k.target_table AND d.column_name=k.key_column
ORDER BY d.table_schema, d.table_name, d.column_name;
Drop the default value associated with the column.

Collections

This query belongs to the following collections:

Find problems about integrity constraints

A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview

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 about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
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:

Comfortability of data management

Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.

Default value

Queries of this catergory provide information about the use of default values.

Fatal problems

Queries of this category provide information about problems that render a part of a database unusable.

Uniqueness

Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

Validity and completeness

Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

NameDescription
Comfortability of data managementQueries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Default valueQueries of this catergory provide information about the use of default values.
Fatal problemsQueries of this category provide information about problems that render a part of a database unusable.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).