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

BUG #1598: using default 'now', or now() or CURRENT_TIMESTAMP when creating table does not work as expected

From: "Nicolas HAHN" <hahnn(at)x-itools(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1598: using default 'now', or now() or CURRENT_TIMESTAMP when creating table does not work as expected
Date: 2005-04-15 12:18:49
Message-ID: 20050415121849.6D71DF0D6C@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1598
Logged by:          Nicolas HAHN
Email address:      hahnn(at)x-itools(dot)com
PostgreSQL version: 7.4.7
Operating system:   Fedora Core 3
Description:        using default 'now', or now() or CURRENT_TIMESTAMP when
creating table does not work as expected
Details: 

I read the doc and saw that, to avoid the problem of using a default 'now'
when creating the table which makes each record to have the timestamp of
when the table has been created, default now() or default CURRENT_TIMESTAMP
must be used instead. But using this is exactly the same as using 'now': I
still get the same timestamp for each record I insert in the table.

Here is what I did and the results, in the 3 cases:

**********************
case 1: using default 'now', which works as expected according the doc:

BEGIN;
CREATE TABLE test (date timestamp not null default 'now', val varchar(64));
INSERT INTO test (val) values ('test 1 at time T');
########### Here I wait 1 minute #############
INSERT INTO test (val) values ('test 2 at time T+1 min');
SELECT * from test;
            date            |      val
----------------------------+------------------------
 2005-04-15 13:47:38.796248 | test 1 at time T
 2005-04-15 13:47:38.796248 | test 2 at time T+1 min
(2 rows)
ROLLBACK;


**********************
case 2: using default now(), which doesn't work as expected according the
doc:

BEGIN;
CREATE TABLE test (date timestamp not null default now(), val varchar(64));
INSERT INTO test (val) values ('test 1 at time T');
########### Here I wait 1 minute #############
INSERT INTO test (val) values ('test 2 at time T+1 min');
SELECT * from test;
            date            |      val
----------------------------+------------------------
 2005-04-15 13:50:53.419981 | test 1 at time T
 2005-04-15 13:50:53.419981 | test 2 at time T+1 min
(2 rows)
ROLLBACK;


**********************
case 3: using default CURRENT_TIMESTAMP, which doesn't work as expected
according the doc:

BEGIN;
CREATE TABLE test (date timestamp not null default CURRENT_TIMESTAMP, val
varchar(64));
INSERT INTO test (val) values ('test 1 at time T');
########### Here I wait 1 minute #############
INSERT INTO test (val) values ('test 2 at time T+1 min');
SELECT * from test;
            date            |      val
----------------------------+------------------------
 2005-04-15 13:54:17.136511 | test 1 at time T
 2005-04-15 13:54:17.136511 | test 2 at time T+1 min
(2 rows)
ROLLBACK;

Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2005-04-15 14:56:43
Subject: Re: BUG #1598: using default 'now', or now() or CURRENT_TIMESTAMP
Previous:From: Jan BehrensDate: 2005-04-14 20:10:39
Subject: BUG #1597: pg_dump --verbose ignores --disable-triggers

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