All the non-primary key columns are optional

Query 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 about the query:

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.

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:

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:

Click on query to copy it

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

SQL query	Description
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.

SQL query

Description

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

Collection name	Collection description
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 .

Collection name

Collection description

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 .

Categories where the query belongs to

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

Category name

Category description

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.

Missing data

Queries of this category provide information about missing data (NULLs) in a database.

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).