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

Tom Lane wrote:

>
> > [ 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'.
>

Tom! I tested your method of creating table with
create table ww (aa int4, bb timestamp default text 'now'),
and it didn't work either! (BTW, this is exactly the way docs suggest
doing it.) See? I promised to deliver a real bug and I did it! :)))

Yes, docs mumble something about 'cacheable' and 'non-cacheable'
functions, but it is not clear to me how Postgres discerns them.

It is complete puzzle to me why 'USER' is cacheable and 'CURRENT_TIMESTAMP'
is not. This distinction, I think, should be made clearer. Maybe in
the sensible form of two-column table in the docs. :) One column is
function name, the other is 'cacheability'.

--
Leon.

Added to FAQ as:

4.22) How do I create a column that will default to the current time?

The tempation is to do:

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

but this makes the column default to the time of table creation, not the
time of row insertion.
Instead do:

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

The casting of the value to text prevents the default value from being
computed at table
creation time, and delays it until insertion 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
>
>

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026