Creating custom "equality operator" for PostgreSQL type (point) for DISTINCT calls

Question

In one of my tables, I have a column that is defined as the PostgreSQL type point. I use this for the earthdistance module—specifically, the <@> distance operator. (Yes, I know about PostGIS, but it was far more complex than my needs, which is simply given a table with lat/long pairs, order the table by distance with room for error from a provided lat/long.)

However, point appears to have no equality implemented, so any DISTINCT call on the table like SELECT DISTINCT * FROM mytable results in the following error:

ERROR: could not identify an equality operator for type point

Though it's generally unadvisable to patch built-in types, I don't mind in this case doing so, 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 get the same error. What am I supposed to do to create the "equality operator" if not create =?

Answer 1

To select distinct values Postgres must have the ability to sort the column. You need to create a complete btree operator class for type point, i.e. five operators (<, <=, =, >=, >) and a function comparing two points and returning integer, as it is described in the documentation.

For the operator = you can use the existing function point_eq(point, point):

create operator = (leftarg = point, rightarg = point, procedure = point_eq, commutator = =);

Example definition of operator <:

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 the operators <=, => and > in a similar way. Having all five operators, 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
$$;

And 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 the class point_ops defined you can select distinct point values and order rows by the column of type point, e.g.:

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 (unique) index on a point column.

---

Update.

Where the function point_eq(point, point) comes from? Why does it already exist?

Postgres has over 2800 auxiliary functions that support operators, indexes, standard functions, etc. You can list them by querying pg_proc, e.g.:

select format('%s(%s)', proname, pg_get_function_arguments(oid))
from pg_proc
where pronamespace::regnamespace = 'pg_catalog'
and proname like 'point%'

The function point_eq(point, point) is used in implementation of some geometric functions and operators.