ERROR:could not identify an equality operator for type point

Question

I've defined operator = using this code:

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

However the code below still did not work and raised error like the title, what is wrong?

create or replace function skyband_get(dataset text, k integer) 
    returns point[]
as $$
declare 
        rest point[];
        collect point[];
        i integer :=0;
begin
        execute '(select array_agg('||dataset||' order by y DESC,x DESC) from '||dataset||')' into rest;
        while i<k loop
            collect := array_cat(collect,array(select * from skyband_sortedlist(rest)));
            rest := array(select * from(select * from unnest(rest) except select * from unnest(collect)) p);
            i := i + 1;
        end loop;
        return collect;
end;
$$ language plpgsql;

Answer 1

To check for equality in a UNION or EXCEPT clause, PostgreSQL uses the equality operator of the type's default operator class for the btree or the hash access method (See the documentation for an explanation of these terms).

The problem is that there is no such operator class for the type point.

You can create one yourself. You have to use the hash access method because points cannot be ordered in a reasonable fashion.

Apart from an equality operator, a hash operator class also needs a hash function for the data type, but it is easy to write one for point:

CREATE OR REPLACE FUNCTION public.hashpoint(point) RETURNS integer
   LANGUAGE sql IMMUTABLE
   AS 'SELECT hashfloat8($1[0]) # hashfloat8($1[1])';

Now you can define an operator class (you have to be superuser, because such a badly defined operator class can confuse or crash the server):

CREATE OPERATOR CLASS public.point_hash_ops DEFAULT FOR TYPE point USING hash AS
   OPERATOR 1 ~=(point,point),
   FUNCTION 1 public.hashpoint(point);

Now it should work:

VALUES (POINT '(1,1)'), (POINT '(2, 2)')
   EXCEPT
VALUES (POINT '(1,1)');

┌─────────┐
│ column1 │
├─────────┤
│ (2,2)   │
└─────────┘
(1 row)