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 4F0439DC9EF for ; Wed, 14 Dec 2005 14:11:03 -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 44038-08 for ; Wed, 14 Dec 2005 14:11:02 -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 32B4B9DC9EA for ; Wed, 14 Dec 2005 14:10:59 -0400 (AST) Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet Mail Server X 3.2.8b1) for ; Wed, 14 Dec 2005 18:10:58 +0000 From: "Ken Winter" To: "PostgreSQL pg-sql list" Subject: Defaulting a column to 'now' Date: Wed, 14 Dec 2005 13:10:50 -0500 Message-ID: <003501c600d9$b799a1c0$6603a8c0@kenxp> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0036_01C600AF.CEC399C0" 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 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/121 X-Sequence-Number: 23663 This is a multi-part message in MIME format. ------=_NextPart_000_0036_01C600AF.CEC399C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0036_01C600AF.CEC399C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D ' 2005-12-14 11:00:16.749616-06 ', which in addition = to being wrong leads to uniqueness constraint violations.

 

~ TIA

~ Ken

 

------=_NextPart_000_0036_01C600AF.CEC399C0--