Skip site navigation (1) Skip section navigation (2)

RE: [HACKERS] datetime default 'now' broken?

From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
To: "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu>, Brett McCormickS <brett(at)abraxas(dot)scene(dot)com>
Cc: pgsql-hackers(at)hub(dot)org
Subject: RE: [HACKERS] datetime default 'now' broken?
Date: 1998-03-16 18:35:48
Message-ID: F10BB1FAF801D111829B0060971D839F18D313@dal_cps.cpsgroup.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> > If I create a table with a datetime field with a default of 'now', 
> > every insert the value is the time of table creation instead of the 
> > time of insert, which is how it behaved in previous releases (I
> think 
> > this was even documented).
> 
> I can't recall it ever working that way, though before we discovered
> that it didn't we all assumed that it _did_ work that way :)
> 
> The workaround is to define it as
> 
>   ... default datetime('now'::text)
> 
> which forces the string to be evaluated at runtime. The SQL symbol
> CURRENT_TIMESTAMP also misbehaves in "default" clauses, and I'm
> considering changing it a bit to get around the problem.
> 
>                  - Tom
> 
test=> drop table tmp1;
DROP
test=> create table tmp1 (c1 int4 primary key, estDate timestamp default
timestamp(now()) not null);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index tmp1_pkey
for table tmp1
CREATE
test=> insert into tmp1(c1) values (0);
INSERT 19653 1
test=> insert into tmp1(c1) select max(c1)+1 from tmp1;
INSERT 19654 1
test=> insert into tmp1(c1) select max(c1)+1 from tmp1;
INSERT 19655 1
test=> insert into tmp1(c1) select max(c1)+1 from tmp1;
INSERT 19656 1
test=> select * from tmp1;
c1|estdate               
--+----------------------
 0|1998-03-16 13:26:32-05
 1|1998-03-16 13:26:39-05
 2|1998-03-16 13:26:40-05
 3|1998-03-16 13:26:44-05
(4 rows)

timestamp(now()) - works for me.
datetime(now()) - also works for datetimes.
		-DEJ

pgsql-hackers by date

Next:From: Peter T MountDate: 1998-03-16 18:53:17
Subject: Re: [QUESTIONS] Re: [HACKERS] text should be a blob field
Previous:From: Thomas G. LockhartDate: 1998-03-16 17:22:20
Subject: Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group