Issue with default values and Rule

From: Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Issue with default values and Rule
Date: 2014-02-27 11:45:34
Message-ID: CALSLE1Oc8wjvyNoEf-agT3M1yCKjuBm8ku2qdkk7W6XYsofyuw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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) 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?

Regards...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2014-02-27 14:43:29 Re: documentation bug
Previous Message James Harper 2014-02-27 10:47:44 documentation bug