Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-09-28T14:03:36Z

   Bruce Momjian <maillist@candle.pha.pa.us> writes:
   > Added to FAQ as:
   > 4.22) How do I create a column that will default to the current time?
   
   > Instead do: 
   >         create table test (x int, modtime timestamp default text 'now');
   
   Actually, Leon's complaint was specifically that that doesn't work!
   Try it with current sources:
   
   regression=> create table test (x int, modtime timestamp default text 'now');
   CREATE
   regression=> insert into test values (1);
   INSERT 545995 1
   regression=> insert into test values (2);
   INSERT 545996 1
   regression=> insert into test values (3);
   INSERT 545997 1
   regression=> select * from test;
   x|modtime
   -+----------------------
   1|1999-09-28 09:53:03-04
   2|1999-09-28 09:53:03-04
   3|1999-09-28 09:53:03-04
   (3 rows)
   
   (and no, I don't type that fast :-)) It does work for a datetime column,
   but not for type timestamp.  I looked into this a while back and found
   that it's caused by StoreAttrDefault's roundabout way of making defaults
   plus lack of a full set of pg_proc entries for type timestamp --- the
   conversion ends up happening anyway when the default expression is
   parsed a second time.
   
   I think the FAQ ought to recommend
   
   	create table test (x int, modtime timestamp default now());
   
   which does work as desired for both data types.
   
   One of the end results of this constant folding + proiscachable work
   should be that the whole problem goes away, because the parser will
   be aware that text-to-datetime is a noncachable function and will not
   try to simplify 'now'::datetime (or ::timestamp) at parse time.
   But until everyone is using 6.6 or later, we had better recommend
   workarounds like the above.
   
   			regards, tom lane
   
   

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

   Bruce Momjian <maillist@candle.pha.pa.us> — 1999-09-28T14:49:10Z

   > Bruce Momjian <maillist@candle.pha.pa.us> writes:
   > > Added to FAQ as:
   > > 4.22) How do I create a column that will default to the current time?
   > 
   > > Instead do: 
   > >         create table test (x int, modtime timestamp default text 'now');
   > 
   > Actually, Leon's complaint was specifically that that doesn't work!
   > Try it with current sources:
   > 
   > regression=> create table test (x int, modtime timestamp default text 'now');
   > CREATE
   > regression=> insert into test values (1);
   > INSERT 545995 1
   > regression=> insert into test values (2);
   > INSERT 545996 1
   > regression=> insert into test values (3);
   > INSERT 545997 1
   > regression=> select * from test;
   > x|modtime
   > -+----------------------
   > 1|1999-09-28 09:53:03-04
   > 2|1999-09-28 09:53:03-04
   > 3|1999-09-28 09:53:03-04
   > (3 rows)
   > 
   > (and no, I don't type that fast :-)) It does work for a datetime column,
   > but not for type timestamp.  I looked into this a while back and found
   > that it's caused by StoreAttrDefault's roundabout way of making defaults
   > plus lack of a full set of pg_proc entries for type timestamp --- the
   > conversion ends up happening anyway when the default expression is
   > parsed a second time.
   > 
   > I think the FAQ ought to recommend
   > 
   > 	create table test (x int, modtime timestamp default now());
   > 
   > which does work as desired for both data types.
   > 
   > One of the end results of this constant folding + proiscachable work
   > should be that the whole problem goes away, because the parser will
   > be aware that text-to-datetime is a noncachable function and will not
   > try to simplify 'now'::datetime (or ::timestamp) at parse time.
   > But until everyone is using 6.6 or later, we had better recommend
   > workarounds like the above.
   
   New text is:
   
   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 now() );
   
   The calling of the function now() prevents the default value from being
   computed at table creation time, and delays it until insertion time. We
   believe this will not be a problem in post-6.5.* releases. 
   	
   
   
   -- 
     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