Re: Issue with default values and Rule

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...