Postgres中不区分大小写的字母数字排序

Question

Postgres中不区分大小写的字母数字排序

28

我是postrges的新手，想要对varchar类型的列进行排序。以下示例将说明问题：

表名：testsorting

   order       name
    1            b
    2            B
    3            a
    4            a1
    5            a11
    6            a2
    7            a20
    8            A
    9            a19

大小写敏感排序（在postgres中是默认的）会给出：

select name from testsorting order by name;

    A
    B
    a
    a1
    a11
    a19
    a2
    a20
    b

不区分大小写的排序结果如下：

select name from testsorting order by UPPER(name);

如何在Postgres中进行字母数字大小写不敏感的排序，以获得以下顺序：:

我不会介意大小写字母的顺序，但顺序应为"aAbB"或"AaBb"，而不应为"ABab"。

如果您在PostgreSQL中有任何解决方案，请提出建议。

- akhi

我正在添加一个扁平化的内容：“区分大小写排序（在PostgreSQL中是默认的）”，但这并不完全正确。默认意味着“表示为数据库定义的语言环境设置。”（参考：PostgreSQL文档collation.html）。例如，在Windows上，它可能是不区分大小写的。在我的Windows 10上就是这样。 - Fabien Haddadi

6个回答

5

如果名称始终采用“1个字母和n个数字”的格式，则为：

select name
from testsorting
order by
    upper(left(name, 1)),
    (substring(name from 2) || '0')::integer

- Clodoaldo Neto

我遇到了这个错误：invalid input syntax for integer: ""。我需要检查一下排序规则吗？如果我去掉末尾的::integer，我得到的输出并不是完全排序的。a11和a19在a2之前列出，这是错误的（A、a、a1、a11、a19、a2、a20、B、b）。正确的排序顺序应该是A、a、a1、a2、a11、a19、a20、B、b。 - akhi

谢谢Clodoaldo。我之所以出现这个错误是因为空值，所以需要额外的开销来处理你建议的方法中的空值。有什么意见吗？ - akhi

@Akhilesh 空值不会产生该错误消息。请检查 select (substring(null from 2) || '0')::integer。请问确切的错误消息是什么？ - Clodoaldo Neto

啊，终于找到我想要的了。 - Abdul Baig

3

就我所知，我使用了PostgreSQL模块citext并使用数据类型CITEXT代替TEXT。它使得这些列的排序和搜索都不区分大小写。

可以使用SQL命令CREATE EXTENSION IF NOT EXISTS citext;来安装该模块。

- Alexis.Rolland

2

PostgreSQL使用C库本地化工具对字符串进行排序。C库由主机操作系统提供。在Mac OS X或BSD家族操作系统上，UTF-8本地化定义有缺陷，因此结果按照"C"排序规则。详细信息请参阅PostgreSQL维基上的常见问题解答：https://wiki.postgresql.org/wiki/FAQ。附带Ubuntu 15.04作为主机操作系统的排序结果的图片，请参见链接1。

- Manu Singhal

1

我同意Clodoaldo Neto的回答，但也不要忘记添加索引

CREATE INDEX testsorting_name on testsorting(upper(left(name,1)), substring(name from 2)::integer)

- Chris Aitchison

这是索引，而不是排序。它不会对您的列表进行排序，Clodoaldo Neto的答案会。这将使排序更有效率。 - Chris Aitchison

这个索引可能不会用于排序。据我所见，仅使用唯一索引进行排序，在 9.3 中是这样的。不过，如果有需要，我很乐意被证明错误... - Risadinha

任何B树索引都可以用于排序，无论它是否唯一：http://www.postgresql.org/docs/9.3/static/indexes-ordering.html。显然，这取决于存在反映特定查询的索引。 - Chris Aitchison

0

答案受到这个的强烈启发。
通过使用函数，如果您需要在不同的查询中使用它，保持代码整洁将更加容易。

CREATE OR REPLACE FUNCTION alphanum(str anyelement)
   RETURNS anyelement AS $$
BEGIN
   RETURN (SUBSTRING(str, '^[^0-9]*'),
      COALESCE(SUBSTRING(str, '[0-9]+')::INT, -1) + 2000000);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

然后你可以这样使用它：

SELECT name FROM testsorting ORDER BY alphanum(name);

测试：

WITH x(name) AS (VALUES ('b'), ('B'), ('a'), ('a1'),
   ('a11'), ('a2'), ('a20'), ('A'), ('a19'))
SELECT name, alphanum(name) FROM x ORDER BY alphanum(name);

 name |  alphanum   
------+-------------
 a    | (a,1999999)
 A    | (A,1999999)
 a1   | (a,2000001)
 a2   | (a,2000002)
 a11  | (a,2000011)
 a19  | (a,2000019)
 a20  | (a,2000020)
 b    | (b,1999999)
 B    | (B,1999999)

- Le Droid

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Michał Niklas · Accepted Answer

我的 PostgreSQL 按你想要的方式排序。PostgreSQL 比较字符串的方式由语言环境和排序规则决定。使用 createdb 创建数据库时有一个 -l 选项可以设置语言环境。也可以使用 psql -l 命令来检查其在你的环境中的配置：

[postgres@test]$ psql -l
List of databases
 Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
---------+----------+----------+------------+------------+-----------------------
 mn_test | postgres | UTF8     | pl_PL.UTF8 | pl_PL.UTF8 |

如您所见，我的数据库使用波兰文排序。

如果您使用其他排序创建了数据库，则可以在查询中使用其他排序方式，例如：

SELECT * FROM sort_test ORDER BY name COLLATE "C";
SELECT * FROM sort_test ORDER BY name COLLATE "default";
SELECT * FROM sort_test ORDER BY name COLLATE "pl_PL";

您可以通过以下方式列出可用的排序规则：

SELECT * FROM pg_collation;

编辑：

哦，我错过了 'a11' 必须在 'a2' 之前。

我认为标准排序无法解决字母数字混合的排序。对于这种排序，您将不得不像 Clodoaldo Neto 的回答中那样将字符串分成多个部分。另一个选项是，如果您经常需要按此方式排序，则将名称字段分成两列会很有用。您可以在插入和更新时创建触发器，将 name 分割为 name_1 和 name_2，然后：

SELECT name FROM sort_test ORDER BY name_1 COLLATE "en_EN", name_2;

我把字符集从波兰语改成了英语，你应该使用你的本地字符集来对像 aącć 等字母进行排序。