Creating a custom "equality operator" for PostgreSQL type (dot) for DISTINCT calls
In one of my tables, I have a column that is defined as a PostgreSQL type point
. I use this for earthdistance
module & mdash, specifically the distance operator <@>
. (Yes, I know about PostGIS, but it was much more complex than my needs, which is just given a table with long / long pairs, order a table by distance with room for error with lat / long provided.)
However, point
it doesn't seem to have any equality, so any call DISTINCT
on the type table SELECT DISTINCT * FROM mytable
results in the following error:
ERROR: could not identify an equality operator for type point
While it is generally impractical to fix the built-in types, I don't mind in this case, and I tried to create my own operator =
for point
:
CREATE OR REPLACE FUNCTION compare_points_equality(point1 POINT, point2 POINT)
RETURNS BOOLEAN AS $$
SELECT point1[0] = point2[0] AND point1[1] = point1[1];
$$ LANGUAGE SQL IMMUTABLE;
CREATE OPERATOR = (
LEFTARG = POINT,
RIGHTARG = POINT,
PROCEDURE = compare_points_equality,
COMMUTATOR = =,
NEGATOR = !=,
HASHES,
MERGES
);
But even after creating this, I am getting the same error. What should I do to create an "equality operator" if not =
?
source to share
To select different values, Postgres must be able to sort the column. You need to create a complete baree operator class for the type, i.e. Five operators ( <
, <=
, =
, >=
, >
), and the function of comparing the two points and returns an integer, as described in the documentation .
For the operator, =
you can use an existing function point_eq(point, point)
:
create operator = (leftarg = point, rightarg = point, procedure = point_eq, commutator = =);
An example of operator definition <
:
create function point_lt(point, point)
returns boolean language sql immutable as $$
select $1[0] < $2[0] or $1[0] = $2[0] and $1[1] < $2[1]
$$;
create operator < (leftarg = point, rightarg = point, procedure = point_lt, commutator = >);
Define operators <=
, =>
and in a >
similar way. With all five operators in place, create a function:
create function btpointcmp(point, point)
returns integer language sql immutable as $$
select case
when $1 = $2 then 0
when $1 < $2 then -1
else 1
end
$$;
Finally:
create operator class point_ops
default for type point using btree as
operator 1 <,
operator 2 <=,
operator 3 =,
operator 4 >=,
operator 5 >,
function 1 btpointcmp(point, point);
With a class definition, point_ops
you can select different point values and order strings with a point column, for example:
with q(p) as (
values
('(1,1)'::point),
('(1,2)'::point),
('(2,1)'::point),
('(1,1)'::point))
select distinct *
from q
order by 1 desc;
p
-------
(2,1)
(1,2)
(1,1)
(3 rows)
You can also create a (unique) index on the point column.
source to share