pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT DISTINCT is picky about constants
2+ messages / 2 participants
[nested] [flat]

* SELECT DISTINCT is picky about constants
@ 2003-02-04 12:15  Andreas Pflug <Andreas.Pflug@web.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Andreas Pflug @ 2003-02-04 12:15 UTC (permalink / raw)
  To: pgsql-performance

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.






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SELECT DISTINCT is picky about constants
@ 2003-02-04 14:29  Rod Taylor <rbt@rbt.ca>
  parent: Andreas Pflug <Andreas.Pflug@web.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Rod Taylor @ 2003-02-04 14:29 UTC (permalink / raw)
  To: Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-performance

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

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-02-04 14:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-04 12:15 SELECT DISTINCT is picky about constants Andreas Pflug <Andreas.Pflug@web.de>
2003-02-04 14:29 ` Rod Taylor <rbt@rbt.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox