postgresql - 为 DISTINCT 调用创建 PostgreSQL 类型(点)的自定义 "equality operator"

Question

在我的一个表中，我有一列定义为 PostgreSQL 类型 point .我将其用于 earthdistance 模块——特别是 <@>距离运算符。 (是的，我知道 PostGIS，但它比我的需要复杂得多，它只是给了一个带有纬度/经度对的表格，按距离对表格进行排序，并从提供的纬度/经度中留出误差空间。)

然而，point似乎没有实现平等，所以任何 DISTINCT像SELECT DISTINCT * FROM mytable一样在 table 上打电话导致以下错误:

ERROR: could not identify an equality operator for type point

虽然修补内置类型通常是不可取的，但我不介意在这种情况下这样做，并且我尝试创建自己的 = 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
);

但是即使在创建这个之后，我也会得到同样的错误。如果不创建 =，我应该怎么做才能创建“相等运算符” ？

Answer 1

要选择不同的值，Postgres 必须能够对列进行排序。你需要创建一个完整的 btree operator class对于类型点，即五个运算符( < 、 <= 、 = 、 >= 、 > )和一个比较两个点并返回整数的函数，如 the documentation 中所述.

对于运营商=您可以使用现有功能 point_eq(point, point) :

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

运算符的示例定义 < :

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 = >);

定义运算符 <= , =>和 >以类似的方式。拥有所有五个运算符，创建一个函数:

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

最后:

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

同类point_ops已定义，您可以选择不同的点值并按点类型的列对行进行排序，例如:

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)

您还可以在点列上创建(唯一)索引。

---

更新。

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

Postgres 有超过2800 个辅助函数，支持运算符、索引、标准函数等，您可以通过查询 pg_proc , 列出它们。例如:

select oid::regprocedure as function
from pg_proc
where pronamespace::regnamespace = 'pg_catalog'::regnamespace
and proname like 'point%'

函数point_eq(point, point)用于一些 geometric functions and operators. 的实现