Re: Issue with default values and Rule

On Thu, Feb 27, 2014 at 9:32 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>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;
Results:
1,2014-02-27 16:35:49.206237,ShowMe

Agree trigger might give more control here. But still suggest any
breakthrough here.

Regards...