Re: Regarding select distinct ...query

Greg Stark <gsstark(at)mit(dot)edu> writes:
> In the current situation we have a perfectly fine standard SQL query that runs
> fine on other databases but fails on postgres. And the only way to "fix" it
> for postgres is to add a non-standard cast to it that won't work on the other
> databases.

Which part of CAST('' AS CHARACTER VARYING(1)) doesn't work on your other
databases?

I am not here to defend the fact that SELECT DISTINCT 'foo' fails; in
fact fixing it is on my todo list I think [digs...] yeah here it is:

: SELECT DISTINCT 'bar' fails because there's no ordering op for unknown.
: Probably should coerce to text, much as we do for CASE and UNION.
: Msg 2/01/02 17:53
: Anywhere we are doing assignSortGroupRef, seems okay to modify tle to
: coerce to text. transformGroupClause, addTargetToSortList (but prepunion
: calls addAllTargetsToSortList; maybe need to do it at higher level?)

I'm just pointing out why it historically hasn't worked.

regards, tom lane