Re: Regarding select distinct ...query

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.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).

Well, if that what the spec says then I would imagine you should at least
follow it in the absence of other information. I don't see what's "dangerous"
about the assumption that valid standard SQL code should be interpreted as
such.

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.

I'm kind of surprised actually that it doesn't work the other way around. It
seems like the standard datatypes ought to be the default for all constants
and the kludges should only be necessary to automatically cast strings and
numbers to other datatypes for programmer convenience.

--
greg