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

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

At 9:59 PM -0800 3/15/98, Thomas G. Lockhart wrote:

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

I'm running 6.1.1 and I *depend* on 'now' giving me the real now in an
insert. I guess I'm glad I never upgraded.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

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

I'm running 6.1.1 and I *depend* on 'now' giving me the real now in an
insert. I guess I'm glad I never upgraded.

Hi Henry. You're missing a lot by not upgrading. DEFAULT clauses on
tables which Brett was asking about aren't even available pre-v6.2. The
'now' behavior in default clauses was an unexpected side-effect of the
optimizer, which evaluates things which look like constants before
execution.

Give me a call during the day at x47797 if you have any other
reservations about upgrading...

- Tom