clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36283 in the last week).

CREATE TABLE fortune500( id serial PRIMARY KEY , ticker int , profits_change text , industry text ); INSERT INTO fortune500 (ticker, profits_change, industry) VALUES (5, 'a' , NULL) , (6, 'a' , NULL) , (6, NULL, NULL);
3 rows affected
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_count_nulls(_tbl regclass) RETURNS TABLE (column_name text, missing_values bigint) LANGUAGE plpgsql STABLE PARALLEL SAFE AS $func$ BEGIN RETURN QUERY EXECUTE ( SELECT format( $$ SELECT x.* FROM (SELECT count(*) AS ct, %s FROM %s) t CROSS JOIN LATERAL (VALUES %s) x(col, nulls) ORDER BY nulls DESC, col DESC $$, string_agg(format('count(%1$I) AS %1$I', attname), ', ') , $1 , string_agg(format('(%1$L, ct - %1$I)', attname), ', ') ) FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum > 0 AND NOT attisdropped -- more filters? ); END $func$;
 hidden batch(es)


SELECT * FROM f_count_nulls('fortune500');
column_name missing_values
industry 3
profits_change 1
ticker 0
id 0
 hidden batch(es)