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

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