Início / dba / Perguntas / 115364
Accepted
orokusaki
Asked: 2015-09-18 06:55:48 +0800 CST

Por que o Postgres ORDER BY parece ignorar parcialmente os sublinhados iniciais?

  • 772

Eu tenho uma animaltabela com um name varchar(255), e adicionei linhas com os seguintes valores:

Piranha
__Starts With 2
Rhino
Starts With 1
0_Zebra
_Starts With 1
Antelope
_Starts With 1

Quando executo esta consulta:

zoology=# SELECT name FROM animal ORDER BY name;
      name       
-----------------
0_Zebra
Antelope
Piranha
Rhino
_Starts With 1
_Starts With 1
Starts With 1
__Starts With 2
(8 rows)

Observe como as linhas são classificadas em uma ordem que implica que a entrelinha _é usada para colocar as _Starts With 1linhas antes da Startslinha, mas o __no __Starts With 2parece ignorar esse fato, como se o 2no final fosse mais importante do que os dois primeiros caracteres.

Por que é isso?

Se eu classificar com Python, o resultado é:

In  [2]: for animal in sorted(animals):
   ....:     print animal
   ....:     
0_Zebra
Antelope
Piranha
Rhino
Starts With 1
_Starts With 1
_Starts With 1
__Starts With 2

Além disso, a ordem do Python sugere que os sublinhados vêm depois das letras, o que indica que a classificação do Postgres das duas primeiras _Startslinhas antes da Startslinha está incorreta.

Nota: estou usando o Postgres 9.1.15

Aqui estão minhas tentativas de encontrar o agrupamento:

zoology=# select datname, datcollate from pg_database;
  datname  | datcollate  
-----------+-------------
 template0 | en_US.UTF-8
 postgres  | en_US.UTF-8
 template1 | en_US.UTF-8
 zoology   | en_US.UTF-8
(4 rows)

E:

zoology=# select table_schema, 
    table_name, 
    column_name,
    collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
    table_name,
    ordinal_position;
 table_schema | table_name | column_name | collation_name 
--------------+------------+-------------+----------------
(0 rows)
postgresql postgresql-9.1

2 respostas

  1. Best Answer

    Como você não definiu um agrupamento diferente para sua coluna em questão, ele usa o de todo o banco de dados, que é en_US.UTF8- exatamente como na minha caixa de teste. Eu observo exatamente o mesmo comportamento, tome isso como um consolo :)

    O que vemos é aparentemente um caso de elementos de collation variáveis . Dependendo do personagem e do agrupamento, vários comportamentos diferentes são possíveis. Aqui o sublinhado (e o hífen e alguns outros também) são usados ​​apenas para desempatar - 'a' e '_a' são equivalentes na primeira rodada, então o empate entre eles é resolvido levando-se em consideração o sublinhado.

    Se você deseja classificar ignorando os sublinhados (e hífens, pontos de interrogação e exclamação no meu exemplo), pode definir uma ordem em uma expressão:

    SELECT * 
FROM (VALUES ('a'), 
             ('b1'), 
             ('_a'), 
             ('-a'), 
             ('?a'), 
             ('!a1'), 
             ('a2')
     ) t (val) 
ORDER BY translate(val, '_-?!', '');

    Em meus experimentos, adicionar um novo valor à lista geralmente altera a ordem entre itens iguais, mostrando que eles são tratados de maneira realmente igual.

    • 6

  2. A função de classificação do Python compara strings elementarmente por seus números de ponto de código Unicode - sem considerar as regras de agrupamento definidas na localidade atual (que está ativa em seu ambiente).

    Observe que os números de ponto de código Unicode dos caracteres ASCII são iguais aos números de código ASCII . E em ASCII os caracteres A-Zsão ordenados antes de _qual é ordenado antes a-z; enquanto os dígitos 0-9são ordenados antes A-Z.

    IOW, ao lidar com strings ASCII , a ordem das strings do Python é igual à ordenação lexicográfica byte-wise.

    Você obtém a mesma ordem no Postgres especificando as regras de agrupamento de localidade C com uma cláusula de agrupamento como esta:

    SELECT name FROM animal ORDER BY name COLLATE "C";

    Observe que as regras de agrupamento de outras localidades podem ser pouco intuitivas e complicadas, por exemplo, porque podem compactar vários caracteres como parte da comparação.

    • 1

relate perguntas