On 02/27/2014 08:51 AM, Dev Kumkar wrote:
On Thu, Feb 27, 2014 at 9:32 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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...
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
