Re: Regarding select distinct ...query

Greg Stark <gsstark(at)mit(dot)edu> writes:
> I ran into precisely the same issue here yesterday. This works perfectly fine
> in Oracle, does the standard really leave "select distinct 'foo'" undefined?

The standard has no notion of datatype extensibility, so it's got no
problem with legislating that anything between single quotes is of type
CHAR(n). If we followed the spec closely on this point, you'd probably
need an explicit cast for *every* literal you wanted to be of a datatype
other than numeric (no quotes) or string (with quotes).

We do have some kluges in place to default to assuming that
string-looking literals are of type TEXT when we can't figure out
anything else, but those kluges didn't cover the SELECT DISTINCT 'foo'
case last time I looked. It's a fairly dangerous default assumption
IMHO, so I think it's appropriate to make it only in very narrowly
defined cases ...

regards, tom lane