BUG #9088: default are not working

plademan@comcast.net wrote on 03.02.2014 23:23:> create table default_test

(
userId varchar(20) default 'test',
date1 timestamp default now(),
date2 timestamp default current_timestamp,
date3 timestamp default localtimestamp
);

insert into default_test
( userId, date1, date2, date3 )
values
( null, null, null, null );

select * from default_test;

You explicitely stated that you want to put NULL into those columns, therefor the default value does not apply.

The default value is only used if the colum is not listed at all in the INSERT statement.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 2/3/14, 11:23 PM, plademan@comcast.net wrote:

It appears that default values are no longer working in 9.3.1.

insert into default_test
( userId, date1, date2, date3 )
values
( null, null, null, null );

select * from default_test;

This wouldn't have worked in previous versions either. You want:

INSERT INTO default_test (userid, date1, date2, date) VALUES
(DEFAULT, DEFAULT, DEFAULT, DEFAULT)

or perhaps

INSERT INTO default_test DEFAULT VALUES;

Regards,
Marko Tiikkaja

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I am sorry, I tried to break it down to the simplest test. It was on the
not null that it was failing. When I reconstructed the test in the
database, I went down the path of thinking it was a timestamp issue not a
default issue.

Here is the issue again.
-- It appears that default values are no longer working in 9.3.1.

-- This is the version that I am running:
-- "PostgreSQL 9.3.1 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
5658) (LLVM build 2336.9.00), 64-bit"

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

On Mon, Feb 3, 2014 at 5:31 PM, Marko Tiikkaja <marko@joh.to> 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:

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

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs