PostgreSQL在订购时忽略破折号 [英] PostgreSQL ignores dashes when ordering
问题描述
dbname =>显示lc_collate;
lc_collate
------------
da_DK.utf8
(1行)
当我从一个表单上选择某个字符,我在字符变化列上订单时,我收到一个奇怪的行为IMO。当订购结果时,PostgreSQL将忽略前缀值的破折号,例如:
通过名称asc从mytable顺序中选择名称;
可以返回类似于
名称
----------------
广告...
Ae ...
Ag。 ..
- Ak ....
At ....
破折号前缀似乎被忽略。
我可以通过将列转换为latin1来解决此问题:
通过convert_to(name,'latin1')asc从mytable顺序中选择名称;
我得到的预期结果如下:
name
----------------
- Ak ....
广告...
Ae ...
Ag ...
At ....
为什么默认情况下,破折号前缀被忽略?可以改变这种行为吗?
在具体情况下可以使用的解决方法是用感叹号替换破折号。我碰巧知道我永远不会得到惊叹号,它会被排除在任何字母或数字之前。
从mytable中选择名称order by translate(name,' - ','!')asc
一定会影响性能我可能会考虑创建一个专门的排序列,但我真的不喜欢这个...
I have a PostgreSQL 8.4 database that is created with the da_DK.utf8 locale.
dbname=> show lc_collate;
lc_collate
------------
da_DK.utf8
(1 row)
When I select something from a table where I order on a character varying column I get a strange behaviour IMO. When ordering the result PostgreSQL ignores dashes that prefixes the value, e.g.:
select name from mytable order by name asc;
May return something like
name
----------------
Ad...
Ae...
Ag...
- Ak....
At....
The dash prefix seems to be ignored.
I can fix this issue by converting the column to latin1 when ordering:
select name from mytable order by convert_to(name, 'latin1') asc;
The I get the expected result as:
name
----------------
- Ak....
Ad...
Ae...
Ag...
At....
Why does the dash prefix get ignored by default? Can that behavior be changed?
A workaround that will work in my specific case is to replace dashes with exclamation points. I happen to know that I will never get exclamation points and it will be sorted before any letters or digits.
select name from mytable order by translate(name, '-', '!') asc
It will certainly affect performance so I may look into creating a special column for sorting but I really don't like that either...
这篇关于PostgreSQL在订购时忽略破折号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!