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

Question

当我尝试比较包含斜杠“/”或问号“?”的字符串时，我的 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 的“官方功能”。按照字典顺序编写比较的正确方法是什么？

非常感谢。

Answer 1

Postgres 使用操作系统的排序规则(在 Linux 上由 glibc 提供)。所以你的结果取决于底层操作系统。

• Rextester 似乎可以在 Windows 上运行，并且有比较 works as expected
• db<>fiddle 在 Debian 上运行，显示 the same behaviour如你所见
• db-fiddle.com 在 RedHat 上运行，比较 works as expected以及。

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

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

这似乎在所有平台上都一样。或者，您可以指定 ICU也将在所有平台上以相同方式工作的排序规则。

---

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