PostgreSQL排序混合的字母数字数据
问题描述:
运行此查询:
select name from folders order by name
返回以下结果:
alphanumeric
a test
test 20
test 19
test 1
test 10
但是我期望:
a test
alphanumeric
test 1
test 10
test 19
test 20
这里出了什么问题?
答
您只需将 name
列强制转换为 bytea
数据类型,允许与整理无关的排序:
You can simply cast name
column to bytea
data type allowing collate-agnostic ordering:
SELECT name
FROM folders
ORDER BY name::bytea;
结果:
name
--------------
a test
alphanumeric
test 1
test 10
test 19
test 20
(6 rows)