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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
