Question 337132 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get percentage of non-null values in a table by column

I am using a PostgreSQL table that has 800+ columns. I need to determine which columns have 100% population. I have two fields that I know are fully populated but I need to determine if there are others.

I can get a list of the columns in the table using

select column_name 
from information_schema.columns 
where table_name = 'projectmeasures'

I just don’t know how to use this to loop through each column to determine count(column_name) / count(*)

Tags:

2

Answers


  1. 0

    You could use PL/pgSQL code like this to generate a query that tells you which columns contain no NULL values:

    DO
$$DECLARE
   r record;
   stmt text := 'SELECT ';
   firstcol boolean := TRUE;
BEGIN
   FOR r IN
      SELECT table_schema,
             table_name,
             column_name
      FROM information_schema.columns
      WHERE table_schema = 'public'
        AND table_name = 'projectmeasures'
      ORDER BY ordinal_position
   LOOP
      IF firstcol THEN
         firstcol := FALSE;
      ELSE
         stmt := stmt || ', ';
      END IF;

      stmt := stmt || format(
                         'bool_and(%I IS NOT NULL) AS %I',
                         r.column_name,
                         r.column_name || '_has_no_nulls'
                      );
   END LOOP;

   stmt := stmt || format(
                      ' FROM %I.%I',
                      r.table_schema,
                      r.table_name
                   );

   RAISE NOTICE '%', stmt;
END;$$;
    Login or Signup to reply.
  2. 0

    To list columns that have no null values:

    select substring(x, '[^(,]+') as column_name
from (
select *
from (select json_each(row_to_json(mytable))::text as x from mytable))
group by 1
having count(*) filter (where x like '%,null)' = true) = 0

    To list columns that have some null values:

    select distinct substring(x, '[^(,]+') as column_name
from (
  select *
  from (select json_each(row_to_json(mytable))::text as x from mytable)
)
where x like '%,null)'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search