From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Leon <leon(at)udmnet(dot)ru>, bughunters <pgsql-bugs(at)postgreSQL(dot)org> |
Subject: | Re: [BUGS] 'Default' troubles again. This time with time :))) |
Date: | 1999-09-28 05:03:48 |
Message-ID: | 199909280503.BAA02136@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)udmnet(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Christof Petig | 1999-09-28 06:33:34 | Re: [BUGS] INDEX broken on NUMERIC type. |
Previous Message | Bruce Momjian | 1999-09-28 03:20:54 | Re: [BUGS] INDEX broken on NUMERIC type. |