0

I'd like to extract some basic statistics about how tables are populated in a given PostgreSQL 16 schema, such as the absolute number and percentage of null values for each column in all tables in that schema.

Currently, I'm fetching/computing those values using Python/psycopg2, but I'm wondering if I can store some kind of function inside PostgreSQL itself that I can call each time I want to grab those statistics?

6
  • COUNT(someColumn) tells you how many non-nulls, so COUNT(*) - COUNT(someColumn) tells you how many nulls, and (COUNT(*) - COUNT(someColumn)) * 100.0 / COUNT(*) tells you the percentage. What exactly is your question? Do you know which tables and columns you want, or is it dynamic? Mar 10 at 1:10
  • 1
    If approximate results are ok, you could query pg_stats. Mar 11 at 7:04
  • That could be an idea indeed, but what exactly do you mean by "approximate"?
    – s.k
    Mar 11 at 9:01
  • @s.k: pg_stats has statistics computed from a random sample, not exact counts. Mar 11 at 9:05
  • Naive question: is it then possible to purposefully compute the same statistics on the entire dataset?
    – s.k
    Mar 11 at 9:15

1 Answer 1

1

... the absolute number and percentage of null values for each column in all tables

Test setup

CREATE TABLE test (col1 int, col2 text, col3 date);
INSERT INTO test VALUES
  (1, 'text', '2024-1-1')
, (2, 'text', null)
, (3, null  , null)
, (4, 'text', null)
, (5, null  , null)
;

Simple, naive, repetitive & slow

Basically, you want this kind of output for all columns of any give table:

SELECT 'col1' AS column
     , count(*) FILTER (WHERE col1 IS NULL) AS has_null
     , round(count(*) FILTER (WHERE col1 IS NULL) / (count(*) / 100.0)) AS pct_null
FROM   test t

UNION ALL
SELECT 'col2' AS column
     , count(*) FILTER (WHERE col2 IS NULL) AS has_null
     , round(count(*) FILTER (WHERE col2 IS NULL) / (count(*) / 100.0)) AS pct_null
FROM   test t

UNION ALL
SELECT 'col3' AS column
     , count(*) FILTER (WHERE col3 IS NULL) AS has_null
     , round(count(*) FILTER (WHERE col3 IS NULL) / (count(*) / 100.0)) AS pct_null
FROM   test t;
column has_null pct_null
col1 0 0
col2 2 40
col3 4 80

Smart, but not dynamic, yet

Besides being much faster, this adds more data and defends against division by zero:

SELECT c.col AS column
     , c.null_ct AS has_null
     , round(c.null_ct / ct_div) AS pct_null
FROM  (
   SELECT count(*) AS ct
        , NULLIF(count(*), 0) / 100.0 AS ct_div
        , '{"* (total_row_count)","* (rows_with_any_null)",col1,col2,col3}'::text[] AS cols   
        , ARRAY [count(*)
               , count(*) FILTER (WHERE NOT (t.*) IS NOT NULL)
               , count(*) FILTER (WHERE col1 IS NULL)
               , count(*) FILTER (WHERE col2 IS NULL)
               , count(*) FILTER (WHERE col3 IS NULL)
                 -- more?
                ] AS nulls
   FROM   test t
   ) sub, unnest(cols, nulls) AS c(col, null_ct);
column_name has_null pct_null
* (total_row_count) 5 100
* (rows_with_any_null) 4 80
col1 0 0
col2 2 40
col3 4 80

About WHERE NOT (t.*) IS NOT NULL:

Related:

Full automation

What you really want: a fully automated function executing a dynamic query. With bells and whistles, and optimized performance. Also defends against possible SQL injection properly.

The function takes one or two arguments:

  1. _tbl regclass ... Table name as (optinally schema-qalified) string literal or as OID type regclass
  2. _show_table_stats bool ... Show additional table stats? Optional, default false.
