From ken@sunward.org Wed Dec  14 18:11:03 2005
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>;
	Wed, 14 Dec 2005 18:10:58 +0000
From: "Ken Winter" <ken@sunward.org>
To: "PostgreSQL pg-sql list" <pgsql-sql@postgresql.org>
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

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">


<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Verdana;
	color:windowtext;
	font-weight:normal;
	font-style:normal;
	text-decoration:none none;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>How can a column&#8217;s default be set to =
&#8216;now&#8217;,
meaning &#8216;now&#8217; as of when each row is =
inserted?</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>For example, here&#8217;s a snip of =
DDL:</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>create table personal_data =
(&#8230;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>effective_date_and_time TIMESTAMP WITH TIME ZONE =
not null
default 'now',&#8230;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>The problem is, when PostgreSQL processes this DDL, =
it
interprets the &#8216;now&#8217; as the timestamp when the table is =
created, so
that the tables definition reads as if the DDL were:</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>effective_date_and_time TIMESTAMP WITH TIME ZONE =
not null
default '</span></font> <font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>2005-12-14 11:00:16.749616-06 ',</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>so all of the newly inserted rows get assigned =
effective_date_and_time
=3D '</span></font> <font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>2005-12-14 11:00:16.749616-06 ', which in addition =
to
being wrong leads to uniqueness constraint violations.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>~ TIA</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>~ Ken</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

</div>

</body>

</html>

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





From banderson@presinet.com Wed Dec  14 18:16:28 2005
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 08BB19DC87C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 14 Dec 2005 14:16:28 -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 46682-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 14 Dec 2005 14:16:27 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from presinet.com (presinet.com [209.53.156.1])
	by postgresql.org (Postfix) with ESMTP id CEA039DC887
	for <pgsql-sql@postgresql.org>; Wed, 14 Dec 2005 14:16:25 -0400 (AST)
Received: from [10.10.1.151] ([10.10.1.151]) by presinet.com with Microsoft
	SMTPSVC(6.0.3790.1830); Wed, 14 Dec 2005 10:12:10 -0800
Message-ID: <43A0612C.2010605@presinet.com>
Date: Wed, 14 Dec 2005 10:15:08 -0800
From: Bricklen Anderson <banderson@presinet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Ken Winter <ken@sunward.org>
CC: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
Subject: Re: Defaulting a column to 'now'
References: <003501c600d9$b799a1c0$6603a8c0@kenxp>
In-Reply-To: <003501c600d9$b799a1c0$6603a8c0@kenxp>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-OriginalArrivalTime: 14 Dec 2005 18:12:10.0937 (UTC)
	FILETIME=[E6F53290:01C600D9]
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/123
X-Sequence-Number: 23665

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',…
> 

try with now(), instead of now
...
effective_date_and_time TIMESTAMPTZ not null default now()...



From tgl@sss.pgh.pa.us Wed Dec  14 18:15:22 2005
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 8D69B9DC830
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 14 Dec 2005 14:15:22 -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 46960-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 14 Dec 2005 14:15:21 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 190419DC82E
	for <pgsql-sql@postgresql.org>; Wed, 14 Dec 2005 14:15:19 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBEIFKNc022428;
	Wed, 14 Dec 2005 13:15:20 -0500 (EST)
To: "Ken Winter" <ken@sunward.org>
cc: "PostgreSQL pg-sql list" <pgsql-sql@postgresql.org>
Subject: Re: Defaulting a column to 'now' 
In-reply-to: <003501c600d9$b799a1c0$6603a8c0@kenxp> 
References: <003501c600d9$b799a1c0$6603a8c0@kenxp>
Comments: In-reply-to "Ken Winter" <ken@sunward.org>
	message dated "Wed, 14 Dec 2005 13:10:50 -0500"
Date: Wed, 14 Dec 2005 13:15:20 -0500
Message-ID: <22427.1134584120@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.002 required=5 tests=[AWL=0.002]
X-Spam-Score: 0.002
X-Spam-Level: 
X-Archive-Number: 200512/122
X-Sequence-Number: 23664

"Ken Winter" <ken@sunward.org> 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



From keithw@narrowpathinc.com Wed Dec  14 18:22:21 2005
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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" <keithw@narrowpathinc.com>
To: "Ken Winter" <ken@sunward.org>
Cc: "PostgreSQL pg-sql list" <pgsql-sql@postgresql.org>
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



From ken@sunward.org Thu Dec  15 17:20:41 2005
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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" <ken@sunward.org>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
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" <ken@sunward.org> 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