Qual é o impacto do LC_CTYPE em um banco de dados PostgreSQL?

Quais são as desvantagens de usar LC_CTYPE='C' em uma localização específica

A documentação menciona a relação entre localidades e recursos SQL no Locale Support :

As configurações de localidade influenciam os seguintes recursos SQL:

• Classifique a ordem nas consultas usando ORDER BY ou os operadores de comparação padrão em dados textuais

• As funções superior, inferior e initcap

• Operadores de correspondência de padrões (expressões regulares no estilo LIKE, SIMILAR TO e POSIX); localidades afetam tanto a correspondência que não diferencia maiúsculas de minúsculas quanto a classificação de caracteres por expressões regulares de classe de caractere

• A família de funções to_char

• A capacidade de usar índices com cláusulas LIKE

O primeiro item (ordem de classificação) é sobre LC_COLLATEe os outros parecem ser sobre LC_CTYPE.

LC_COLLATE

LC_COLLATEafeta comparações entre strings. Na prática, o efeito mais visível é a ordem de classificação. LC_COLLATE='C'(ou POSIXque é um sinônimo) significa que é a ordem dos bytes que conduz as comparações, enquanto uma localidade no language_REGIONformulário significa que as regras culturais irão conduzir as comparações.

Um exemplo com nomes em francês, executados de dentro de um banco de dados UTF-8:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "fr_FR";

Resultado:

primeiro nome
-----------
 Beatriz
 berenice
 Bernardo
 boris

béatricevem antes borisde , porque o E acentuado se compara com O como se não fosse acentuado. É uma regra cultural.

Isso difere do que acontece com uma Clocalidade:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris')) 
 AS l(firstname)
order by firstname collate "C";

Resultado:

primeiro nome
-----------
 Bernardo
 boris
 Beatriz
 berenice

Agora os nomes com E acentuado são colocados no final da lista. A representação de byte éem UTF-8 é o hexadecimal C3 A9e para oele é 6f. c3é maior do que 6fisso na Clocalidade, 'béatrice' > 'boris'.

Não são apenas acentos. Existem regras mais complexas com hifenização, pontuação e caracteres estranhos como œ. Regras culturais estranhas são esperadas em cada localidade.

Agora, se as strings a serem comparadas misturam idiomas diferentes, como quando se tem uma firstnamecoluna para pessoas de todo o mundo, pode ser que qualquer localidade em particular não deva dominar, de qualquer maneira, porque alfabetos diferentes para idiomas diferentes não foram projetados para serem ordenados uns contra os outros.

Neste caso Cé uma escolha racional, e tem a vantagem de ser mais rápida, pois nada supera comparações de bytes puros.

LC_CTYPE

Definir LC_CTYPEcomo 'C' implica que C funciona como isupper(c)ou tolower(c)fornece resultados esperados apenas para caracteres no intervalo US-ASCII (ou seja, até o ponto de código 0x7F em Unicode).

Como as funções SQL como upper(), lower()ou initcap são implementadas no Postgres em cima dessas funções libc, elas são afetadas por isso assim que houver caracteres não US-ASCII nas strings.

Exemplo:

test=> show lc_ctype;
  lc_ctype   
-------------
 fr_FR.UTF-8
(1 row)

-- Good result
test=> select initcap('élysée');
 initcap 
---------
 Élysée
(1 row)

-- Wrong result
-- collate "C" is the same as if the db has been created with lc_ctype='C'
test=> select initcap('élysée' collate "C");
 initcap 
---------
 éLyséE
(1 row)

Para a Clocalidade, éé tratado como um caractere não categorizável.

Da mesma forma, resultados errados também são obtidos com expressões regulares:

test=> select 'élysée' ~ '^\w+$';
 ?column? 
----------
 t
(1 row)

test=> select 'élysée' COLLATE "C" ~ '^\w+$';
 ?column? 
----------
 f
(1 row)

Em referência à resposta aceita de Daniel sobre classificação usando agrupamentos, esteja ciente de que, se você estiver executando o PostgreSQL em um Mac, seu agrupamento preferido pode não funcionar como esperado devido a configurações inadequadas para alguns agrupamentos no nível do sistema operacional. Você pode ler mais sobre o assunto aqui:

http://www.postgresql.org/message-id/[email protected]

Este não é um problema específico do PostgreSQL, especificamente, mas sim um problema com a configuração padrão do Mac para configurações de agrupamento. Meu sistema atual está executando o PostgreSQL 9.3 no OS X El Capitan versão 10.11 e sofre com esse problema. Meu sistema retorna os mesmos resultados de consulta, independentemente de eu usar o agrupamento “fr_FR” ou “en_US”. Por exemplo:

Usando o agrupamento “fr_FR”:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
AS l(firstname)
order by firstname collate "fr_FR";

results:
==============
bernard
boris
béatrice
bérénice

Usando o agrupamento “en_US”:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
AS l(firstname)
order by firstname collate "en_US";

results:
==============
bernard
boris
béatrice
bérénice

No meu sistema, as configurações de agrupamento (no nível do sistema operacional) são as mesmas para “fr_FR” e “en_US”, conforme demonstrado no shell executando diff:

cd /usr/share/locale
diff fr_FR.UTF-8/LC_COLLATE en_US.UTF-8/LC_COLLATE

Espero que esta informação adicional seja útil para quem está lendo isso e está usando o PostgreSQL em um Mac que sofre desse problema.