CREATE OR REPLACE FUNCTION public.f_null_ratio(_tbl regclass, _show_table_stats bool = false)
  RETURNS TABLE(column_name text, has_null bigint, pct_null numeric)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _cols text[];
   _nulls_sql text;
   _sql text;
BEGIN
   SELECT INTO _cols, _sql
        CASE WHEN _show_table_stats
               THEN '{* (total_row_count), * (rows_with_any_null)}'::text[] || array_agg(col)
               ELSE array_agg(col)
           END  -- AS cols
        , format(
/* dynamic query string */
$q$SELECT c.col  -- AS column
     , c.null_ct  -- AS has_null
     , round(c.null_ct / ct_div)  -- AS pct_null
FROM  (
   SELECT count(*) AS ct
        , NULLIF(count(*), 0) / 100.0 AS ct_div
        , $1 AS cols   
        , %1$s AS nulls
   FROM   %2$s t
   ) sub, unnest(cols, nulls) AS c(col, null_ct)$q$
/* dynamic query string */
               , CASE WHEN _show_table_stats
                      THEN 'ARRAY [count(*), count(*) FILTER (WHERE NOT (t.*) IS NOT NULL), '
                      ELSE 'ARRAY [' END
              || string_agg('count(*) FILTER (WHERE ' || a.col || ' IS NULL)', ', ') || ']'  -- %1$s 
               , _tbl                                                                        -- %2$s 
                )  -- AS sql
   FROM  (
      SELECT a.attrelid, quote_ident(a.attname) AS col
      FROM   pg_catalog.pg_attribute a
      WHERE  a.attrelid = _tbl
      AND    NOT a.attisdropped  -- no dropped (dead) columns
      AND    a.attnum > 0        -- no system columns
      ORDER  BY a.attnum
      ) a;

   -- RAISE NOTICE E'%\n%', _sql, _cols;  -- debug?
   RETURN QUERY EXECUTE _sql
   USING _cols;
END
$func$;

Example calls:

SELECT * FROM f_null_ratio('public.test'::regclass);
SELECT * FROM f_null_ratio('test');
SELECT * FROM f_null_ratio('_tbl => public.test', _show_table_stats => true);
SELECT * FROM f_null_ratio('test', true);

fiddle

Now you just call this function for every table of interest.
You need to know your way around PL/pgSQL to handle this properly.

About the use of %s for the table name as type regclass in format():

Related:

7
  • Big big thanks! This kind of answer is of public interest!
    – s.k
    Mar 10 at 9:52
  • Impressive answer (as usual). I'm just wondering if it might be worth mentioning to the OP that one can set stats on a column by column basis. Delving into the source code is (currently) above my pay grade, but I'm just wondering that if there are columns that the OP may be more or less interested in, setting stats on a col-by-col basis might be less onerous than on a table-wide one? I found here and here helpful.
    – Vérace
    Mar 12 at 6:32
  • @Vérace: Increasing the statistics target with SET STATISTICS can be done per column. Yes, may be of relevance. See: stackoverflow.com/a/15034896/939860 But that's just a gradual improvement for estimates in pg_stats (that comes at a price). BTW, you posted the same link twice. A typo, I assume. Mar 13 at 0:16
  • 1
    Yes, sorry, 2nd link was meant to be ALTER TABLE... - SET STATISTICS..., leads on to section 14.2 which leads to an entire chapter. I hope you don't mind me having the temerity to suggest this, but you, it appears to me, want your answers to cover all the bases, so I thought it might be a useful point to note? In any case, keep up the good work - many of your posts are nothing short of awe-inspiring!
    – Vérace
    Mar 13 at 11:10
  • @ErwinBrandstetter you are a god amongst men! Managed to follow f_null_ratio (eventually), but may I ask - why %s rather than %L in the subquery within the dynamic SQL string? I'm trying to modify this to a) get these stats for all tables in a given schema, b) consider empty strings as NULLs for text fields and c) exclude the table-level stats rows you're generating.
    – enjayaitch
    Mar 14 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.