Nullable but not null

nullable_but_not_null.sql

-- SQL-only version of
-- https://efe.me/posts/nullable-but-not-null/

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;