Re: SELECT DISTINCT is picky about constants

On Tue, 2003-02-04 at 07:15, Andreas Pflug wrote:
> The query
>     SELECT DISTINCT keycol, 'constant' FROM myTable
> or
>     SELECT DISTINCT keycol, NULL FROM myTable
>
> will result in an error message (7.3.1)
>
> Unable to identify an ordering operator '<' for type "unknown"
> Use explicit ordering operator or modify query
>
> If I use 'constant'::varchar or NULL::varchar everything's fine.
> Unfortunately, this SELECT DISTINCT will appear quite often in my app.
>
> I'd rather like PostgreSQL to use implicit type casting for such
> constants. The final type chosen doesn't matter anyway and life would be
> easier.

How about:

SELECT keycol, NULL FROM (SELECT DISTINCT keycol FROM myTable) AS tab;

Might even be quicker as you won't have to do any comparisons against
the constant.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc