Обсуждение: case when evaluating else condition anyway?

I've

case when ''=extinput then null else extinput::timestamp end

now when extinput='' it seems that else extinput::timestamp get
evaluated anyway and I get:

invalid input syntax for type timestamp: ""

The purpose of all this gymnic would be to convert empty string to
null and everything else to a timestamp.

Is there any cleaner functional way that doesn't involve prepared
statement etc... since the whole exercise is caused by an
null-impaired DB API (aka MySQLish).

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I've
> case when ''=extinput then null else extinput::timestamp end
> now when extinput='' it seems that else extinput::timestamp get
> evaluated anyway and I get:
> invalid input syntax for type timestamp: ""

I think you are leaving off a large percentage of the truth.
Is "extinput" actually a variable, or do you mean that you are
substituting a literal string there?

            regards, tom lane

On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo wrote:
> case when ''=extinput then null else extinput::timestamp end

I'd tend to use nullif(extinput,'')::timestamp for this sort of thing.
It's not going to do anything much different from what you're doing, but
may end up evaluating "extinput" less which may be a good thing.

> Is there any cleaner functional way that doesn't involve prepared
> statement etc... since the whole exercise is caused by an
> null-impaired DB API (aka MySQLish).

As Tom said, maybe if you could send a larger example it would help.


  Sam

On Wed, 26 Nov 2008 14:18:44 +0000
Sam Mason <sam@samason.me.uk> wrote:

> On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo
> wrote:
> > case when ''=extinput then null else extinput::timestamp end
>
> I'd tend to use nullif(extinput,'')::timestamp for this sort of

Thanks it was on the top of my fingers... BTW it was a substituted
literal ala printf before anything was piped to postgresql, so
actually postgresql would receive something as the examples below.

Still why does the else condition get evaluated anyway when at least
one of the "when" condition is true?

> thing. It's not going to do anything much different from what
> you're doing, but may end up evaluating "extinput" less which may
> be a good thing.
>
> > Is there any cleaner functional way that doesn't involve prepared
> > statement etc... since the whole exercise is caused by an
> > null-impaired DB API (aka MySQLish).
>
> As Tom said, maybe if you could send a larger example it would
> help.

as a more self contained example:

select case when ''='' then null else ''::timestamp end;

vs.

select nullif('','')::timestamp;

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Wed, Nov 26, 2008 at 04:17:40PM +0100, Ivan Sergio Borgonovo wrote:
> as a more self contained example:
>
> select case when ''='' then null else ''::timestamp end;

Tee hee, I've just realized what you're doing.  You've got the cast in
the wrong place!  Try:

  select case when ''='' then null else '' end::timestamp;

I was reading it this way around automatically!  Literals are always
expanded immediately and hence you're getting the error.  You want the
case statement to work with strings and only cast it when you know it's
actually safe to make the move from a string literal to a timestamp
value.


  Sam

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> Still why does the else condition get evaluated anyway when at least
> one of the "when" condition is true?

Because 'literal'::type is a literal of that type; it does not represent
a run-time conversion.  I think we document that under the discussion
of constants in the syntax chapter.

            regards, tom lane