work_around_not_null.sql

/*

https://dba.stackexchange.com/questions/308114/use-default-value-instead-of-inserted-null/311501#311501

*/
BEGIN;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    t_id        int GENERATED by default as IDENTITY
    ,time_created timestamptz NOT NULL DEFAULT now()
    ,edited_by   text NOT NULL DEFAULT CURRENT_USER
    ,misc       text
);

CREATE OR REPLACE FUNCTION coerce_to_default()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    set search_path = public
    AS $FUNC$
BEGIN
    IF new.time_created IS NULL THEN
        NEW.time_created := now();
    END IF;

    IF new.edited_by IS NULL THEN
        NEW.edited_by := CURRENT_USER;
    END IF;

    RETURN new;
END
$FUNC$;

CREATE OR REPLACE TRIGGER trig_coerce_to_default
    BEFORE INSERT OR UPDATE ON test_table FOR EACH ROW
    WHEN (new.time_created  IS NULL OR new.edited_by IS NULL)
    EXECUTE FUNCTION coerce_to_default ();

COMMIT;

-- test time
insert into test_table(time_created, edited_by) 
        values(null,'test1')    RETURNING   *;

insert into test_table(time_created, edited_by) 
        values(now() - interval '10 days',NULL) RETURNING*;

insert into test_table(time_created, edited_by,misc) 
        values(now() - interval '10 days',NULL,'test_misc') RETURNING*;

update test_table set time_created = NULL where misc ='test_misc' RETURNING *;

--clean up.
DROP TABLE IF EXISTS test_table;