Conceitualmente, toda expressão de um tipo de dados ordenável (collatable) tem uma ordenação. (Os tipos de dados ordenáveis ​​nativos são text, varchar e char. Os tipos-base definidos pelo usuário também podem ser marcados como ordenáveis ​​e, é claro, um domínio sobre um tipo de dados ordenável é ordenável.) Se a expressão for uma referência a coluna, a ordenação da expressão será a ordenação definida da coluna. Se a expressão for uma constante, a ordenação será a ordenação padrão do tipo de dados da constante. A ordenação de uma expressão mais complexa é derivada das ordenações de suas entradas, conforme descrito abaixo.

A ordenação de uma expressão pode ser a ordenação “padrão”, que significa as configurações de localidade definidas para o banco de dados. Também é possível que a ordenação de uma expressão seja indeterminada. Nesses casos, as operações de ordenação, e outras operações que precisam conhecer a ordenação, vão falhar.

Quando o sistema de banco de dados precisa executar uma ordenação, ou uma classificação de caracteres, ele usa a ordenação da expressão de entrada. Isto acontece, por exemplo, com as cláusulas ORDER BY, e chamadas de função ou operador como o <. A ordenação a ser aplicada em uma cláusula ORDER BY é simplesmente a ordenação da chave de classificação. A ordenação a ser aplicada para uma chamada de função, ou operador, é derivada dos argumentos, conforme descrito abaixo. Além dos operadores de comparação, as ordenações são levadas em conta por funções que fazem conversão entre letras minúsculas e maiúsculas, como lower, upper e initcap; por operadores de correspondência de padrão; e por to_char e funções relacionadas.

Para uma chamada de função ou operador, a ordenação derivada do exame das ordenações dos argumentos é usada em tempo de execução, para executar a operação especificada. Se o resultado da chamada da função, ou operador, for de um tipo de dados ordenável, a ordenação também será usada no momento da análise como a ordenação definida da função ou expressão do operador, caso haja uma expressão ao redor que exija o conhecimento de sua ordenação.

A derivação da ordenação de uma expressão pode ser implícita ou explícita. Esta distinção afeta como as ordenações são combinadas, quando várias ordenações diferentes aparecem em uma expressão. Uma derivação de ordenação explícita ocorre quando a cláusula COLLATE é usada; todas as outras derivações de ordenação são implícitas. Quando precisam ser combinadas várias ordenações como, por exemplo, em uma chamada de função, são usadas as seguintes regras:

Por exemplo, considerando-se a seguinte definição de tabela: ^[144].

CREATE TABLE test1 (
    a text COLLATE "de-DE-x-icu",
    b text COLLATE "es-ES-x-icu"
    ...
);

Então em

SELECT a < 'foo' FROM test1;

a comparação < é realizada segundo as regras de-DE-x-icu, porque a expressão combina uma ordenação derivada implicitamente com a ordenação padrão. Mas em

SELECT a < ('foo' COLLATE "fr-FR-x-icu") FROM test1;

a comparação é realizada segundo as regras fr-FR-x-icu, porque a derivação de ordenação (collation) explícita prevalece sobre a implícita. Além disso, dado

SELECT a < b FROM test1;

o analisador não consegue determinar qual ordenação aplicar, uma vez que as colunas a e b possuem ordenações implícitas conflitantes. Como o operador < precisa saber qual ordenação usar, isto resulta em erro. O erro pode ser resolvido anexando um especificador de ordenação explícito a qualquer uma das expressões de entrada, da seguinte forma:

SELECT a < b COLLATE "de-DE-x-icu" FROM test1;

ou de forma equivalente

SELECT a COLLATE "de-DE-x-icu" < b FROM test1;

Por outro lado, o caso estruturalmente semelhante

SELECT a || b FROM test1;

não resulta em erro, porque o operador || não leva em conta as ordenações: seu resultado é o mesmo, independentemente da ordenação.

A ordenação atribuída a uma função, ou às expressões de entrada combinadas do operador, também é considerada aplicável ao resultado da função ou do operador, se a função ou o operador fornecer um resultado de um tipo de dados que pode ser ordenado. Portanto, em

SELECT * FROM test1 ORDER BY a || 'foo';

a ordenação será feita segundo as regras para de-DE-x-icu. Mas a consulta a seguir

SELECT * FROM test1 ORDER BY a || b;

resulta em erro, porque mesmo que o operador || não precise conhecer a ordenação, a cláusula ORDER BY precisa. Como antes, o conflito pode ser resolvido com um especificador de ordenação explícito:

SELECT * FROM test1 ORDER BY a || b COLLATE "fr-FR-x-icu";

Uma ordenação é um objeto de esquema do SQL, que mapeia um nome do SQL para localidades fornecidas por bibliotecas instaladas no sistema operacional. Uma definição de ordenação tem um provedor, que especifica qual biblioteca fornece os dados de localidade. O nome de provedor padrão é libc, que usa as localidades fornecidas pela biblioteca C do sistema operacional. Estes são os locais usados ​​pela maioria das ferramentas fornecidas pelo sistema operacional. Outro provedor é o icu, que usa a biblioteca ICU externa. Os locais ICU só podem ser usados ​​quando o suporte para ICU estava configurado quando o PostgreSQL foi construído.

