Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id AAA35421 for ; Wed, 3 Nov 1999 00:48:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id AAA18778; Wed, 3 Nov 1999 00:48:26 -0500 (EST) To: davidrugge@mindspring.com cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] timestamps In-reply-to: Your message of Tue, 2 Nov 1999 21:51:52 -0500 <99110221560802.00836@joplin> Date: Wed, 03 Nov 1999 00:48:26 -0500 Message-ID: <18776.941608106@sss.pgh.pa.us> From: Tom Lane David Rugge writes: > I created a table using this statement: > create table timestamptest (creation_date timestamp default text 'now') > But I get the creation date of the table instead of the date of the > transaction. This is the format suggested in the manual to workaround the > default value being set at table creation, but it doesn't work! Try "default now()". The documentation recommending "default text 'now'" is bogus, IMHO. That method depends on a particular set of interacting behaviors in the parser and the table-default-creation code, and at least some of those behaviors were arguably bugs. They were also data-type-dependent --- I believe "default text 'now'" does work in 6.5.* for a DATETIME column, but not for a TIMESTAMP column, because of slight differences in the sets of available operators for the two datatypes. FYI, any of these variants should give the result you want in future releases. But now() is the only one that I think can be counted on to work in current and obsolete Postgres versions too. regards, tom lane