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

Mon, 16 Mar 1998 12:35:48 -0600
"Jackson, DeJuan" <djackson@cpsgroup.com>
RE: [HACKERS] datetime default 'now' broken?