Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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…
/*
* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.