Trigger to prevent introducing new NULL values to a list of specified columns. I'm using this because I have an 8mil-row, 100GB (with indexes) table that I can't afford to lock to add a NOT NULL constraint. This allows me to start enforcing data now, and back-fill the values without downtime. Later, when I can schedule downtime, another constrai…

no_new_nulls_trigger.sql

/*
 * Prevent INSERTs or UPDATEs that introduce NULLs. Useful for when the
 * table is too big to add NOT NULL constraints without downtime that
 * can't currently be performed.
 *
 * Use like:
 *
 *    CREATE TRIGGER <name>
 *      AFTER INSERT OR UPDATE 
 *      ON <table>
 *      FOR EACH ROW 
 *        EXECUTE PROCEDURE no_new_nulls('col1', 'col2', '...');
 *
 */
CREATE OR REPLACE FUNCTION no_new_nulls()
RETURNS TRIGGER AS $$
  DECLARE
    _col text;
    _old_null bool;
    _new_null bool;
  BEGIN
    FOREACH _col IN ARRAY TG_ARGV LOOP
       EXECUTE 'SELECT $1.' || quote_ident(_col) || ' IS NULL'
       USING NEW
       INTO _new_null;

       IF (TG_OP = 'UPDATE') THEN
         EXECUTE 'SELECT $1.' || quote_ident(_col) || ' IS NULL'
         USING OLD
         INTO _old_null;

         IF (_new_null AND NOT _old_null) THEN
           RAISE EXCEPTION 'null value in column "%" violates not-null constraint', _col;
         ELSE
           RETURN NEW;
         END IF;
       ELSIF (TG_OP = 'INSERT') THEN
         IF (_new_null) THEN
           RAISE EXCEPTION 'null value in column "%" violates not-null constraint', _col;
         ELSE
           RETURN NEW;
         END IF;
       END IF;

       RETURN NEW;
    END LOOP;
    RETURN res;
  END
$$ LANGUAGE plpgsql;