PostgreSQL: count datasets / lines containing NULL values

nullpostgresql

I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved.

For example:

CREATE TABLE tab1 (
id INTEGER PRIMARY KEY,
dat1 VARCHAR,
dat2 VARCHAR);

INSERT INTO tab1 VALUES 
(1, NULL, 'abc'), 
(2, 'abc', NULL), 
(3, 'abc', 'abc');

SELECT count(*)
FROM tab1
WHERE id IS NULL OR dat1 IS NULL OR dat2 IS NULL;
-- 2

(I know that Primary Keys are not likely to habe NULLs 😉

My current solution uses R to build the query.
Any suggestions?

Best Answer

You will need to use dynamic SQL to acheive this, perhaps like this:

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as 
select generate_series(0,9) as id, null::integer as val;

create table my_table2 as 
select g as id, case when g<=5 then null::integer else 1 end as val
from generate_series(0,9) g;

function:

create function get_with_nulls_rowcount(p_schema in text, p_table in text) 
                returns integer language plpgsql as $$
declare 
  n integer;
  s text;
begin
  --
  select array_to_string(array_agg(column_name::text||' is null'), ' or ')
  into s
  from information_schema.columns
  where table_schema=p_schema and table_name=p_table;
  --
  execute 'select count(*) from '||p_table||' where '||s into n;
  return n;
end;$$;

query:

select table_schema, table_name, 
       get_with_nulls_rowcount(table_schema, table_name)
from information_schema.tables
where table_schema='stack';

result:

 table_schema | table_name | get_with_nulls_rowcount
--------------+------------+-------------------------
 stack        | my_table   |                      10
 stack        | my_table2  |                       6
(2 rows)
Related Question