With current CVS, I did
regression=# create table foo (f1 date default current_date,
regression(# f2 time default current_time,
regression(# f3 timestamp default current_timestamp);
CREATE
regression=# \d foo
Table "foo"
Column | Type | Modifiers
--------+--------------------------+----------------------------------
f1 | date | default date('now'::text)
f2 | time | default "time"('now'::text)
f3 | timestamp with time zone | default "timestamp"('now'::text)
regression=# insert into foo default values;
INSERT 139633 1
regression=# insert into foo default values;
INSERT 139634 1
regression=# select * from foo;
f1 | f2 | f3
------------+----------+------------------------
2001-10-03 | 13:15:37 | 2001-10-03 13:15:37-04
2001-10-03 | 13:15:49 | 2001-10-03 13:15:50-04
(2 rows)
It's fairly disconcerting that f2 and f3 don't agree, wouldn't you say?
Further experimentation shows that it happens about half the time, with
the timestamp always one second ahead of the time when they differ.
I infer that the new sub-second-resolution transaction timestamp is
being correctly rounded when stored as a timestamp, but is truncated not
rounded when stored as a time. Type timetz shows the same misbehavior.
Not sure where to look for this ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
