Re: BUG #9088: default are not working

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:

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
(DEFAULT, DEFAULT, DEFAULT, DEFAULT);
insert into default_test (userId) values ('x');
select * from default_test;
userid | date1 | date2 | date3
--------+----------------------------+----------------------------+----------------------------
test | 2014-02-04 09:10:28.587693 | 2014-02-04 09:10:28.587693 | 2014-02-04 09:10:28.587693
x | 2014-02-04 09:11:05.15543 | 2014-02-04 09:11:05.15543 | 2014-02-04 09:11:05.15543
(2 rows)

Maybe you are mixing things up with MySQL, where NULL values trigger
default values.

Best regards,

--
Christian Kruse http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services