PostgreSQL 排序規(guī)則支持

在概念上，一種可排序數(shù)據(jù)類型的每一種表達(dá)式都有一個(gè)排序規(guī)則（內(nèi)建的可排序數(shù)據(jù)類型是text、varchar和char。用戶定義的基礎(chǔ)類型也可以被標(biāo)記為可排序的，并且在一種可排序數(shù)據(jù)類型上的域也是可排序的）。如果該表達(dá)式是一個(gè)列引用，該表達(dá)式的排序規(guī)則就是列所定義的排序規(guī)則。如果該表達(dá)式是一個(gè)常量，排序規(guī)則就是該常量數(shù)據(jù)類型的默認(rèn)排序規(guī)則。更復(fù)雜表達(dá)式的排序規(guī)則根據(jù)其輸入的排序規(guī)則得來，如下所述：

一個(gè)表達(dá)式的排序規(guī)則可以是“默認(rèn)”排序規(guī)則，它表示數(shù)據(jù)庫的區(qū)域設(shè)置。一個(gè)表達(dá)式的排序規(guī)則也可能是不確定的。在這種情況下，排序操作和其他需要知道排序規(guī)則的操作會(huì)失敗。

當(dāng)數(shù)據(jù)庫系統(tǒng)必須要執(zhí)行一次排序或者字符分類時(shí)，它使用輸入表達(dá)式的排序規(guī)則。這會(huì)在使用例如ORDER BY子句以及函數(shù)或操作符調(diào)用（如<）時(shí)發(fā)生。應(yīng)用于ORDER BY子句的排序規(guī)則就是排序鍵的排序規(guī)則。應(yīng)用于函數(shù)或操作符調(diào)用的排序規(guī)則從它們的參數(shù)得來，具體如下文所述。除比較操作符之外，在大小寫字母之間轉(zhuǎn)換的函數(shù)會(huì)考慮排序規(guī)則，例如 lower、upper和initcap。模式匹配操作符和to_char及相關(guān)函數(shù)也會(huì)考慮排序規(guī)則。

對(duì)于一個(gè)函數(shù)或操作符調(diào)用，其排序規(guī)則通過檢查在執(zhí)行指定操作時(shí)參數(shù)的排序規(guī)則來獲得。如果該函數(shù)或操作符調(diào)用的結(jié)果是一種可排序的數(shù)據(jù)類型，萬一有外圍表達(dá)式要求函數(shù)或操作符表達(dá)式的排序規(guī)則，在解析時(shí)結(jié)果的排序規(guī)則也會(huì)被用作函數(shù)或操作符表達(dá)式的排序規(guī)則。

一個(gè)表達(dá)式的排序規(guī)則派生可以是顯式或隱式。該區(qū)別會(huì)影響多個(gè)不同的排序規(guī)則出現(xiàn)在同一個(gè)表達(dá)式中時(shí)如何組合它們。當(dāng)使用一個(gè)COLLATE子句時(shí)，將發(fā)生顯式排序規(guī)則派生。所有其他排序規(guī)則派生都是隱式的。當(dāng)多個(gè)排序規(guī)則需要被組合時(shí)（例如在一個(gè)函數(shù)調(diào)用中），將使用下面的規(guī)則：

例如，考慮這個(gè)表定義：

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

然后在

SELECT a < 'foo' FROM test1;

中，<比較被根據(jù)de_DE規(guī)則執(zhí)行，因?yàn)楸磉_(dá)式組合了一個(gè)隱式派生的排序規(guī)則和默認(rèn)排序規(guī)則。但是在

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

中，比較被使用fr_FR規(guī)則執(zhí)行，因?yàn)轱@式排序規(guī)則派生重載了隱式排序規(guī)則。更進(jìn)一步，給定

SELECT a < b FROM test1;

解析器不能確定要應(yīng)用哪個(gè)排序規(guī)則，因?yàn)?code class="structfield">a列和b列具有沖突的隱式排序規(guī)則。由于<操作符不需要知道到底使用哪一個(gè)排序規(guī)則，這將會(huì)導(dǎo)致一個(gè)錯(cuò)誤。該錯(cuò)誤可以通過在一個(gè)輸入表達(dá)式上附加一個(gè)顯式排序規(guī)則說明符來解決，因此：

SELECT a < b COLLATE "de_DE" FROM test1;

或者等效的

SELECT a COLLATE "de_DE" < b FROM test1;

在另一方面，結(jié)構(gòu)相似的情況

SELECT a || b FROM test1;

不會(huì)導(dǎo)致一個(gè)錯(cuò)誤，因?yàn)?code class="literal">||操作符不關(guān)心排序規(guī)則：不管排序規(guī)則怎樣它的結(jié)果都相同。

如果一個(gè)函數(shù)或操作符發(fā)送一個(gè)具有可排序數(shù)據(jù)類型的結(jié)果，分配給該函數(shù)或操作符的組合輸入表達(dá)式的排序規(guī)則也被考慮應(yīng)用在函數(shù)或操作符的結(jié)果。因此，在

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

中排序?qū)⒏鶕?jù)de_DE規(guī)則完成。但這個(gè)查詢：

SELECT * FROM test1 ORDER BY a || b;

會(huì)導(dǎo)致一個(gè)錯(cuò)誤，因?yàn)榧词?code class="literal">||操作符不需要知道排序規(guī)則，但ORDER BY子句需要。按照以前，沖突可以通過使用一個(gè)顯式排序規(guī)則說明符來解決：

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

排序規(guī)則是SQL模式對(duì)象，它將SQL名稱映射到操作系統(tǒng)中安裝的庫提供的語言環(huán)境。 排序規(guī)則定義中有一個(gè)提供程序， 它指定哪個(gè)庫提供語言環(huán)境數(shù)據(jù)。一個(gè)標(biāo)準(zhǔn)的提供者名稱是libc， 它使用操作系統(tǒng)C庫提供的語言環(huán)境。這些是操作系統(tǒng)提供的大多數(shù)工具使用的語言環(huán)境。 另一個(gè)提供者是icu，它使用外部ICU 庫。 只有在構(gòu)建PostgreSQL時(shí)配置了對(duì)ICU的支持，才能使用ICU區(qū)域設(shè)置。

libc提供的一個(gè)排序規(guī)則對(duì)象映射到LC_COLLATE 和LC_CTYPE設(shè)置的組合， 如setlocale()系統(tǒng)庫調(diào)用所接受的。 （正如其名字所說的，一個(gè)排序規(guī)則的主要目的是設(shè)置LC_COLLATE， 它控制排序順序。但是在實(shí)際中 LC_CTYPE設(shè)置與LC_COLLATE 不同是很少有必要的，因此通過一個(gè)概念來收集這些信息比為了設(shè)置每一個(gè)表達(dá)式的 LC_CTYPE而創(chuàng)建另一種架構(gòu)要更加方便）。此外， 一個(gè)libc排序規(guī)則是和一個(gè)字符集編碼（見第 23.3 節(jié)） 綁定在一起的。相同的排序規(guī)則名字可能存在于不同的編碼中。

由icu提供的排序規(guī)則對(duì)象映射到由ICU庫提供的指定整理器。 ICU不支持單獨(dú)的“collate”和“ctype”設(shè)置， 所以它們總是相同的。此外，ICU排序規(guī)則與編碼無關(guān)， 因此在數(shù)據(jù)庫中總是只有一個(gè)給定名稱的ICU排序規(guī)則。

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

CREATE COLLATION german (provider = libc, 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);