Re: [BUGS] 'Default' troubles again. This time with time :)))

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
> Added to FAQ as:
> 4.22) How do I create a column that will default to the current time?

> Instead do:
> create table test (x int, modtime timestamp default text 'now');

Actually, Leon's complaint was specifically that that doesn't work!
Try it with current sources:

regression=> create table test (x int, modtime timestamp default text 'now');
CREATE
regression=> insert into test values (1);
INSERT 545995 1
regression=> insert into test values (2);
INSERT 545996 1
regression=> insert into test values (3);
INSERT 545997 1
regression=> select * from test;
x|modtime
-+----------------------
1|1999-09-28 09:53:03-04
2|1999-09-28 09:53:03-04
3|1999-09-28 09:53:03-04
(3 rows)

(and no, I don't type that fast :-)) It does work for a datetime column,
but not for type timestamp. I looked into this a while back and found
that it's caused by StoreAttrDefault's roundabout way of making defaults
plus lack of a full set of pg_proc entries for type timestamp --- the
conversion ends up happening anyway when the default expression is
parsed a second time.

I think the FAQ ought to recommend

create table test (x int, modtime timestamp default now());

which does work as desired for both data types.

One of the end results of this constant folding + proiscachable work
should be that the whole problem goes away, because the parser will
be aware that text-to-datetime is a noncachable function and will not
try to simplify 'now'::datetime (or ::timestamp) at parse time.
But until everyone is using 6.6 or later, we had better recommend
workarounds like the above.

regards, tom lane