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
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 |