postgresql - PostgreSQL 中的字母数字排序

Question

我在 Postgres 9.6 中有一个带有 character varying 列的表格:

id | column 
------------
1  |IR ABC-1
2  |IR ABC-2
3  |IR ABC-10

我看到一些解决方案将列类型转换为 bytea。

select * from table order by column::bytea.

但结果总是:

id | column 
------------
1  |IR ABC-1
2  |IR ABC-10
3  |IR ABC-2

我不知道为什么“10”总是在“2”之前。我如何对该表进行排序，假设排序的基础是字符串的最后一个整数，而不管该数字之前的字符是什么。

Answer 1

当对字符数据类型进行排序时，collation rules 适用 - 除非您使用区域设置“C”，它按字节值对字符进行排序。应用归类规则可能是可取的，也可能不是可取的。在任何情况下，它都会使排序变得更加昂贵。如果您想在没有排序规则的情况下进行排序，请不要转换为 bytea，而是使用 COLLATE "C":

SELECT * FROM table ORDER BY column COLLATE "C";

但是，这还没有解决您提到的字符串中数字的问题。拆分字符串并将数字部分排序为数字。

SELECT *
FROM   table
ORDER  BY split_part(column, '-', 2)::numeric;

或者，如果您所有的数字都适合 bigint 甚至 integer，请改用它(更便宜)。

我忽略了主要部分，因为你写道:

... the basis for ordering is the last whole number of the string, regardless of what the character before that number is.

相关:

• Alphanumeric sorting with PostgreSQL
• Split comma separated column data into additional columns
• What is the impact of LC_CTYPE on a PostgreSQL database?

通常，最好将字符串的不同部分保存在单独的列中作为相应的数据类型，以避免任何此类混淆。

如果前导字符串对于所有 列都是相同的，考虑只删除冗余噪声。您始终可以使用 VIEW 来添加要显示的字符串，或者即时执行，成本较低。