Sortowanie w PostgreSQL

Sortowanie w PostgreSQL uwzględniające zasady obowiązujące dla konkretnego języka może być dość kłopotliwe. Niestety PostgreSQL nie pozwala na dynamiczne zmienianie sposobu sortowania tak jak jest to możliwe w MySQL (klauzula COLLATE) albo w Oraclu (parametr sesji NLS_SORT), tylko wymaga podania sposobu sortowania podczas tworzenia klastra (parametry –locale, –lc-collate i poniekąd –lc-ctype polecenia initdb) i później już parametry te pozostają niezmienne.

Jest to niewątpliwa wada jeśli chciałoby się przechowywać w bazie dane w wielu językach. Sytuacje poprawia nieco fakt, że przy locale bazujących na normie ISO14651 (a jest to chyba większość locales w nowoczesnych linuxach) sortowanie w wielu językach jest w miarę poprawne bez względu na wybrane locale.

Np. w przypadku Ubuntu 7 i 8 polskie napisy sortują się poprawnie zarówno przy locale pl_PL.utf8 jak i en_US.utf8, co można sprawdzić wykonując polecenia (PostgreSQL będzie sortował identycznie przy tym samym locale):

echo -e "ą\na\nb\nc\nć\nł\nz\nż\nź" |LC_ALL=pl_PL.utf8 sort
echo -e "ą\na\nb\nc\nć\nł\nz\nż\nź" |LC_ALL=en_US.utf8 sort

Natomiast przy locale ustawionym na POSIX lub C sortowanie nie będzie poprawne, ponieważ w tym przypadku porównywane są poprostu binarne kody znaków i polskie litery lądują na końcu.

Wróćmy na razie do PostgreSQL. Jeżeli podczas wykonywania initdb nie podaliśmy explicite parametrów –locale, –lc-collate i/lub –lc-ctype to initdb pobiera wartości tych parametrów ze zmiennych systemowych LC_ALL w pierwszej kolejności, LC_COLLATE oraz LC_CTYPE jeśli LC_ALL nie jest ustawione i LANG jeśli poprzednie zmienne również nie były ustawione. Jeśli żadna z tych zmiennych nie jest ustawiona to locale jest ustawiane na C.

Czyli jeśli korzystamy z klastra stworzonego np. automatycznie przy instalacji PostgreSQL, to może się zdażyć, że locale będzie inne niż byśmy sobie tego życzyli. Najlepiej w tami wypadku klaster usunąć (po wkonaniu backupu) i stworzyć ręcznie od nowa z odpowiednim locale. W Ubuntu można się zamiast initdb posłużyć wygodniejszym pg_createcluster.

OK, ustawiliśmy sobie locale na pl_PL.utf8 i badamy dalej sortowanie (dla ułatwienia eksperymentów posługuję się dalej kombinacją poleceń echo/sort – PostgreSQL będzie sortował tak samo):

echo -e "język polski\njęzyk angielski\njęzykowy\njęzyk-language\njęzyka\njęzyk" |LC_ALL=pl_PL.utf8 sort

W efekcie dostajemy taki porządek sortowania:

język
języka
język-language
językowy
język angielski
język polski

Nie wygląda to najlepiej i nie jest zgodne z polskimi zasadami sortowania (są one dość dobrze opisane np. tutaj: http://www.ia.pw.edu.pl/~wujek/tex/idx/porzadek.html#reguly). Lepiej by było gdyby napisy zostały posortowane następująco:

język
język angielski
język polski
język-language
języka
językowy

Jak coś takiego osiągnąć? Trzeba nieco zmienić plik z definicją locale.

Pliki źródłowe definiujące zasady obowiązujące dla poszczególnych locale znajdują się w katalogu /usr/share/i18n/locales. Najlepiej nie zmieniać standardowego polskiego locale pl_PL tylko stworzyć jego inny wariant np. pl_PL@bettersort. W tym celu kopijemy plik pl_PL i zabieramy się za jego edycję.

Interesuje nas sekcja LC_COLLATE a szczególnie fragment:

reorder-after <U00A0>
<U0020> <U0020>;IGNORE;<U0020>;<U0020>

Zmieniamy go na następujący:

reorder-after <RES-1>
<U0020> <U0020>;IGNORE;<U0020>;<U0020>
<U00A0> <U0020>;IGNORE;<U0020>;<U00A0>
<U002D> <U002D>;IGNORE;<U002D>;<U002D>
<U2010> <U002D>;IGNORE;<U002D>;<U2010>
<U2011> <U002D>;IGNORE;<U002D>;<U2011>
<U2012> <U002D>;IGNORE;<U002D>;<U2012>
<U2013> <U002D>;IGNORE;<U002D>;<U2013>
<U2212> <U002D>;IGNORE;<U002D>;<U2212>

Co to oznacza? Symbol RES-1 to symbol specjalny znajdujący się w porządku sortowania przed wszystkimi innymi znakami. Polecenie reorder-after <RES-1> powoduje wstawienie w porządku sortowania symboli znajdujących się w kolejnych linijkach właśnie za symbolem RES-1, a więc na pewno przed wszystkimi innymi znakami. Dwie pierwsze linijki zaczynające się od symboli <U0020> i <U00A0> są odpowiedzialne za sortowanie zwykłej spacji i spacji nieroździlającej (nbsp). Kolejne linijki to różne wersje dywizu i myślnika.

Po wyedytowaniu tego pliku należy jeszcze dopisać go do listy obsługiwanych locale znajdującej się w pliku /usr/share/i18n/SUPPORTED. Dodajemy tam wpis:

pl_PL.UTF-8@bettersort UTF-8

Następnie trzeba skompilować nasze nowe locale poleceniem:

sudo locale-gen pl_PL.UTF-8@bettersort

Sprawdzamy jeszcze czy wszystko się udało poleceniem: locale -a i testujemy sortowanie:

echo -e "język polski\njęzyk angielski\njęzykowy\njęzyk-language\njęzyka\njęzyk" |LC_ALL=pl_PL@bettersort.utf8 sort

Powinniśmy dostać poprawne sortowanie.

Pozstaje nam jeszcze stworzenie od nowa klsatra PostgreSQL poleceniem:

sudo pg_createcluster --locale=pl_PL.utf8@bettersort --lc-messages=en_US.utf8 --start 8.3 main

W tak stworzonej bazie sortowanie powinno być poprawne.