postgres postgresql collation
Uma colação é um conjunto de regras que definem a ordenação e igualdade de dados (normalmente textuais, mas não exclusivamente textuais). Esse conjunto de regras é variável em função da codificação, língua, contexto e outras regras semânticas presentes no domínio da aplicação e dos dados. Portanto, é necessário que as colações sejam criáveis e configuráveis para que as aplicações possam tratar os dados corretamente.
Por exemplo, uma placa de carro 'ABC-0123' deveria ser idêntica à mesma placa 'abc0123', independentemente da proveniência dos dois valores, ou seja, se foram armazenados no banco, digitados pelo usuário ou trazidos de outro sistema por API. Portanto, nesse exemplo uma comparação deveria ignorar a caixa das letras e pontuações. Outros exemplos similares são todo tipo de códigos, como RG, CPF, CEP, UF, entre muitos outros.
Outros casos são ainda mais complexos, incluindo e-mails, nomes próprios e textos gerais, nos quais também acentos (jose vs. josé) e representações alternativas de caracteres (ae vs. æ, ss vs. ẞ) precisam ser considerados.
E a ordem das respostas também pode ser controlada, produzindo resultados variando a ordem em função de minúsculas (m), maiúsculas (M), dígitos (d), números completos (N), assim como pontuação (p) e outros fatores, o que produz qualquer ordem desejada:
| p<M<m<d | m<M<N<p | M<m<p<N | m<M<d<p | N<M<n<p |
|---|---|---|---|---|
| ; | a | A | a | 1 |
| A | A | a | A | 2 |
| a | 1 | ; | 1 | 10 |
| 1 | 2 | 1 | 10 | A |
| 10 | 10 | 2 | 2 | a |
| 2 | ; | 10 | ; | ; |
A colação mais simples é conhecida como C (ou POSIX em alguns contextos), e compara os caracteres como os próprios valores numéricos dos seus bytes. Muitas distribuições incluem uma colação C.UTF-8, na qual os caracteres são comparados pelos seus respectivos codepoints no Unicode (codepoints não são necessariamente iguais aos bytes do texto armazenado).
A lista completa de colações depende da distribuição. O PostgreSQL importa as
colações que encontra em tempo de initdb, cuja lista pode ser consultada pela
visão pg_collation ou pelo metacomando \dOS.
As colações mais simples são chamadas determinísticas, que não conseguem lidar com múltiplas representações dos mesmos dados e, portanto, fazem apenas comparações diretas de memória. Isso significa que deve existir, obrigatoriamente, um mapeamento de um para um entre a representação binária do texto e seu significado no domínio da aplicação. Nesse caso, algumas representações binárias são consideradas "inválidas" ou, ao menos, indesejadas, e costuma-se aplicar uma função de normalização, normalmente irreversível, para transformá-las em uma representação "válida". É apenas essa representação normalizada que será comparada byte-a-byte usando a colação determinística.
Em resumo, a colação determinística é pouco inteligente e necessita que seja aplicada uma função de normalização aos dados antes de serem comparados. Portanto, uma comparação simples não pode ser escrita apenas como:
WHERE coluna = valor
Mas sim como:
WHERE funcao_normalizacao(coluna) = funcao_normalizacao(valor)
As desvantagens dessa estratégia são (a) o fato de que os dados precisam ser tratados manualmente na entrada (ou indexados com um índice funcional, ou guardados em uma coluna extra, atualizada por um gatilho que aplica a função, ou, recentemente usando uma coluna gerada); e (b) toda consulta também precisa conhecer e usar a função de normalização no valor de entrada.
A vantagem é que a comparação é relativamente rápida, já que é comparação direta dos bytes do texto normalizado, tanto no índice quanto na coluna original.
Historicamente, essa também era a única opção possível no PostgreQL, portanto vemos muito código como:
WHERE lower(email) = lower('fulano@dominio.br')
Uma normalização é uma etapa ou função necessária para as colações
determinísticas. Diversas funções podem ser usadas para alcançar esse objetivo e
é comum, inclusive, que seja necessário usar mais de uma função para alcançar a
normalização necessária para o campo, já que existem funções para normalização
Unicode (NFC, NFD, NFKC e NFKD), de caixa
(lower(), upper() e de case fold em algumas linguagens), remoção de
acentos (unaccent()), remoção de caracteres no início e fim de textos
(trim(), ltrim(), rtrim()), substituição de padrões (regexp_replace()) e
diversas outras.
Usando o exemplo da placa de carro, temos várias possíveis representações de
entrada para os textos com a mesma placa. Mas ao usar uma colação
determinística, que depende de uma representação binária única, devemos aplicar
uma função que transforma todas elas nessa forma normalizada única, por
exemplo, com a função regexp_replace(lower(valor),'[^a-z0-9]', '', 'g'):
| original | aceitável? | normalizada |
|---|---|---|
| abc0123 | Sim | abc0123 |
| ABC-0123 | Não | abc0123 |
| ABC0123 | Não | abc0123 |
| abc-0123 | Não | abc0123 |
| Abc0123 | Não | abc0123 |
| abc//0123 | Não | abc0123 |
| [abc-01-23] | Não | abc0123 |
| A B C 0 1 2 3 | Não | abc0123 |
| ... | ... | ... |
Contudo, é claro que esse tipo de tratamento dificulta muito o seu uso pela aplicação e por todo usuário, que agora deve sempre consultar a coluna como:
WHERE regexp_replace(lower(coluna),'[^a-z0-9]', '', 'g') = regexp_replace(lower(valor),'[^a-z0-9]', '', 'g')
Adicionalmente, muitas dessas funções são insuficientes e, às vezes,
completamente incorretas para alguns dados de entrada. A função unaccent()
pode cobrir os acentos latinos do seu caso de testes, mas não os acentos eslavos
que o seu usuário final coloca no ambiente produtivo. As funções lower() e
upper() não conseguem tratar algumas letras cuja caixa é intransitiva, ou
seja, lower(upper(col)) != lower(col), como ss e ẞ, razão pela qual toda
linguagem de programação tem casefold() e equivalentes, além das duas
primeiras funções. E mesmo se o programador se esforçar para fazer uma função
customizada e completa, é provável que não consiga algo que esteja certa para
sempre, já que o Unicode muda continuamente. Por fim, seria um trabalho extra
bastante grande se for necessário implementar uma ordenação numérica natural (1
< 2 < 10), diferente da ordenação dos caracteres dos dígitos (1 < 10 < 2).
Portanto, é desejável encontrar uma forma mais inteligente de fazer as comparações corretamente para todo texto. Se possível, essa forma deve também simplificar as consultas, evitar o índice funcional, evitar a duplicação do dado original, permitir que o dado original sem transformação seja armazenado como fornecido pelo usuário (o que é vital para nomes próprios, e-mails e outros casos similares) e sobreviver à prova do tempo.
Se uma colação determinística é aquela na qual cada significado real é mapeado para exatamente uma representação binária, que é comparada de forma simples e direta, uma colação não determinística é o oposto, ou seja, é quando cada significado real pode ter mais de uma representação binária aceitável e, portanto, a comparação é mais complexa, mas também mais correta para alguns tipos de dados. Isso significa que todas as formas de entrada dos dados são válidas e não precisamos de funções de normalização.
No exemplo da placa de carro, não faremos a normalização, já que todas as entradas são comparáveis como estão:
| original | aceitável? |
|---|---|
| abc0123 | Sim |
| ABC-0123 | Sim |
| ABC0123 | Sim |
| abc-0123 | Sim |
| Abc0123 | Sim |
| abc//0123 | Sim |
| [abc-01-23] | Sim |
| A B C 0 1 2 3 | Sim |
| ... | ... |
E a consulta é escrita de forma simples, já que esconde a complexidade da colação não determinística na criação da coluna:
WHERE coluna = valor
Mas para alcançar esse resultado, precisamos criar uma colação com o comportamento desejado e associá-la à coluna, o que vai ser feito mais adiante.
As vantagens dessa estratégia são (a) o fato de que os dados originais são mantidos, o que é muito necessário para nomes próprios, logradouros, e-mails e outros campos; (b) nenhum espaço extra é usado para armazenamento; (c) nenhum índice funcional é necessário; (d) não é necessário gatilho ou coluna gerada; (e) a consulta se torna simples e à prova de erro mesmo para usuários e terceiros consultando diretamente a tabela; (f) não é necessário escrever a função de normalização; (g) funciona para todos os casos em que a ICU cobre, incluindo línguas, acentos e pontuações que estão sendo continuamente adicionados no Unicode.
As desvantagens são que (a) a comparação é mais complexa e, portanto, mais lenta; (b) índices sobre colações não determinísticas não podem ser usados para atender consultas com expressões regulares (mas é possível criar um índice adicional em outra colação e usá-lo).
Obs.: Apesar da longa lista de vantagens, essa estratégia não é ideal para todo tipo de dado. Especialmente em campos cujos valores são bem conhecidos ou que têm formatos bem conhecidos, como no próprio caso de placa de carro (e RG, CPF, CEP, UF), a normalização dos dados na entrada por gatilhos e/ou a validação (CHECK) de que foram fornecidos no formato correto, aliado à colação C, é a solução com comportamento correto e melhor desempenho. Por outro lado, o caso da comparação de e-mails é um bom uso de colações não determinísticas, já que é vital que o texto original seja armazenado sem normalização e usado posteriormente para todos os envios (por segurança, alguns filtros de spam usam a capitalização do endereço de destinatário, junto com outros fatores, como indicativo de que o endereço vazou para listas de spam; outros usam o separador + com informação adicional para o mesmo propósito).
Colações (determinísticas e não determinísticas) são criadas com o comando CREATE COLLATION. Elas devem ser criadas com base em outras colações; e as customizações disponíveis dependem do provedor. O provedor é a biblioteca usada, que pode ser a libc (biblioteca C do sistema) ou a ICU, mas apenas a ICU permite colações não determinísticas e outras customizações interessantes.
O formato das customizações é complexo e definido em http://userguide.icu-project.org/collation, então alguns exemplos podem ser mais claros:
-
und-u-ks-level2/und@colStrength=secondary: insensível apenas a caixa -
und-u-ks-level1/und@colStrength=primary: insensível a caixa e a acentos -
und-u-ks-level1-kc-true/und@colStrength=primary;caseLevel=on: insensível apenas a acentos -
und-u-ka-shifted/und@colAlternate=shifted: ignora pontuação -
und-u-ks-level4-ka-shifted/und@colStrength=quaternary;colAlternate=shifted: compara pontuações equivalentes, como a elipse (os três pontinhos): ... (U+002E U+002E U+002E) e … (U+2026) -
pt-u-kn-true/pt@numericOrdering=on: colação português com ordenação numérica natural
Para criar uma colação determinística como variação de uma colação existente, usamos o nome da colação original, seguido dos modificadores:
[[local]:5432] postgres@postgres=# CREATE COLLATION pt_num (PROVIDER = icu, locale = 'pt-u-kn-true');
Para criar uma colação não determinística, usamos a colação 'und' como base, aplicando os modificadores desejados e informando ao PostgreSQL que ela será não determinística:
[[local]:5432] postgres@postgres=# CREATE COLLATION ignora_caixa (PROVIDER = icu, LOCALE = 'und-u-ks-level2', DETERMINISTIC = false); [[local]:5432] postgres@postgres=# CREATE COLLATION ignora_acentos (PROVIDER = icu, LOCALE = 'und-u-ks-level1-kc-true', DETERMINISTIC = false); [[local]:5432] postgres@postgres=# CREATE COLLATION ignora_ambos (PROVIDER = icu, LOCALE = 'und-u-ks-level1', DETERMINISTIC = false);
Depois podemos usar a colação criada na criação da tabela ou nas comparações das consultas. Seguindo o exemplo da placa:
[[local]:5432] postgres@postgres=# CREATE COLLATION placa (PROVIDER = icu, LOCALE = 'und-u-ks-level2-ka-shifted', DETERMINISTIC = false);
Em uma consulta:
[[local]:5432] postgres@postgres=# SELECT 'abc0123' = 'ABC-0123' COLLATE placa AS "iguais?"; ╔═════════╗ ║ iguais? ║ ╠═════════╣ ║ t ║ ╚═════════╝
Ou na coluna da própria tabela, o que permite que seja usada em todas as consultas sobre aquela coluna:
[[local]:5432] postgres@postgres=# CREATE TABLE automovel ( [[local]:5432] postgres@postgres-# -- ... [[local]:5432] postgres@postgres-# placa TEXT COLLATE placa, [[local]:5432] postgres@postgres-# -- ... [[local]:5432] postgres@postgres-# );
Que aparece como:
[[local]:5432] postgres@postgres=# \d automovel
Table "public.usuarios"
╔════════╤══════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │ Type │ Collation │ Nullable │ Default ║
╠════════╪══════╪═══════════╪══════════╪═══════════════════════════════╣
║ ... │ ... │ │ │ ║
║ placa │ text │ placa │ │ ║
║ ... │ ... │ │ │ ║
╚════════╧══════╧═══════════╧══════════╧═══════════════════════════════╝
Que retorna o resultado esperado em consultas simples como:
[[local]:5432] postgres@postgres=# INSERT INTO automovel(placa) VALUES ('abc-0123');
[[local]:5432] postgres@postgres=# SELECT placa
[[local]:5432] postgres@postgres-# FROM automovel
[[local]:5432] postgres@postgres-# WHERE placa = 'aBC--0 123';
╔══════════╗
║ placa ║
╠══════════╣
║ abc-0123 ║
╚══════════╝
E também permite criar restrições de unicidade sobre a coluna:
[[local]:5432] postgres@postgres=# ALTER TABLE automovel
[[local]:5432] postgres@postgres-# ADD CONSTRAINT placa_unica
[[local]:5432] postgres@postgres-# UNIQUE (placa);
[[local]:5432] postgres@postgres=# INSERT INTO automovel(placa) VALUES ('ABC 0 1 2 3');
ERROR: duplicate key value violates unique constraint "placa_unica"
DETAIL: Key (placa)=(ABC 0 1 2 3) already exists.
Atenção: Todo índice sobre colações é criado usando as APIs disponibilizadas pela biblioteca da colação (libc ou ICU). Mas essas bibliotecas são continuamente atualizadas, recebendo tanto correções de bugs quanto novos blocos Unicode e atualizações nas propriedades de cada código. Isso significa que as APIs podem trazer valores diferentes em versões diferentes das bibliotecas. Portanto, se um índice foi construído com uma versão de biblioteca, é possível que seja necessário reconstruí-lo após o upgrade da biblioteca, do sistema operacional e do PostgreSQL. Quando em dúvida, confira as notas de lançamento desses componentes antes de aplicar as atualizações.