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

Leon <leon(at)udmnet(dot)ru> writes:
> bdb=> create table dd (aa int4, gg timestamp default 'now');

> [ default value doesn't change over time ]

This oughta be in the FAQ I think ...

When you write a simple constant default, it gets coerced to the target
data type before the default information is stored. So, what you
effectively did was to create table dd with a default value for gg
of the time at which you executed 'create table'.

To get the effect you want, you need the text string 'now' to be
converted to timestamp type *at the time an INSERT uses the default*.
Any expression more complex than a simple constant will do, but the
usual idiom for this task is:

create table dd (aa int4, gg timestamp default text 'now');

> Seems there is lot'a trouble with default values :)

There *are* some known bugs with defaults: if you write a default
for a fixed-length character field (ie, char(n)) it's a good idea
to make sure the default value is exactly n characters. 6.4 messes
up badly with a wrong-length default. 6.5 cures the simplest case
(constant default value) and I'm currently working on a more general
fix for 6.6. That's got nothing to do with the timestamp question,
though.

regards, tom lane