NULLable but not NULL#
Inspired by Nullable but not null - Efe Öge I asked Qwen3 Coder to generate a SQL-only version.
CREATE OR REPLACE FUNCTION find_nullable_columns_and_count_null()
RETURNS TABLE (
table_name TEXT,
column_name TEXT,
data_type TEXT,
row_count BIGINT,
null_count BIGINT,
null_percentage NUMERIC
) AS $$
DECLARE
rec RECORD;
sql TEXT;
BEGIN
FOR rec IN
SELECT
c.table_schema,
c.table_name,
c.column_name,
c.data_type
FROM information_schema.columns c
JOIN information_schema.tables t
ON c.table_name = t.table_name
AND c.table_schema = t.table_schema
WHERE c.is_nullable = 'YES'
AND c.table_schema NOT IN ('information_schema', 'pg_catalog')
AND t.table_type = 'BASE TABLE'
LOOP
-- Count total rows
sql := format('SELECT COUNT(*) FROM %I.%I', rec.table_schema, rec.table_name);
EXECUTE sql INTO row_count;
-- Skip empty tables
CONTINUE WHEN row_count = 0;
-- Count null values in the column
sql := format('SELECT COUNT(*) FROM %I.%I WHERE %I IS NULL',
rec.table_schema, rec.table_name, rec.column_name);
EXECUTE sql INTO null_count;
-- Compute percentage
null_percentage := ROUND((null_count::NUMERIC / row_count) * 100, 2);
-- Return the result row
table_name := rec.table_name;
column_name := rec.column_name;
data_type := rec.data_type;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM find_nullable_columns_and_count_null() ORDER BY null_percentage, table_name, column_name;