On 02/27/2014 03:45 AM, Dev Kumkar wrote:
>
> Am facing issues with using UPSERT rule having default value columns.
> Here is the code:
>
> create table my_test (id int, col_1 timestamp null, col_2 varchar(12)
> null default 'Initial');
>
> CREATE OR REPLACE RULE RULE_my_test AS ON INSERT TO my_test WHERE
> EXISTS (SELECT 1 from my_test WHERE id = NEW.id )
> DO INSTEAD
> UPDATE my_test SET col_1 = coalesce(NEW.col_1,my_test.col_1),col_2
> = coalesce(NEW.col_2,my_test.col_2),id = coalesce(NEW.id,my_test.id
> <http://my_test.id>) WHERE id = NEW.id;
>
> insert into my_test(id,col_1,col_2) values(1, now() at time zone 'UTC',
> 'NewValue');
> select * from my_test;
>
> Results:
> 1, 2014-02-27 10:19:20.144141,NewValue
>
> -- Lets not insert col_2 here
> insert into my_test(id,col_1) values(1, now() at time zone 'UTC');
> select * from my_test;
>
> Results:
> 1,2014-02-27 10:20:06.573496,Initial
>
>
> col_2 value becomes the default value i.e. 'Initial'
> So rule picks up default value when column is not in the insert list.
> Can the rule here modified to not pick default value of column and do
> the update stuff correctly?
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.
>
> Regards...
--
Adrian Klaver
adrian.klaver@aklaver.com