Re: Bad time external representation ''

On Thu, 9 May 2002, Heiko wrote:

> currently upgraded SuSE linux 7.0 to 8.0 including an upgrade from postgres
> 7.0 to 7.2.
> almost everything went very well, all the data intact, very peachy. only one
> seemingly stupid problem:
> queries like 'select case when a.dtmstart is null then '' else a.dtmstart
> end from a;' where 'dtmstart' is a time-field result in a 'bad time external
> representation ''' - error. (tried various casting tricks, none worked).
>
> it works fine if i just leave the entire 'case when' - section out; if i
> remember correctly, i put the section in because otherwise i got explicit
> 'null' values in my application; this does not happen anymore, so the
> 'select case' workaround has become superfluous.
>
> for various reasons it would be a major pain in the butt for me, however, to
> change the sql syntax in my applications; i would either have to produce and
> control twice the amount of versions or change my interface so that it
> catches the 'null's that 7.0 produces. is there a way to make postgres 7.2.
> accept my old 'case when'-queries? and on a logical basis: why should a

Is it possible for you to use a valid datetime as the result?  I'm not
sure what '' meant as a datetime, so I don't know how you're using it.

> 'case when' expect one explicit data-type? shouldn't it theoretically be
> possible to use a 'case when' also to hand back different types depending on
> whether the condition comes true or not? or am i overlooking something basic
> (would not be entirely untypical of me :-)

Well, what happens when it differs between rows?  What type is that row?
:)

I think we technically don't meet the wording of sql92, but I think we
meet the intention given that we have a much more complicated type system
Case mentions that the data type of a <case specification> is determined
by applying the rules of "Set operation result data types" to the data
types of all result expressions. That section gives a bunch of rules
like, if any is a character string, all will be character strings with
the same character repertoire.