From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Bugs for PostgreSQL <pgsql-bugs(at)postgreSQL(dot)org> |
Subject: | Re: SELECT DISTINCT on boxes |
Date: | 2004-07-15 03:29:11 |
Message-ID: | 200407150329.i6F3TBg06522@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Bruce Momjian wrote:
> Is there a way to do a SELECT DISTINCT on boxes:
>
> test=> create TABLE t3 (a box);
> CREATE TABLE
> test=> insert into t3 values ('(2,2),(1,1)');
> INSERT 17232 1
> test=> insert into t3 values ('(2,2),(1,1)');
> INSERT 17233 1
> test=> insert into t3 values ('(3,3),(2,2)');
> INSERT 17234 1
> test=> insert into t3 values ('(3,3),(2,2)');
> INSERT 17235 1
> test=> select distinct * from t3;
> ERROR: could not identify an ordering operator for type box
> HINT: Use an explicit ordering operator or modify the query.
>
> I tried doing subqueries and using oids but that didn't help.
>
> I don't understand why this doesn't work:
>
> SELECT a
> FROM t3 t2
> WHERE t2.oid = (SELECT MIN(t.oid) FROM t3 t WHERE t2.a = t.a);
>
> a
> -------------
> (2,2),(1,1)
> (1 row)
>
> If finds only the duplicate.
I found the cause. Equals for boxes compares only area, \do:
pg_catalog | = | box | box | boolean | equal by area
The proper fix is to use ~= which compares boxes for similarity:
pg_catalog | ~= | box | box | boolean | same as?
The correct query for DISTINCT is:
test=> SELECT oid, a FROM t3 t2 WHERE t2.oid = (SELECT MIN(t.oid) FROM
t3 t WHERE t2.a ~= t.a);
oid | a
-------+-------------
17232 | (2,2),(1,1)
17234 | (3,3),(2,2)
(2 rows)
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-07-15 04:26:12 | Re: SELECT DISTINCT on boxes |
Previous Message | Aaron Hillegass | 2004-07-14 18:53:50 | Large object API problems |