Re: BUG #9088: default are not working

Christian Kruse-4 wrote
> Hi,
>
> On 03/02/14 18:38, Patrick Lademan wrote:
>> -- Test Case
>> drop table default_test;
>>
>> create table default_test
>> (
>>   userId   varchar(20)   default 'test' not null,
>>   date1    timestamp     default now() not null,
>>   date2    timestamp     default current_timestamp not null,
>>   date3    timestamp     default localtimestamp not null
>> );
>>
>> insert into default_test
>> ( userId, date1, date2, date3 )
>> values
>> ( null, null, null, null );
>>
>> select * from default_test;
>>
>> ERROR:  null value in column "userid" violates not-null constraint
>> DETAIL:  Failing row contains (null, null, null, null).
>> ********** Error **********
>>
>> ERROR: null value in column "userid" violates not-null constraint
>> SQL state: 23502
>> Detail: Failing row contains (null, null, null, null).
>
> This won't work and didn't work in earlier versions. When you
> explicitly set the columns to NULL the default values don't apply. You
> have to leave them out or to explicitly request the default values:

I'll admit I've occasionally wished for the ability for the system to
convert any supplied literal NULL into the default but that is not how it
works.  Specifying NULL explicitly means that is what you want and if the
column is constrained to be non-NULL the system will be unable to fulfill
your request and throw an error.

You may be able to use a BEFORE INSERT trigger to check for the presence of
NULL in the column in question and manually issue the "DEFAULT" expression
yourself (I do not believe the trigger can say "use the default" to set a
value) though I'm not sure on the specifics as to when the NULL check
occurs.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-9088-default-are-not-working-tp5790410p5790511.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.