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 BA9CC9DC948 for ; Wed, 14 Dec 2005 14:22:21 -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 47216-07 for ; Wed, 14 Dec 2005 14:22:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by postgresql.org (Postfix) with ESMTP id 717269DC89C for ; Wed, 14 Dec 2005 14:22:19 -0400 (AST) Received: from mail.99main.com (mail.99main.com [208.28.184.11]) by svr2.postgresql.org (Postfix) with ESMTP id 5DC9EF0B55 for ; Wed, 14 Dec 2005 18:22:19 +0000 (GMT) Received: from 99main.com (localhost [127.0.0.1]) by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id jBEIMGaF020190; Wed, 14 Dec 2005 13:22:16 -0500 (EST) From: "Keith Worthington" To: "Ken Winter" Cc: "PostgreSQL pg-sql list" Reply-To: KeithW@narrowpathinc.com Subject: Re: Defaulting a column to 'now' Date: Wed, 14 Dec 2005 13:22:16 -0500 Message-Id: <20051214181720.M80996@narrowpathinc.com> In-Reply-To: <003501c600d9$b799a1c0$6603a8c0@kenxp> References: <003501c600d9$b799a1c0$6603a8c0@kenxp> X-Mailer: Open WebMail 2.50 20050106 X-OriginatingIP: 68.235.22.242 (npkeithw) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/124 X-Sequence-Number: 23666 On Wed, 14 Dec 2005 13:10:50 -0500, Ken Winter wrote > How can a column's default be set to 'now', meaning 'now' as of when each > row is inserted? > > For example, here's a snip of DDL: > > create table personal_data (. > > effective_date_and_time TIMESTAMP WITH TIME ZONE not null default > 'now',. > > The problem is, when PostgreSQL processes this DDL, it interprets the 'now' > as the timestamp when the table is created, so that the tables definition > reads as if the DDL were: > > effective_date_and_time TIMESTAMP WITH TIME ZONE not null default ' > 2005-12-14 11:00:16.749616-06 ', > > so all of the newly inserted rows get assigned effective_date_and_time > = ' > 2005-12-14 11:00:16.749616-06 ', which in addition to being wrong > leads to uniqueness constraint violations. > > ~ TIA > > ~ Ken Ken, effective_date_and_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone Kind Regards, Keith