PostgreSQL报错：含point类型的复合主键无可用比较函数

这个问题我之前帮同事排查过，本质是PostgreSQL主键背后的B-tree索引对字段类型的要求导致的——咱们一步步来拆解解决：

为什么会出现这个错误？ #

PostgreSQL的主键约束会自动创建一个唯一的B-tree索引，而B-tree索引要求索引的每一列都必须支持标准的排序比较操作（<、<=、>、>=等）。但point作为几何类型，PostgreSQL并没有为它内置这些用于排序的比较函数，所以当你把point作为复合主键的一部分时，数据库没法创建所需的B-tree索引，插入数据时就会抛出could not identify a comparison function for type point这个错误。

解决方案 #

根据你的表结构（已经有NORTH和EAST两个real类型字段对应point的坐标），推荐两种方案，优先选第一种：

方案1：用NORTH、EAST替换LOCATION作为主键字段 #

既然LOCATION本质就是NORTH（纬度）和EAST（经度）的组合，完全可以把这两个字段替换主键里的LOCATION，这样所有主键字段都是支持比较的基础类型，问题直接解决：

1. 先删除原有的主键约束（可以通过\d your_table命令查看主键名称，默认是表名_pkey）：

ALTER TABLE your_table DROP CONSTRAINT your_table_pkey;

2. 重新创建包含STATION、NORTH、EAST、SERVICE的复合主键：

ALTER TABLE your_table ADD PRIMARY KEY (STATION, NORTH, EAST, SERVICE);

3. （可选）为了保证LOCATION和NORTH/EAST的数据一致性，可以把LOCATION设为生成列，让数据库自动根据坐标生成point值：

ALTER TABLE your_table 
ALTER COLUMN LOCATION SET DATA TYPE point 
GENERATED ALWAYS AS (point(NORTH, EAST)) STORED;

这样LOCATION字段会自动同步坐标变化，不会出现数据不一致的情况。

方案2：自定义point类型的比较函数（不推荐，仅作参考） #

如果你一定要保留LOCATION在主键里，需要自定义比较逻辑和操作符类，让PostgreSQL知道如何排序point类型。比如按纬度优先、经度次之的规则排序：

1. 创建比较函数：

CREATE OR REPLACE FUNCTION point_cmp(point, point) RETURNS integer AS $$
BEGIN
    -- 先比较纬度（point的第一个元素）
    IF $1[0] < $2[0] THEN RETURN -1;
    ELSIF $1[0] > $2[0] THEN RETURN 1;
    ELSE
        -- 纬度相同则比较经度（point的第二个元素）
        IF $1[1] < $2[1] THEN RETURN -1;
        ELSIF $1[1] > $2[1] THEN RETURN 1;
        ELSE RETURN 0;
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 创建适配B-tree索引的操作符类：

CREATE OPERATOR CLASS point_cmp_ops
DEFAULT FOR TYPE point USING btree AS
    OPERATOR 1 < ,
    OPERATOR 2 <= ,
    OPERATOR 3 = ,
    OPERATOR 4 >= ,
    OPERATOR 5 > ,
    FUNCTION 1 point_cmp(point, point);

完成后重新创建主键约束，就可以正常使用了。不过这个方案比较繁琐，后续如果排序逻辑变化还需要修改函数，所以优先推荐方案1。

内容的提问来源于stack exchange，提问作者user2891462