4

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.

4

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
$$;
  • Just declare a proper function with the same body, except that you return the text from it instead of raising the notice. You can even replace the text with ALTER TABLE statements, which then you can just run. – dezso Aug 5 '16 at 8:27
  • @dezso ah that seems like a good approach. I won't ALTER TABLE directly because some of these tables run a bit hot and I'd want to evaluate whether the application writes any of these fields (even if very briefly) as NULL. The goal is to just rule out columns that definitely have NULLs so I don't waste my time on them. – Bo Jeanes Aug 5 '16 at 9:45
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;
  • 1
    In some cases, when NULLs are rare enough to not show up in the sample ANALYSE considers, this might lead to false positives. – dezso Aug 5 '16 at 8:51
  • @dezso You are right, statistic is not absolutely accurate. However, IMO, if nulls are rare enough, then the column should be considered as a candidate to making it as not null. – Abelisto Aug 5 '16 at 9:01
  • I don't have permission on the pg_statistic table in this environment so I'll have to load a snapshot of the database locally to test this, but in principle this sounds like a good approach. For my purposes, some false positives and false positives are acceptable anyway, as I'm just trying to focus my effort. – Bo Jeanes Aug 5 '16 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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