Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-07-18T14:27:31Z

   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
   
   

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

   Leon <leon@udmnet.ru> — 1999-07-18T16:29:31Z

   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.
   
   
   

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

   Bruce Momjian <maillist@candle.pha.pa.us> — 1999-09-28T05:03:48Z

   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