Issue with default values and Rule

Поиск
Список
Период
Сортировка
От Dev Kumkar
Тема Issue with default values and Rule
Дата
Msg-id CALSLE1Oc8wjvyNoEf-agT3M1yCKjuBm8ku2qdkk7W6XYsofyuw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Issue with default values and Rule  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Issue with default values and Rule  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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...

В списке pgsql-general по дате отправления:

Предыдущее
От: James Harper
Дата:
Сообщение: documentation bug
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: documentation bug