> 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