Обсуждение: BUG #9088: default are not working
The following bug has been logged on the website: Bug reference: 9088 Logged by: Patrick Lademan Email address: plademan@comcast.net PostgreSQL version: 9.3.1 Operating system: OS X 10.9.1 (13B42) Description: 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', 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;
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.
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
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: > 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 >
Hi,
On 03/02/14 18:38, Patrick Lademan wrote:
> -- Test Case
> drop table default_test;
>=20
> 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
> );
>=20
> insert into default_test
> ( userId, date1, date2, date3 )
> values
> ( null, null, null, null );
>=20
> select * from default_test;
>=20
> ERROR: null value in column "userid" violates not-null constraint
> DETAIL: Failing row contains (null, null, null, null).
> ********** Error **********
>=20
> 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,
--=20
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.