select distinct point? 
Author Message
 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Wed, 22 Jun 2005 09:22:56 GMT
 select distinct point?

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Wed, 22 Jun 2005 10:08:48 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. select distinct much slower than non-distinct

2. SELECT DISTINCT not distinct enough

3. SELECT DISTINCT yields more than SELECT

4. ORDER BY items must appear in the select list if SELECT DISTINCT is specified

5. SELECT DISTINCT blocks; SELECT ALL doesn't

6. SELECT works, but SELECT DISTINCT fails

7. SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

8. Select closest point

9. SELECT DISTINCT PROBLEMS!!!

10. SELECT DISTINCT [high number of columns] fails

11. How to select top n distinct ...?

12. Use "Order By" with SELECT DISTINCT


 
Powered by phpBB® Forum Software