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

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