时间戳

首页 > 解决方案 > 当字符串包含某些符号字符时，神秘的 Postgres 字符串比较结果

sql - 当字符串包含某些符号字符时，神秘的 Postgres 字符串比较结果

问题描述

当我尝试比较包含斜杠“/”或问号“？”的字符串时，我的 Postgres 服务器给了我非常混乱的结果。例如在 psql 我跑：

select ('/' < '1') as c1,
       ('/1' < '1') as c2,
       ('/////1' < '1') as c3,
       ('/1' < '2') as c4,
       ('/1' < '11') as c5;

结果是：

 c1 | c2 | c3 | c4 | c5
----+----+----+----+----
 t  | f  | f  | t  | t

所以'/'小于'1'，但'/1'大于'1'。实际上'/1'是介于'1'等'2'之间'/////1'。这不符合字典顺序。

但是，'/1'（正确地）小于11这让我更加困惑。

我想看看是否'/1'被认为是逃跑了。所以我跑了：

select length('/1');

我得到了2，这意味着 postgres 确实将'/1'其视为两个字符串。

当我/用其他符号（例如$或）替换时，也会出现同样的问题?。

如果你有 docker，这个问题很容易通过在 docker 容器中运行 postgres 来重现：

docker run postgres:11
docker exec  -it `docker ps | grep postgres:11 | cut -d' ' -f 1` psql -U postgres

然后尝试上面的 SQL。我尝试了 postgres 10 图像并且行为是相同的。

当我将 VARCHAR 列与字符串文字进行比较时，真实的 SQL 也会发生同样的事情。这个问题让我发疯，因为我需要编写正确的 SQL 来比较文件路径，这些路径显然包含许多“/”符号。

我搜索并没有找到任何谈论这个的文件，所以这看起来不像 postgres 的“官方功能”。按照字典顺序编写比较的正确方法是什么？

提前非常感谢。

标签： sqlpostgresqlcollation

解决方案

Postgres 使用操作系统的排序规则（在 Linux 上是由 提供的排序规则glibc）。因此，您的结果取决于底层操作系统。

您可以使用"C"排序规则强制进行 ASCCI 比较（就像我在上面的示例中所做的那样）：

select '/1' > '1' collate "C"

这似乎在所有平台上都一样。或者，您可以指定一个ICU排序规则，该排序规则在所有平台上都同样有效。

您提到要比较文件路径。仅对“名称”（忽略分隔符）执行此操作的一种方法是将路径转换为数组string_to_array(filepath, '/')，然后例如使用该数组进行排序或比较。

推荐阅读