Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 8424E2E33DD for ; Wed, 28 Nov 2007 15:01:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 52385-01 for ; Wed, 28 Nov 2007 15:01:16 -0400 (AST) Received: from smtp126.iad.emailsrvr.com (smtp126.iad.emailsrvr.com [207.97.245.126]) by postgresql.org (Postfix) with ESMTP id A65FE2E35DE for ; Wed, 28 Nov 2007 15:01:14 -0400 (AST) Received: from relay2.r2.iad.emailsrvr.com (localhost [127.0.0.1]) by relay2.r2.iad.emailsrvr.com (SMTP Server) with ESMTP id EED7E44D82C; Wed, 28 Nov 2007 14:01:13 -0500 (EST) Received: by relay2.r2.iad.emailsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id B915D44C0F2; Wed, 28 Nov 2007 14:01:13 -0500 (EST) In-Reply-To: <17DF2022-C1D2-4D9E-ABAC-DC3F5B20FBE7@seespotcode.net> References: <474404120711271804y21b8e0b3j7f4b3771c088a82a@mail.gmail.com> <17DF2022-C1D2-4D9E-ABAC-DC3F5B20FBE7@seespotcode.net> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: Cc: "Gera Mel Handumon" , pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: Erik Jones Subject: Re: NULLIF problem Date: Wed, 28 Nov 2007 13:00:49 -0600 To: Michael Glaesemann X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/144 X-Sequence-Number: 29672 On Nov 27, 2007, at 8:47 PM, Michael Glaesemann 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=3Dnullif(mytime,'') >> >> i want to null the value of field DBTIME if the variable mytime=3D" " >> >> DBTIME =3D"timestamp with time zone" datatype >> >> error: column DBTIME is of type timestamp with time zone but =20 >> expression is of type text. > > I believe the reason is that '' is not a valid timestamp value: =20 > think of it this way: > > IF mytime =3D '' THEN > mytime :=3D NULL; > END IF; > > The first thing it needs to do is compare the mytime value with ''. =20= > As '' is not a valid timestamp value, it may be casing mytime to =20 > text. You'll run into problems if you're assigning a text value to =20 > a timestamp field (which happens after the initial comparison--and =20 > the cast--are done.) > > I think you may need to handle this is you middleware, or handle =20 > the IF THEN explicitly in a function. Maybe CASE would work: > > CASE WHEN mytime =3D '' THEN NULL > ELSE CAST(mytime AS TIMESTAMP) > END Why not just: UPDATE table SET mytime=3DNULL WHERE mytime=3D''; Erik Jones Software Developer | Emma=AE erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com