Conceptualmente, cada expresión de un tipo de datos ordenable tiene un ordenamiento. (Los tipos de datos ordenables integrados son text, varchar y char. Los tipos base definidos por el usuario también se pueden marcar como ordenables, y por supuesto un dominio sobre un tipo de datos ordenable es ordenable). Si la expresión es una referencia a una columna, el ordenamiento de la expresión es el ordenamiento definido de la columna. Si la expresión es una constante, el ordenamiento es el ordenamiento por defecto del tipo de datos de la constante. El ordenamiento de una expresión más compleja se deriva de los ordenamientos de sus entradas, como se describe a continuación.

El ordenamiento de una expresión puede ser el ordenamiento «por defecto» (default), lo que significa los ajustes de configuración regional definidos para la base de datos. También es posible que el ordenamiento de una expresión sea indeterminado. En tales casos, las operaciones de ordenamiento y otras operaciones que necesiten conocer el ordenamiento fallarán.

Cuando el sistema de base de datos tiene que realizar una ordenación o una clasificación de caracteres, utiliza el ordenamiento de la expresión de entrada. Esto sucede, por ejemplo, con las cláusulas ORDER BY y las llamadas a funciones u operadores como <. El ordenamiento a aplicar para una cláusula ORDER BY es simplemente el ordenamiento de la clave de clasificación. El ordenamiento a aplicar para una llamada a una función u operador se deriva de los argumentos, como se describe a continuación. Además de los operadores de comparación, las ordenaciones son tenidas en cuenta por las funciones que convierten entre letras mayúsculas y minúsculas, como lower, upper e initcap; por los operadores de coincidencia de patrones; y por to_char y funciones relacionadas.

Para una llamada a una función u operador, el ordenamiento que se deriva al examinar las ordenaciones de los argumentos se utiliza en tiempo de ejecución para realizar la operación especificada. Si el resultado de la llamada a la función u operador es de un tipo de datos ordenable, el ordenamiento también se utiliza en tiempo de análisis como el ordenamiento definido de la expresión de la función u operador, en caso de que haya una expresión circundante que requiera el conocimiento de su ordenamiento.

La derivación de ordenamiento (collation derivation) de una expresión puede ser implícita o explícita. Esta distinción afecta a cómo se combinan las ordenaciones cuando aparecen múltiples ordenaciones diferentes en una expresión. Una derivación de ordenamiento explícita ocurre cuando se utiliza una cláusula COLLATE; todas las demás derivaciones de ordenamiento son implícitas. Cuando es necesario combinar múltiples ordenaciones, por ejemplo en una llamada a una función, se utilizan las siguientes reglas:

Por ejemplo, considera esta definición de tabla:

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);

Luego en:

SELECT a < 'foo' FROM test1;

la comparación < se realiza de acuerdo con las reglas de de_DE, porque la expresión combina una ordenación derivada implícitamente con la ordenación por defecto. Pero en:

SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

la comparación se realiza utilizando las reglas de fr_FR, porque la derivación de ordenamiento explícita anula la implícita. Además, dado:

SELECT a < b FROM test1;

el analizador no puede determinar qué ordenamiento aplicar, ya que las columnas a y b tienen ordenaciones implícitas en conflicto. Dado que el operador < sí necesita saber qué ordenamiento utilizar, esto dará como resultado un error. El error se puede resolver añadiendo un especificador de ordenamiento explícito a cualquiera de las expresiones de entrada, de esta manera:

SELECT a < b COLLATE "de_DE" FROM test1;

o de manera equivalente:

SELECT a COLLATE "de_DE" < b FROM test1;

Por otro lado, el caso estructuralmente similar:

SELECT a || b FROM test1;

no produce un error, porque al operador || no le importan las ordenaciones: su resultado es el mismo independientemente de la ordenación.

También se considera que el ordenamiento asignado a las expresiones de entrada combinadas de una función u operador se aplica al resultado de la función u operador, si este entrega un resultado de un tipo de datos ordenable. Así, en:

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

la ordenación se realizará de acuerdo con las reglas de de_DE. Pero esta consulta:

SELECT * FROM test1 ORDER BY a || b;

produce un error, porque aunque el operador || no necesita saber una ordenación, la cláusula ORDER BY sí. Como antes, el conflicto se puede resolver con un especificador de ordenamiento explícito:

SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";

Una ordenación es un objeto de esquema SQL que mapea un nombre SQL a las configuraciones regionales proporcionadas por las bibliotecas instaladas en el sistema operativo. La definición de una ordenación tiene un proveedor (provider) que especifica qué biblioteca proporciona los datos de la configuración regional. Un nombre de proveedor estándar es libc, que utiliza las configuraciones regionales proporcionadas por la biblioteca C del sistema operativo. Estas son las configuraciones regionales utilizadas por la mayoría de las herramientas proporcionadas por el sistema operativo. Otro proveedor es icu, que utiliza la biblioteca externa ICU. Las ordenaciones de ICU solo se pueden utilizar si se configuró el soporte para ICU cuando se compiló PostgreSQL.

Un objeto de ordenación proporcionado por libc se mapea a una combinación de los ajustes LC_COLLATE y LC_CTYPE, tal como los acepta la llamada a la biblioteca del sistema setlocale(). (Como el nombre indica, el propósito principal de un ordenamiento es establecer LC_COLLATE, que controla el orden de clasificación. Pero en la práctica rara vez es necesario tener un ajuste de LC_CTYPE que sea diferente de LC_COLLATE, por lo que es más conveniente agruparlos bajo un solo concepto que crear otra infraestructura para establecer LC_CTYPE por expresión). Además, un ordenamiento de libc está vinculado a una codificación de juego de caracteres (consulta Section 23.3). El mismo nombre de ordenamiento puede existir para diferentes codificaciones.

Un objeto de ordenación proporcionado por icu se mapea a un ordenador con nombre proporcionado por la biblioteca ICU. ICU no admite ajustes de «collate» y «ctype» separados, por lo que siempre son iguales. Además, las ordenaciones de ICU son independientes de la codificación, por lo que siempre hay una sola ordenación de ICU con un nombre dado en una base de datos.

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

CREATE COLLATION german (provider = libc, locale = 'de_DE');

CREATE COLLATION german (provider = icu, locale = 'de-DE');

CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";

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

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

ICU permite un control amplio sobre el comportamiento de la ordenación al definir nuevas ordenaciones con ajustes de ordenación como parte de la etiqueta de idioma. Estos ajustes pueden modificar el orden de clasificación para adaptarse a una variedad de necesidades. Por ejemplo:

-- ignorar las diferencias en los acentos y las mayúsculas/minúsculas
CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true

-- las letras mayúsculas se ordenan antes que las minúsculas.
CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper');
SELECT 'B' < 'b' COLLATE upper_first; -- true

-- tratar los dígitos numéricamente e ignorar la puntuación
CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn');
SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true

Muchas de las opciones disponibles se describen en Section 23.2.3.2, o bien consulta Section 23.2.3.5 para obtener más detalles.

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 invisible ignorado en todos los niveles excepto en identic
SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false

-- puntuación ignorada en level3 pero no en level 4
SELECT 'x-y' = 'x_y' COLLATE level3; -- true
SELECT 'x-y' = 'x_y' COLLATE level4; -- false

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