On Thu, Feb 27, 2014 at 9:32 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
Realized my previous answer:
col_2 = coalesce(my_test.col_2, NEW.col_2)
works for the particular situation you described, but not for the general case. It would not allow an update of a field where a NON NULL value exists and you want to change that value, as the existing field would trump the new one.
Yes, there you are. Changing the order in coalesce will not solve the issue here. As update will also have some real non-null NEW values.
Actually internally when the rule gets called then default value is being in this case.
However note that 'null' is being explicitly inserted then default value is not picked by postgres engine internally and data is persisted correctly:
create table my_test (id int, col_1 timestamp null, col_2 varchar(12) null default 'Initial');
insert into my_test(id,col_1,col_2) values(1, now() at time zone 'UTC','ShowMe');
select * from my_test;
Results:
1,2014-02-27 16:34:23.464088,ShowMe
insert into my_test(id,col_1,col_2) values(1, now() at time zone 'UTC',null);
select * from my_test;
create table my_test (id int, col_1 timestamp null, col_2 varchar(12) null default 'Initial');
insert into my_test(id,col_1,col_2) values(1, now() at time zone 'UTC','ShowMe');
select * from my_test;
Results:
1,2014-02-27 16:34:23.464088,ShowMe
insert into my_test(id,col_1,col_2) values(1, now() at time zone 'UTC',null);
select * from my_test;
Results:
1,2014-02-27 16:35:49.206237,ShowMe
Agree trigger might give more control here. But still suggest any breakthrough here.
Regards...
