FAQ
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@candle.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

Search Discussions

  • Bruce Momjian at Jul 15, 2004 at 3:29 am

    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@candle.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
  • Tom Lane at Jul 15, 2004 at 4:26 am

    Bruce Momjian writes:
    I found the cause. Equals for boxes compares only area, \do:
    ... 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
  • Bruce Momjian at Jul 15, 2004 at 2:32 pm

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    I found the cause. Equals for boxes compares only area, \do:
    ... 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 ...
    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@candle.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
  • Bruce Momjian at Jul 20, 2004 at 7:52 pm
    Added to TODO:

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


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

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    I found the cause. Equals for boxes compares only area, \do:
    ... 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
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJul 14, '04 at 5:03p
activeJul 20, '04 at 7:52p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Bruce Momjian: 4 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase