Identify columns that allow NULL but don't contain NULL

Question

I'm working with a legacy database that never had any FOREIGN KEYs defined and not very many NOT NULL constraints, even where they are obviously applicable. I've been manually adding these in where the migrations are cheap and the applicability is obvious.

However, I'd really like a systematic way to identify some appropriate targets instead of stumbling around.

I'm wondering if it's possible to write a query (as slow as it might be) to run on a snapshot of the database that would identify every table.column which is not NOT NULL but which doesn't have any (or perhaps has very few NULLs.

I saw https://stackoverflow.com/a/17688833/56690 which is kind of the inverse problem but I am not really familiar enough with this kind of meta-SQL to know how reasonable it is to accomplish what I'm hoping.

Answer 1

I adapted the query from the linked answer in my question and have something I think works, but I'd prefer if it returned a table instead of just raising notices. I may improve it later but if I don't and anybody else does later, I'll switch the accepted answer. Ditto for any obvious performance improvements (though I understand this is likely to just be slow).

DO $$
DECLARE
   rec      record;
  _notnull  boolean;
BEGIN
   FOR rec IN 
      SELECT format('SELECT COUNT(*) = 0 FROM %s WHERE %I IS NULL;'
                   , c.oid::regclass, a.attname) AS qry_to_run
            ,c.oid::regclass AS tbl
            ,a.attname       AS col
      FROM   pg_namespace n 
      JOIN   pg_class     c ON c.relnamespace = n.oid 
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname <> 'information_schema'
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system, temp, toast tbls
      AND    c.relkind = 'r'
      AND    a.attnum > 0
      AND    a.attnotnull = FALSE
      AND    a.attisdropped = FALSE
   LOOP
      EXECUTE rec.qry_to_run INTO _notnull;

      IF _notnull THEN
        RAISE NOTICE 'Table % has no NULLs in the NULLable field %'
                    , rec.tbl,rec.col;
      END IF;
   END LOOP;
END
$$;

Answer 2

Probably the simplest solution:

analyse;

select
  sc.nspname,
  rl.relname,
  ar.attname,
  st.stanullfrac
from
  pg_statistic as st
    join pg_class rl on(rl.oid = st.starelid)
      join pg_namespace sc on (sc.oid = rl.relnamespace)
    join pg_attribute as ar on (ar.attrelid = st.starelid and ar.attnum = st.staattnum)

where
  sc.nspname in ('public', 'foo') and
  not ar.attnotnull;