Um objeto de ordenação fornecido pela libc aponta para uma combinação de configurações LC_COLLATE e LC_CTYPE, conforme aceito pela chamada da biblioteca do sistema setlocale(). (Como o nome sugere, o objetivo principal de uma ordenação é definir LC_COLLATE, que controla a ordem de classificação. Mas, na prática, é raramente necessário ter uma configuração de LC_CTYPE diferente de LC_COLLATE, portanto é mais conveniente juntá-los sob um conceito, do que criar outra infraestrutura para definir LC_CTYPE por expressão.) Além disso, uma ordenação libc está vinculada a uma codificação de conjunto de caracteres (veja Suporte a conjunto de caracteres). O mesmo nome de ordenação pode existir para diferentes codificações.

Um objeto de ordenação fornecido pelo icu aponta para um ordenador com o nome fornecido pela biblioteca ICU. A biblioteca ICU não oferece suporte a configurações separadas de “collate” e “ctype”, portanto elas são sempre as mesmas. Além disso, as ordenações ICU são independentes da codificação, portanto sempre há apenas uma ordenação ICU com um determinado nome em um banco de dados.

SELECT collname
FROM pg_collation
WHERE collname LIKE '%BR%';

   collname
--------------
 pt_BR.utf8
 pt_BR
 es-BR-x-icu   -- Espanhol falado no Brasil
 kgp-BR-x-icu  -- Língua kaingang, falada no sul do Brasil.
 pt-BR-x-icu
 yrl-BR-x-icu  -- Língua Geral Indígena da Amazônia
(6 linhas)

SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;

CREATE COLLATION ptbr (provider = libc, locale = 'pt_BR.utf8');

$ locale -a | grep BR
pt_BR
pt_BR.utf8

CREATE COLLATION ptbricu (provider = icu, locale = 'pt-BR');

CREATE COLLATION alemão FROM "de-DE-x-icu";
CREATE COLLATION francês FROM "fr-x-icu";

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

CREATE COLLATION ignora_min_mai (provider = icu,
                                 locale = 'und-u-ks-level2',
                                 deterministic = false);
CREATE COLLATION ignora_acentos (provider = icu,
                                 locale = 'und-u-kc-ks-level1',
                                 deterministic = false);

ICU permite um controle abrangente sobre o comportamento da ordenação, definindo novas ordenações com configurações de ordenação como parte da etiqueta de linguagem. Estas configurações podem modificar a ordenação para atender a diversas necessidades. Por exemplo:

-- ignorar as diferenças de acento e letras maiúsculas/minúsculas.
CREATE COLLATION ignora_acento_min_mai (provider = icu,
                                        deterministic = false,
                                        locale = 'und-u-ks-level1');
SELECT 'Å' = 'A' COLLATE ignora_acento_min_mai; -- verdade
SELECT 'z' = 'Z' COLLATE ignora_acento_min_mai; -- verdade

-- letras maiúsculas ordenadas antes das minúsculas.
CREATE COLLATION mai_antes (provider = icu,
                            locale = 'und-u-kf-upper');
SELECT 'B' < 'b' COLLATE mai_antes; -- verdade

-- tratar os dígitos numericamente e ignorar a pontuação.
CREATE COLLATION num_ignore_punct (provider = icu,
                                   deterministic = false,
                                   locale = 'und-u-ka-shifted-kn');
SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- verdade
SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- verdade

Muitas das opções disponíveis são descritas em Configurações de ordenação para uma localidade ICU, ou veja Referências externas para ICU para obter mais detalhes.

CREATE COLLATION level3 (provider = icu,
                         deterministic = false,
                         locale = 'und-u-ka-shifted-ks-level3');
CREATE COLLATION level4 (provider = icu,
                         deterministic = false,
                         locale = 'und-u-ka-shifted-ks-level4');
CREATE COLLATION identic (provider = icu,
                          deterministic = false,
                          locale = 'und-u-ka-shifted-ks-identic');

-- separador invisível ignorado em todos os níveis, exceto em identic.
SELECT 'ab' = U&'a\2063b' COLLATE level4;  -- verdade
SELECT 'ab' = U&'a\2063b' COLLATE identic; -- falso

-- pontuação ignorada no nível 3, mas não no nível 4.
SELECT 'x-y' = 'x_y' COLLATE level3; -- verdade
SELECT 'x-y' = 'x_y' COLLATE level4; -- falso

CREATE COLLATION custom (provider = icu,
                 locale = 'und',
                 rules = '&V << w <<< W');

CREATE COLLATION ebcdic (provider = icu, locale = 'und',
rules = $$
& ' ' < '.' < '<' < '(' < '+' < \|
< '&' < '!' < '$' < '*' < ')' < ';'
< '-' < '/' < ',' < '%' < '_' < '>' < '?'
< '`' < ':' < '#' < '@' < \' < '=' < '"'
<*a-r < '~' <*s-z < '^' < '[' < ']'
< '{' <*A-I < '}' <*J-R < '\' <*S-Z <*0-9
$$);

SELECT c
FROM (VALUES ('a'), ('b'), ('A'), ('B'),
             ('1'), ('2'), ('!'), ('^')) AS x(c)
ORDER BY c COLLATE ebcdic;

 c
---
 !
 a
 b
 ^
 A
 B
 1
 2
(8 linhas)