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

> 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