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.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Bruce Momjian wrote:
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.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
.... which is in itself pretty bogus IMHO. There are a couple of the
geometric types for which '=' does not do what you'd really expect.
I've been wondering if we could get away with changing this ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane wrote:
Yes, we would have to document it in the release notes but it is quite
surprising at is currently is configured.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Added to TODO:

* Fix data types where equality comparison isn't intuitive, e.g. box


---------------------------------------------------------------------------

Tom Lane wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly