patternsqlMinor

Identify columns that allow NULL but don't contain NULL

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

columnscontainnullidentifybutallowthatdon

Problem

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.

Solution

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
$;

Code Snippets

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
$$;

Context

StackExchange Database Administrators Q#145953, answer score: 4

Revisions (0)

No revisions yet.