pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNULLIF problem
5+ messages / 3 participants
[nested] [flat]
* NULLIF problem
@ 2007-11-28 02:04 Gera Mel Handumon <geramel.h@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gera Mel Handumon @ 2007-11-28 02:04 UTC (permalink / raw)
To: pgsql-sql
HELLO,
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.
What should i do?
Thanks in advance...
--
Gera Mel E. Handumon
Application Programmer
PaySoft Solutions, Inc.
-----------------------------------------------------------------
"Share your knowledge. It's a way to achieve immortality" - Dalai Lama
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLIF problem
@ 2007-11-28 02:47 Michael Glaesemann <grzm@seespotcode.net>
parent: Gera Mel Handumon <geramel.h@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Michael Glaesemann @ 2007-11-28 02:47 UTC (permalink / raw)
To: Gera Mel Handumon <geramel.h@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLIF problem
@ 2007-11-28 19:00 Erik Jones <erik@myemma.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 1 reply; 5+ messages in thread
From: Erik Jones @ 2007-11-28 19:00 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Gera Mel Handumon <geramel.h@gmail.com>; pgsql-sql
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=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
Why not just:
UPDATE table
SET mytime=NULL
WHERE mytime='';
Erik Jones
Software Developer | Emma®
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLIF problem
@ 2007-11-29 09:43 Gera Mel Handumon <geramel.h@gmail.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 5+ messages in thread
From: Gera Mel Handumon @ 2007-11-29 09:43 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLIF problem
@ 2007-11-29 12:46 Michael Glaesemann <grzm@seespotcode.net>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2007-11-29 12:46 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Gera Mel Handumon <geramel.h@gmail.com>; pgsql-sql
On Nov 28, 2007, at 14:00 , Erik Jones wrote:
> Why not just:
>
> UPDATE table
> SET mytime=NULL
> WHERE mytime='';
If mytime is a timestamp field, it won't have any values ''. I
believe the OP is updating mytime to a client-supplied value which is
passing '' when it probably means NULL.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-11-29 12:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-28 02:04 NULLIF problem Gera Mel Handumon <geramel.h@gmail.com>
2007-11-28 02:47 ` Michael Glaesemann <grzm@seespotcode.net>
2007-11-28 19:00 ` Erik Jones <erik@myemma.com>
2007-11-29 12:46 ` Michael Glaesemann <grzm@seespotcode.net>
2007-11-29 09:43 ` Gera Mel Handumon <geramel.h@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox