Received: from propertykey.com (IDENT:root@propertykey.com [206.196.37.193]) by hub.org (8.10.1/8.10.1) with ESMTP id e55FrFO76588 for ; Mon, 5 Jun 2000 11:53:15 -0400 (EDT) Received: from propertykey.com (gotojail.propertykey.com [206.196.37.197]) by propertykey.com (8.9.3/8.9.3) with ESMTP id KAA04792; Mon, 5 Jun 2000 10:53:03 -0500 Message-ID: <393BCCC6.7D394758@propertykey.com> Date: Mon, 05 Jun 2000 10:52:38 -0500 From: Jeff Hoffmann Organization: PropertyKey.com X-Mailer: Mozilla 4.72 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: rslifka@home.com, pgsql-sql@postgresql.org Subject: Re: Default timestamp value References: <001101bfcec0$8683f1e0$39117118@cr699264-a.rct1.bc.wave.home.com> <12065.960218869@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200006/40 Tom Lane wrote: > > "Rob S." writes: > > ...but I still don't see how to have the default value of a timestamp to be > > the time at which the individual record is inserted. I just get the time I > > created the table. > Versions before 7.0 are not entirely consistent about this, but I > believe the explicit function call now() will work the way you want > in any version. you can also use the value CURRENT_TIMESTAMP, which is standard SQL. you have to be careful about using 'now' as a default, though. i have a tendancy to put type qualifications on most of the things i write ever since i had problems with getting an index to be used on an int2 field. the key to the faq is that you can't put a type qualification after the 'now', otherwise it won't work. so in 7.0, if you create a table like any of these: create table foo (f1 int, f2 timestamp default now()); create table foo (f1 int, f2 timestamp default 'now'); create table foo (f1 int, f2 timestamp default CURRENT_TIMESTAMP); it will work. if you do it like this, it won't: create table foo (f1 int, f2 timestamp default 'now'::datetime); create table foo (f1 int, f2 timestamp default 'now'::timestamp); it will give you the time that the table was created. it's just a little gotcha that might frustrate people in a similar situation. jeff