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?
An immediate solution would be to change:
col_2 = coalesce(NEW.col_2,my_test.col_2)
to
col_2 = coalesce(my_test.col_2, NEW.col_2)
COALESCE returns the first non null value, so in the first case you
would be replacing the existing value with default each time.
The longer term solution would be to use triggers instead of rules. The
logic of what happens in a trigger is a lot easier to understand. In
that vein I offer the following on what happens in an INSERT rule:
http://www.postgresql.org/docs/9.3/interactive/rules-update.html
>
> Regards...
--
Adrian Klaver
adrian.klaver@aklaver.com