The list of all the queries

Perhaps incorrect use of 'NULL'

Query goal: Find Boolean expressions, queries, routines, and default values that refer to value 'NULL'. Perhaps NULL was intended instead. 'NULL' is a string (a value) but NULL is a special marker for denoting missing value.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: In case of DEFAULT values - if NULL is the intention, then drop the default value because by default each SQL base table column that is not a part of the primary key is optional, i.e., permits NULLs. In case of other types of objects - use NULL instead of NULL.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c'   
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
UNION SELECT trigger_schema, event_object_table || '.' || trigger_name, action_condition, 'TRIGGER WHEN' AS type
FROM INFORMATION_SCHEMA.triggers
WHERE action_condition IS NOT NULL
UNION SELECT schemaname, tablename || '.' || rulename, definition, 'RULE WHERE' AS type
FROM pg_catalog.pg_rules
WHERE schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT table_schema, table_name, view_definition, type
FROM (SELECT  table_schema, table_name, regexp_replace(view_definition,'[\r\n]','<br>','g') AS view_definition, 'VIEW SUBQUERY' AS type
FROM information_schema.views
WHERE 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)
UNION SELECT schemaname, matviewname, regexp_replace(definition,'[\r\n]','<br>','g') AS definition, 'MATERIALIZED VIEW SUBQUERY' AS type
FROM pg_catalog.pg_matviews) AS foo
WHERE view_definition ~*'^.+([[:space:]]+where[[:space:]]+|[[:space:]]+having[[:space:]]+).+$'
UNION SELECT pg_namespace.nspname AS routine_schema,  pg_proc.proname || '(' || pg_get_function_identity_arguments(pg_proc.oid)  || ')' AS routine_name,  regexp_replace(pg_get_functiondef(pg_proc.oid),'[\r\n]','<br>','g')  AS routine_src, 'ROUTINE' AS type
FROM pg_catalog.pg_proc, pg_catalog.pg_namespace
WHERE pg_proc.pronamespace = pg_namespace.oid 
AND pg_proc.prokind<>'a'
AND pg_proc.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') 
AND pg_namespace.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=pg_proc.oid)
UNION SELECT A.table_schema,
A.table_name || '.' || A.column_name,
A.column_default AS expression,
'COLUMN DEFAULT' AS type
FROM information_schema.columns A INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
INNER JOIN information_schema.tables AS T USING (table_schema, table_name)
WHERE (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
UNION SELECT D.domain_schema, D.domain_name, D.domain_default, 'DOMAIN' AS type
FROM information_schema.domains D INNER JOIN information_schema.schemata S ON D.domain_schema=S.schema_name
WHERE (D.domain_schema = 'public'
OR S.schema_owner<>'postgres'))
SELECT schema, name AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE expression ~*'['']NULL['']'
ORDER BY schema, type, name;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', A.table_schema, A.table_name, A.column_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE lower(column_default)='''null''::character varying'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
AND (A.table_schema, A.table_name) IN (SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE')
ORDER BY A.table_schema, A.table_name, A.column_name;
Drop the default that is associated directly with the base table column.
SELECT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', D.domain_schema, D.domain_name) AS statements
FROM information_schema.domains D INNER JOIN information_schema.schemata S ON D.domain_schema=S.schema_name
WHERE lower(D.domain_default)='''null''::character varying'
AND (D.domain_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY domain_schema, domain_name;
Drop the default that is associated with the domain.

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 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.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.
SyntacticsQueries of this category provide information about syntactic mistakes.
User-defined routinesQueries of this category provide information about the user-defined routines

Reference materials for further reading

Reference
https://www.bbc.com/future/article/20160325-the-names-that-break-computer-systems

The list of all the queries