RE: [HACKERS] datetime default 'now' broken? - Mailing list pgsql-hackers

From Jackson, DeJuan
Subject RE: [HACKERS] datetime default 'now' broken?
Date
Msg-id F10BB1FAF801D111829B0060971D839F18D313@dal_cps.cpsgroup.com
Whole thread Raw
List 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:

Previous
From: maycock@intelliquest.com
Date:
Subject: Problem with GRANT permissions
Next
From: Peter T Mount
Date:
Subject: Re: [QUESTIONS] Re: [HACKERS] text should be a blob field