Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id F2F702E3924
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 29 Nov 2007 05:44:01 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 71623-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 29 Nov 2007 05:43:54 -0400 (AST)
Received: from wa-out-1112.google.com (wa-out-1112.google.com
 [209.85.146.178])
	by postgresql.org (Postfix) with ESMTP id C902C2E391D
	for <pgsql-sql@postgresql.org>; Thu, 29 Nov 2007 05:43:39 -0400 (AST)
Received: by wa-out-1112.google.com with SMTP id m38so2373632waf
	for <pgsql-sql@postgresql.org>; Thu, 29 Nov 2007 01:43:38 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=omzoYhbQCUT4iJ+yIWD3eKPyYvygfs5GTUbFrd5PJXc=;
	b=CkhyevY7DHms/46dzP/bCaGsYKy+Ztxiirkxap9zjmTPaiKOag0MGPAit/sjrF/7rPl0FO2dlx3nRvg+UtDb80sab0ooKHlQuMDDeh/jkgzhT3dUP1pZWoQb7aLI//ff6ZPWk+8ZDnDKMcor9lUvPMvv7oc2zU4zsCIkcPX/T2Y=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=l61rp1vY1qoOs/asE91w3kqdnoVxAAv+N1LYmcQqcSawOf7kcY9YaeyAbINZBi+9c7XzImfBMPYDxFW/U0qsGEpDASvsd0Y0bcY9ZzG+RoEbYaJfz+xkFifunpULzYItw6ecGDOGFcwRnAQO1N0GFPq/gpT85oK6BOXf3cR+Cw8=
Received: by 10.114.120.1 with SMTP id s1mr689037wac.1196329418361;
	Thu, 29 Nov 2007 01:43:38 -0800 (PST)
Received: by 10.115.88.17 with HTTP; Thu, 29 Nov 2007 01:43:38 -0800 (PST)
Message-ID: <474404120711290143g6ef15a73j9363b2b95882f043@mail.gmail.com>
Date: Thu, 29 Nov 2007 17:43:38 +0800
From: "Gera Mel Handumon" <geramel.h@gmail.com>
To: "Michael Glaesemann" <grzm@seespotcode.net>
Subject: Re: NULLIF problem
Cc: pgsql-sql@postgresql.org
In-Reply-To: <17DF2022-C1D2-4D9E-ABAC-DC3F5B20FBE7@seespotcode.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1120_12121189.1196329418310"
References: <474404120711271804y21b8e0b3j7f4b3771c088a82a@mail.gmail.com>
	<17DF2022-C1D2-4D9E-ABAC-DC3F5B20FBE7@seespotcode.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/150
X-Sequence-Number: 29678

------=_Part_1120_12121189.1196329418310
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks!

On Nov 28, 2007 10:47 AM, Michael Glaesemann <grzm@seespotcode.net> wrote:

>
> On Nov 27, 2007, at 21:04 , Gera Mel Handumon wrote:
>
> > I encounter an error if i use NULLIF with timestamp with time zone.
> > eq. dbtime=nullif(mytime,'')
> >
> > i want to null the value of field DBTIME if the variable mytime=" "
> >
> > DBTIME ="timestamp with time zone" datatype
> >
> > error: column DBTIME is of type timestamp with time zone but
> > expression is of type text.
>
> I believe the reason is that '' is not a valid timestamp value: think
> of it this way:
>
> IF mytime = '' THEN
>   mytime := NULL;
> END IF;
>
> The first thing it needs to do is compare the mytime value with ''.
> As '' is not a valid timestamp value, it may be casing mytime to
> text. You'll run into problems if you're assigning a text value to a
> timestamp field (which happens after the initial comparison--and the
> cast--are done.)
>
> I think you may need to handle this is you middleware, or handle the
> IF THEN explicitly in a function. Maybe CASE would work:
>
> CASE WHEN mytime = '' THEN NULL
>      ELSE CAST(mytime AS TIMESTAMP)
>     END
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>


-- 
Gera Mel E. Handumon
Application Programmer
PaySoft Solutions, Inc.
-----------------------------------------------------------------
"Share your knowledge. It's a way to achieve immortality" - Dalai Lama

------=_Part_1120_12121189.1196329418310
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks!<br><br><div class="gmail_quote">On Nov 28, 2007 10:47 AM, Michael Glaesemann &lt;<a href="mailto:grzm@seespotcode.net">grzm@seespotcode.net</a>&gt; wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class="Ih2E3d"><br>On Nov 27, 2007, at 21:04 , Gera Mel Handumon wrote:<br><br>&gt; I encounter an error if i use NULLIF with timestamp with time zone.<br>&gt; eq. dbtime=nullif(mytime,&#39;&#39;)<br>&gt;<br>&gt; i want to null the value of field DBTIME if the variable mytime=&quot; &quot;
<br>&gt;<br>&gt; DBTIME =&quot;timestamp with time zone&quot; datatype<br>&gt;<br>&gt; error: column DBTIME is of type timestamp with time zone but<br>&gt; expression is of type text.<br><br></div>I believe the reason is that &#39;&#39; is not a valid timestamp value: think
<br>of it this way:<br><br>IF mytime = &#39;&#39; THEN<br> &nbsp; mytime := NULL;<br>END IF;<br><br>The first thing it needs to do is compare the mytime value with &#39;&#39;.<br>As &#39;&#39; is not a valid timestamp value, it may be casing mytime to
<br>text. You&#39;ll run into problems if you&#39;re assigning a text value to a<br>timestamp field (which happens after the initial comparison--and the<br>cast--are done.)<br><br>I think you may need to handle this is you middleware, or handle the
<br>IF THEN explicitly in a function. Maybe CASE would work:<br><br>CASE WHEN mytime = &#39;&#39; THEN NULL<br> &nbsp; &nbsp; &nbsp;ELSE CAST(mytime AS TIMESTAMP)<br> &nbsp; &nbsp; END<br><font color="#888888"><br>Michael Glaesemann<br>grzm seespotcode net
<br><br><br></font></blockquote></div><br><br clear="all"><br>-- <br>Gera Mel E. Handumon<br>Application Programmer<br>PaySoft Solutions, Inc.<br>-----------------------------------------------------------------<br>&quot;Share your knowledge. It&#39;s a way to achieve immortality&quot; - Dalai Lama

------=_Part_1120_12121189.1196329418310--