PostgreSQL含特殊字符的>=与<=比较行为问题及解决需求

问题原因 #

PostgreSQL的字符串比较默认依赖数据库的排序规则（collation），大多数默认规则（如en_US.UTF-8）会将符号~的排序优先级放在小写字母之前；但Firestore、JavaScript、Rust等环境默认采用ASCII码值排序，~的ASCII码（126）大于小写字母z（122），因此会得到c28xzxe29h <= 'c28x~'为true的结果，两者排序逻辑不一致导致匹配失败。

解决方案 #

1. 查询时显式指定ASCII排序规则 #

在比较语句中添加COLLATE "C"，强制PostgreSQL使用ASCII码值排序：

SELECT 'c28xzxe29h' <= 'c28x~' COLLATE "C";
-- 返回 true

对应业务查询写法：

SELECT * FROM your_table WHERE geoHash <= 'c28x~' COLLATE "C";

2. 修改geoHash列的默认排序规则 #

如果需要长期统一排序逻辑，可直接修改列的排序规则为"C"：

ALTER TABLE your_table ALTER COLUMN geoHash TYPE text COLLATE "C";

后续查询无需每次指定collate，默认会用ASCII规则比较。

3. 替换~为Geohash字符集外的高优先级字符 #

Geohash的字符集是0123456789bcdefghjkmnpqrstuvwxyz，最大字符为z（ASCII 122）。可将geofire-commons生成的~替换为ASCII码更大的字符（如chr(126)本身，或其他符合要求的符号），但此方法可靠性不如前两种，仅作为备选。

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