Re: Setting a DEFAULT when NULL is inserted

Am 12.07.2017 um 12:32 schrieb Andreas Kretschmer:
> i would suggest a TRIGGER on Insert.

as a short example:

test=# CREATE TABLE my_table
(
id integer,
insertion_datetime timestamp DEFAULT now()
);
CREATE TABLE
test=*# create or replace function set_timestamp() returns trigger as
$$begin new.insertion_datetime := now(); return new; end; $$language
plpgsql;
CREATE FUNCTION
test=*# create trigger trg_set_timestamp before insert on my_table for
each row when (new.insertion_datetime is null) execute procedure
set_timestamp();
CREATE TRIGGER
test=*# commit;
COMMIT
test=# insert into my_table (id) values (1);
INSERT 0 1
test=*# commit;
COMMIT
test=# insert into my_table (id, insertion_datetime) values (2, NULL);
INSERT 0 1
test=*# commit;
COMMIT
test=# select * from my_table ;
id | insertion_datetime
----+----------------------------
1 | 2017-07-12 15:44:57.946964
2 | 2017-07-12 15:45:05.083043
(2 Zeilen)

test=*#

note that the trigger fires only if the new.insertion_datetime is null
(a so called conditional trigger)

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com