A trigger is the right workaround if your problem can't be fixed.
The problem is that the server produces queries with explicit null
values if not given other instructions. I have no way of changing the
query being sent from the server to pg.
The right solution would be to "give other instructions". Or replace that "server" of yours with decent software if it's incapable (which I doubt).
Additionally, define both columns
NOT NULL to raise an exception if the error should still happen (unless you need to allow
If you have to add that trigger after all, make it efficient:
CREATE OR REPLACE FUNCTION force_user_defaults()
LANGUAGE plpgsql AS
NEW.date_created := now();
NEW.edited_by := current_user();
CREATE TRIGGER test_table_before_insert
BEFORE INSERT ON test_table
FOR EACH ROW
WHEN (NEW.date_created IS NULL OR NEW.edited_by IS NULL) -- !
EXECUTE FUNCTION force_user_defaults();
The point being to only even call the trigger function if violating
NULL values are passed. Note then
WHEN clause in
CREATE TRIGGER - which only makes sense if those
NULL values are the exception rather than the rule.