X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C0B599DC807 for ; Thu, 15 Dec 2005 13:20:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17774-05 for ; Thu, 15 Dec 2005 13:20:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from anu.ie (mail.anu.net [213.247.49.236]) by postgresql.org (Postfix) with ESMTP id 104819DC998 for ; Thu, 15 Dec 2005 13:20:38 -0400 (AST) Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet Mail Server X 3.2.8b1); Thu, 15 Dec 2005 17:20:38 +0000 From: "Ken Winter" To: "'Tom Lane'" Cc: "'PostgreSQL pg-sql list'" Subject: Re: Defaulting a column to 'now' Date: Thu, 15 Dec 2005 12:20:28 -0500 Message-ID: <002301c6019b$d8e8ec60$6603a8c0@kenxp> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal In-Reply-To: <22427.1134584120@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[AWL=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/134 X-Sequence-Number: 23676 Thanks, Tom (also Keith Worthington and Bricklen Anderson). That works. ~ Ken > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Wednesday, December 14, 2005 1:15 PM > To: Ken Winter > Cc: PostgreSQL pg-sql list > Subject: Re: [SQL] Defaulting a column to 'now' > > "Ken Winter" writes: > > How can a column's default be set to 'now', meaning 'now' as of when > each > > row is inserted? > > You need a function, not a literal constant. The SQL-spec way is > CURRENT_TIMESTAMP > (which is a function, despite the spec's weird idea that it should be > spelled without parentheses); the traditional Postgres way is > now() > > Either way only sets an insertion default, though. If you want to > enforce a correct value on insertion, or change the value when the > row is UPDATEd, you need to use a trigger. > > regards, tom lane