All the non-primary key columns are optional

Back to Catalog
Goal Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements.
Notes If NOT NULL is specified through a domain the information of it is still available in the view INNER JOIN INFORMATION_SCHEMA.columns. Thus, there is no need to read a view with the information about domains.
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 License
Fixing Suggestion Increase the number of columns that have NOT NULL constraint. It means going back to the requirements and finding out as to whether some attribute or relationship type of an entity type is actually mandatory. A problem is that you cannot add the NOT NULL constraint to a column that already has NULLs in it. In this case you can add a CHECK constraint with the NOT VALID state. "If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option." (https://www.postgresql.org/docs/current/sql-altertable.html
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH 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')),
non_pk_columns AS (SELECT A.table_schema, A.table_name, A.column_name, A.is_nullable
FROM INFORMATION_SCHEMA.columns A
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
WHERE T.table_type='BASE TABLE' AND A.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) 
EXCEPT SELECT table_schema, table_name, column_name, is_nullable
FROM pk_columns)
SELECT table_schema, table_name
FROM non_pk_columns
GROUP BY table_schema, table_name
HAVING Count(*) FILTER (WHERE is_nullable='YES')  = Count(*)
ORDER BY table_schema, table_name;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
WITH pk_columns AS (SELECT  table_schema,  table_name, c.column_name, c.is_nullable, c.ordinal_position
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')),
non_pk_columns AS (SELECT A.table_schema, A.table_name, A.column_name, A.is_nullable, A.ordinal_position
FROM INFORMATION_SCHEMA.columns A
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
WHERE T.table_type='BASE TABLE' AND A.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) 
EXCEPT SELECT table_schema, table_name, column_name, is_nullable, ordinal_position
FROM pk_columns),
all_non_pk_NULL AS (SELECT table_schema, table_name
FROM non_pk_columns
GROUP BY table_schema, table_name
HAVING Count(*) FILTER (WHERE is_nullable='YES')  = Count(*))
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL;', table_schema, table_name, column_name) AS statements
FROM non_pk_columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM all_non_pk_NULL)
ORDER BY table_schema, table_name, ordinal_position;
Add the NOT NULL constraint. Most probably only a subset of the statements has to be executed.
WITH pk_columns AS (SELECT  table_schema,  table_name, c.column_name, c.is_nullable, c.ordinal_position
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')),
non_pk_columns AS (SELECT A.table_schema, A.table_name, A.column_name, A.is_nullable, A.ordinal_position
FROM INFORMATION_SCHEMA.columns A
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
WHERE T.table_type='BASE TABLE' AND A.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) 
EXCEPT SELECT table_schema, table_name, column_name, is_nullable, ordinal_position
FROM pk_columns),
all_non_pk_NULL AS (SELECT table_schema, table_name
FROM non_pk_columns
GROUP BY table_schema, table_name
HAVING Count(*) FILTER (WHERE is_nullable='YES')  = Count(*))
SELECT format('ALTER TABLE %1$I.%2$I ADD CONSTRAINT chk_%2$I_%3$I CHECK (%3$I IS NOT NULL) NOT VALID;', table_schema, table_name, column_name) AS statements
FROM non_pk_columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM all_non_pk_NULL)
ORDER BY table_schema, table_name, ordinal_position;
Add the NOT VALID CHECK constraint. Most probably only a subset of the statements has to be executed.
Collections

This query belongs to the following collections:

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:

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.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.
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).