point types in "DISTINCT" queries

From: "Jonathan S(dot) Katz" <jonathan(dot)katz(at)excoventures(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: point types in "DISTINCT" queries
Date: 2011-06-28 22:56:14
Message-ID: 9419BBB1-4858-4DC0-9FD8-733BAC99CF03@excoventures.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am running PostgreSQL 9.0.4 and I am getting an error with a SELECT DISTINCT query that contains a point type in the SELECT clause. To be more specific, a query such as:

-- explicit declaration that it's a point type
SELECT DISTINCT a.geocode::point
FROM a
WHERE a.region = 'x';

Will return the error:

ERROR: could not identify an equality operator for type point

I read the notes about how point types do not have "=" defined for them, but "~=" aka the "same as" operator (http://www.postgresql.org/docs/9.0/static/functions-geometry.html). For points, I would treat ~= as equality. I tried creating my own equality operator based on that:

CREATE OR REPLACE FUNCTION point_equality(point, point) RETURNS bool
AS 'SELECT $1 ~= $2;'
LANGUAGE SQL;

CREATE OPERATOR = (
LEFTARG = point,
RIGHTARG = point,
PROCEDURE = point_equality,
COMMUTATOR = =
);

And when I ran the query again:

ERROR: could not identify an equality operator for type point

I looked into the mailing list archives and found a potential answer on this thread: http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php However I wanted to see if it was still necessary that I would need the complete btree operator class to run such a query. Are there plans to have a defined "=" operator on the point type? I can understand how the other geometric types, "=" would represent area, but AFAIK I think "=" could be safely applied on a point type (and i realize I could submit a patch for that :-) maybe depending on the resolution to this / refreshing my C...).

Is there possibly a relatively quick solution to this issue?

Thanks!

Jonathan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rick Genter 2011-06-28 22:57:17 Re: DROP TABLE Appears to Fail
Previous Message Rich Shepard 2011-06-28 22:53:11 Re: DROP TABLE Appears to Fail