pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Bad time external representation '' 2+ messages / 2 participants [nested] [flat]
* Bad time external representation '' @ 2002-05-09 14:37 Heiko <heiko611@gmx.net> 2002-05-10 16:05 ` Re: Bad time external representation '' Stephan Szabo <sszabo@megazone23.bigpanda.com> 0 siblings, 1 reply; 2+ messages in thread From: Heiko @ 2002-05-09 14:37 UTC (permalink / raw) To: pgsql-sql hi, 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 '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 :-) thanks a lot for any help, h. jakubzik ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bad time external representation '' 2002-05-09 14:37 Bad time external representation '' Heiko <heiko611@gmx.net> @ 2002-05-10 16:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com> 0 siblings, 0 replies; 2+ messages in thread From: Stephan Szabo @ 2002-05-10 16:05 UTC (permalink / raw) To: Heiko <heiko611@gmx.net>; +Cc: pgsql-sql 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-10 16:05 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-05-09 14:37 Bad time external representation '' Heiko <heiko611@gmx.net> 2002-05-10 16:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.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