Thread: Issue with default values and Rule

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

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

On 02/27/2014 08:51 AM, Dev Kumkar wrote:
> On Thu, Feb 27, 2014 at 9:32 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>     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.
>
>
> Yes, there you are. Changing the order in coalesce will not solve the
> issue here. As update will also have some real non-null NEW values.
> Actually internally when the rule gets called then default value is
> being in this case.
> However note that 'null' is being explicitly inserted then default value
> is not picked by postgres engine internally and data is persisted correctly:

That works because you said NULL is a valid value for the column. If you
had specified NOT NULL then you would get an error about violating the
NOT NULL constraint. Since you have said NULL is a valid value and you
actually specified it in the INSERT the following applies:

http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

DEFAULT default_expr
  ....

     The default expression will be used in any insert operation that
does not specify a value for the column. If there is no default for a
column, then the default is null.


>
>        create table my_test (id int, col_1 timestamp null, col_2
> varchar(12) null default 'Initial');
>
>        insert into my_test(id,col_1,col_2) values(1, now() at time zone
> 'UTC','ShowMe');
>        select * from my_test;
>        Results:
>        1,2014-02-27 16:34:23.464088,ShowMe
>
>        insert into my_test(id,col_1,col_2) values(1, now() at time zone
> 'UTC',null);
>        select * from my_test;
>        Results:
> 1,2014-02-27 16:35:49.206237,ShowMe
>
> Agree trigger might give more control here. But still suggest any
> breakthrough here.

>
> Regards...