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

在对字符数据类型进行排序时，应用 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，请使用它(更便宜)。

我忽略了主角，因为你写：

..。排序的基础是字符串的最后一个整数，而不管该数字之前的字符是什么。

相关信息：

• Alphanumeric sorting with PostgreSQL
• Split comma separated column data into additional columns
• CTYPE on a PostgreSQL database?

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

如果前导字符串对所有列都是相同的，请考虑只删除冗余噪声。您可以始终使用VIEW来预置一个字符串以供显示，也可以在运行时廉价地进行.

Answer 2

与注释中的注释一样，拆分和强制转换整数部分

select *
from
    table
    cross join lateral
    regexp_split_to_array(column, '-') r (a)
order by a[1], a[2]::integer