Thread: select distinct point?

You can't select distinct on the point datatype column? I see why it's
nonobvious how to sort points but then how come it works fine to select
distinct on a box column?


slo=> create table test (p point);
CREATE TABLE
slo=> select distinct * from test;
ERROR:  Unable to identify an ordering operator '<' for type 'point'
    Use an explicit ordering operator or modify the query
slo=> create table test2 (b box);
CREATE TABLE
slo=> select distinct * from test2;
 b
---
(0 rows)




--
greg

Greg Stark <gsstark@mit.edu> writes:
> You can't select distinct on the point datatype column? I see why it's
> nonobvious how to sort points but then how come it works fine to select
> distinct on a box column?

Depends on your definition of "works fine", I suppose.

regression=# create table test2 (b box);
CREATE TABLE
regression=# insert into test2 values ('(1,1), (2,2)');
INSERT 680713 1
regression=# insert into test2 values ('(1,1), (1.5,3)');
INSERT 680714 1
regression=# select * from test2;
       b
---------------
 (2,2),(1,1)
 (1.5,3),(1,1)
(2 rows)

regression=# select distinct * from test2;
      b
-------------
 (2,2),(1,1)
(1 row)

This is not DISTINCT's fault:

regression=# select '(2,2),(1,1)'::box = '(1.5,3),(1,1)'::box;
 ?column?
----------
 t
(1 row)

Type box has '<' and '=' operators, but they're defined to compare the
areas of boxes.  It bothers me that the name '=' was used for an
operator that is not box equality by any sane standard, but that's how
it's defined at the moment.  Poor DISTINCT, of course, just applies the
operators that have the right names; it's got no way to know that the
semantics aren't sensible.

            regards, tom lane