The following bug has been logged online:
Bug reference: 1598
Logged by: Nicolas HAHN
Email address: [EMAIL PROTECTED]
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;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
