default timestamp of 'now'

From: "Michael Richards" <michael(at)fastmail(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Subject: default timestamp of 'now'
Date: 2000-08-18 17:35:24
Message-ID: 399D73DC.000017.87541@frodo.searchcanada.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I'm using postgres 7.0.2 on a FreeBSD 4.0-STABLE system. Compiled
with gcc version 2.95.2 19991024 (release).

When I create a table with a default timestamp of 'now' it works as
expected. When I create it with the timestamp 'now'::timestamp
explicitly specifying the type, it evaluates this to the actual time
I create the table:
> create table test (a int4,test timestamp default 'now');
CREATE
> create table test1 (a int4,test timestamp default 'now'::timestamp);
CREATE
> \d test
Table "test"
Attribute | Type | Modifier
-----------+-----------+---------------
a | integer |
test | timestamp | default 'now'

fastmail=> \d test1
Table "test1"
Attribute| Type | Modifier
---------+-----------+-----------------------------------------------
a | integer |
test | timestamp | default '2000-08-18 13:24:16-04'::"timestamp"

My guess is that it's performing the conversion and interpretation of
the default expression when the table is created. This is a good
thing if you have a constant expression, ie default 1+2. However, an
expression that is not constant should not be evaluated until the
insertion is performed.

Something like default 'now'::timestamp-'1 year'::timespan is odd
because it evaluates the 'now' but not the entire function:

default ('2000-08-18 13:28:41-04'::"timestamp" - '1 year
00:00'::"interval")

A simple addition:
create table test3 (a int4 default 1+2,test timestamp);

Yields an already evaluated expression:
a | integer | default 3

-Michael
_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2000-08-18 18:23:39 Re: INSERT/SELECT with ORDER BY
Previous Message Bob Rivoir 2000-08-18 02:32:41 INSERT/SELECT with ORDER BY