Use default value instead of inserted null

Question

I have a table definition in Postgres that use now() for timestamp and current_user() for auditing.

date_created date NULL DEFAULT now(),
edited_by varchar NULL DEFAULT "current_user"(),

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.

INSERT INTO test_table
    (notes, date, edited_by)
    VALUES('test', null, null);

Is there a way to still use these default values somehow? Or is the solution to write a trigger function to solve it?

Answer 1

A trigger is the right workaround if your problem can't be fixed.

You stated:

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 NULL values).

If you have to add that trigger after all, make it efficient:

CREATE OR REPLACE FUNCTION force_user_defaults()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.date_created := now();
   NEW.edited_by    := current_user();
   RETURN NEW;
END
$func$;

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.

About EXECUTE FUNCTION:

• Trigger uses a procedure or a function?

Answer 2

create table test_table (
notes varchar,
date_created date NOT NULL DEFAULT now(),
edited_by varchar NOT NULL DEFAULT "current_user"()
);

INSERT INTO test_table
    (notes)
    VALUES('test');