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@udmnet.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@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026