pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Heiko <heiko611@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Bad time external representation ''
Date: Fri, 10 May 2002 09:05:11 -0700 (PDT)
Message-ID: <20020510085836.F39883-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <000601c1f767$17b34000$010aa8c0@heiko1>

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.




view thread (2+ messages)

Message-ID: <20020510085836.F39883-100000@megazone23.bigpanda.com>
Permalink:  ../20020510085836.F39883-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20020510085836.F39883-100000@megazone23.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sszabo@megazone23.bigpanda.com, heiko611@gmx.net
  Subject: Re: Bad time external representation ''
  In-Reply-To: <20020510085836.F39883-100000@megazone23.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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