Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2000-08-18 18:23:39
Subject: Re: INSERT/SELECT with ORDER BY
Previous:From: Bob RivoirDate: 2000-08-18 02:32:41
Subject: INSERT/SELECT with ORDER BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group