Thread

default timestamp of 'now'

Michael Richards <michael@fastmail.ca> — 2000-08-18T17:35:24Z

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