Thread: SELECT DISTINCT is picky about constants

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.

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