Пишу, по большей части, про историю, свою жизнь и немного про программирование.

PostgreSQL: сортировка без учёта регистра

Мы переводим свои некоторые продукты с «Оракла» на «Постргрес» и постоянно сталкиваемся с какими-то различиями разной величины. Сегодняшнее открытие — «Постгрес» (в отличие от «Оракла») сортирует числовые значения без учёта регистра:

test=# SELECT * FROM (SELECT 'Ба' UNION ALL SELECT 'бб' UNION ALL SELECT 'Бв') _ ORDER BY 1;
 ?column?
----------
 Ба
 бб
 Бв
(3 строки)

Ни в одном описании опыта такого же перехода я почему-то не натыкался на такую проблему. Если регистр для вас важен, то есть целых два выхода. Либо сменить принцип сравнения:

test=# SELECT * FROM (SELECT 'Ба' AS str UNION ALL SELECT 'бб' UNION ALL SELECT 'Бв') _ ORDER BY str COLLATE "C";
 str
-----
 Ба
 Бв
 бб
(3 строки)

Либо преобразовать строку к бинарному типу:

test=# SELECT * FROM (SELECT 'Ба' AS str UNION ALL SELECT 'бб' UNION ALL SELECT 'Бв') _ ORDER BY str::bytea;
 str
-----
 Ба
 Бв
 бб
(3 строки)

Правда в последнем случае неясно сможет ли «Постгрес» использовать индекс — мы пока таких испытаний не проводили.

9 комментариев
diaryea (diaryea.livejournal.com) 2015

Можно сразу хранить нужный коллейт, не забывая об особенностях lower/upper, если они нужны:
select
lower(str collate «default»)
from (select ’Ба’ collate «C» str union select ’бб’ union select ’Бв’) _
order by str;

Ну, или вылечить голову топором:
initdb -​-​no-locale

Евгений Степанищев (bolknote.ru) 2015

Комментарий для diaryea.livejournal.com:

Круто, спасибо!

ninjacolumbo@ya.ru 2015

Помимо непосредственно запросов, можно указать дефолтный COLLATE любому полю в таблице при её создании. Если он не указан, то будет использован COLLATE базы данных. И в отличие от MySQL нельзя задать COLLATE для таблицы.

Евгений Степанищев (bolknote.ru) 2015

Комментарий для ninjacolumbo@ya.ru:

Спасибо, Саш! А ты где используешь «Постгрес»?

ninjacolumbo@ya.ru 2015

В Островке использовал. Ну и в домашних проектах.

Из киллер-фич по сравнению с MySQL могу отметить очень крутую работу с JSON:
   http://www.postgresql.org/docs/9.4/static/datatype-json.html
   http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf

Евгений Степанищев (bolknote.ru) 2015

Комментарий для ninjacolumbo@ya.ru:

Для нас киллер-фичами больше всё-таки является более развитый синтаксис, быстродействие и наличие команды ANALYZE, которая выдаёт план запроса (иначе переход с «Оракла» вряд ли вообще был возможен).

И очень огорчает отсутствие AWR report.

ninjacolumbo@ya.ru 2015

Это отчёт о загруженности? Постгрес умеет собирать разную статистику: http://www.postgresql.org/docs/9.4/static/monitoring-stats.html

ninjacolumbo@ya.ru 2015

И вот ещё нашёл такую штуку: https://github.com/dalibo/pgbadger​, но сам ею не пользовался.

Евгений Степанищев (bolknote.ru) 2015

Комментарий для ninjacolumbo@ya.ru:

Не совсем. Предположим ты зашёл в мониторинг кластера и увидел, что базе 30 минут назад было плохо. Как узнать что это было? Можно снять AWR report с базы за это время и посмотреть что там происходило — какие запросы выполнялись, как работали буферы, чем была занята система I/O, если перечислять, список огромный выйдет. Данных гигантского количество.

За ссылки спасибо — посмотрю!