Re: Regarding select distinct ...query - Mailing list pgsql-general

From Tom Lane
Subject Re: Regarding select distinct ...query
Date
Msg-id 13595.1040259080@sss.pgh.pa.us
Whole thread Raw
In response to Re: Regarding select distinct ...query  (Greg Stark <gsstark@mit.edu>)
List pgsql-general
Greg Stark <gsstark@mit.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

pgsql-general by date:

Previous
From: "Ian Harding"
Date:
Subject: Re: Regarding select distinct ...query
Next
From: Larry Rosenman
Date:
Subject: Re: trouble caused by change in 7.3 handling of '' in