Трюк с частичным (partial) индексом

Автор оригинала: Joshua Drake
  • Перевод
  • Tutorial
На канале #postgresql сегодня прозвучал один очень интересный вопрос (изменен для читаемости):

Мне нужно создать ограничение (constraint) на таблицу. Ограничение должно проверять уникальность двух полей: строкового и логического. Однако, есть дополнительное требование. Допускается существование только одной записи с некой строкой и значением TRUE. В то же время, допускается несколько записей с некой строкой, но значением FALSE. Например, может быть несколько {«abc», false}, но только одна строка {«abc», true}.


Ну и, казалось бы, в чем важность данной задачи? Просто это очень удачный пример использования гибкости PostgreSQL, который имеет в своем активе механизм частичных индексов (partial indices). Решение простое и элегантное:

CREATE TABLE foo(bar TEXT, bing BOOLEAN);

CREATE UNIQUE INDEX baz_index ON foobar(bar, bing) WHERE bing = 't';

INSERT INTO foobar VALUES('1','t');
INSERT INTO foobar VALUES('2','t');
INSERT INTO foobar VALUES('1','f');
INSERT INTO foobar VALUES('1','f');
INSERT INTO foobar VALUES('1','t');
ERROR:  duplicate key value violates unique constraint «baz_index»

Все гениальное просто. Так-то!
Поделиться публикацией
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 30

    +12
    Именно ради таких фишечек хочется в конце концов уйти с MySQL.
    З.Ы. Хочу длинную статью по миграции с MySQL на PostgreSQL. С описанием всех возможных проблем, с которыми можно столкнуться.
      +3
      Ну, переходить надо всё-таки не из-за фишечек, а из-за каких-то серъёзных недостатков или преимуществ. А в реальной жизни вообще часто получается, что заказчик базу диктует, так как у него уже что-то есть (БД + обученные люди для её обслуживания).
        +1
        Скажу лично по своему опыту отказа от мускуля в пользу постгреса — ничего страшного и отталкивающего не произошло. Хотя так и не нашёл адекватного инструмента для конвертирования. Пришлось писать своё решение )
        В постгресе ещё полно всяких фишечек, например как я раньше жил без аналитических функций типа rank() OVER (partition by field), или такой нужной функции как generate_subscripts.
        Ну я уж не говорю про то, что постгрес на порядок, а то и два быстрее работает.
        Короче мой вам скромный совет — переходите, не бойтесь.
          0
          документации по PostgreSQL маловато, на мой скромный взгляд.
            +1
            Ну уж не знаю, по моему здесь всё описано www.postgresql.org/docs/
            Тем более, если раньше работали на мускуле, то вообще вопросов не должно возникнуть.
              +1
              Мож вы не там смотрите? Самая классная дока по моему из всех что я видел.
                0
                да, очевидно, что так. попробую почитать еще раз)
                  0
                  Лучше — только в Qt! И то местами.
                  А вот код постгри разбирать не советую, так как там чёрт ногу сломит.
                    0
                    И опять я с Вами не соглашусь. :) По сравнению с кодом Мускуля, код Постгри предел мечтаний. Что и не удивительно, впрочем, ибо Постгри изначально академическая софтина.
                      0
                      :-) Я не видел кода мускля, поэтому не могу сравнивать.
                      Но постгри… При всём своём величии и академичтости, дико разрознен.
                      В Опен Сорсе часто так бывает. Вот вы никогда не пытались влазить в ядро Linux? Я не профессионал, но та разностилица, которую я там нашёл, «немного» обускураживает. Тоже и в постгри. ИМХО.
                0
                Эм, на порядок — это уже в десять раз. :)
                  0
                  Мда, прочитал по диагонали. Сорри. :/
                  0
                  Ну я уж не говорю про то, что постгрес на порядок, а то и два быстрее работает.

                  Это вы так шутите про два порядка или у вас порядки двоичные?
                    0
                    Так и знал что щас мне про порядки влетит =)
                    Исходя из опыта нашего сайта, нагрузка на базу снизилась очень сильно (посещаемость 60 тыщ уников)
                    +1
                    >> Хотя так и не нашёл адекватного инструмента для конвертирования

                    Например вот: github.com/maxlapshin/mysql2postgres
                    +2
                    Каждый процесс миграции по своему уникален. Наверное, именно поэтому ни одно из миграционных приложений не хватает звезд с неба. Я бы обратил внимание на решение от EnterpriseDB. Они признанные миграторы.
                    –23
                    microsoft sql server 2005

                      +1
                      Не, IBM DB2.

                      Только топик, кажется, все таки о Postgres.
                        0
                        DB2 это еще тот ужос, один клиент чего стоит… я уж не говорю про его интерфейс
                        0
                        частичные индексы добавили в 2008
                        +3
                        Много лет на Постгри. Там ещё и не такие штучки делать можно…
                        На PL/SQL можно делать офигительно быстрые и удобные вещи!

                        С MySQL я столкнулся лишь однажды и больше видеть не хочу — контраст ощутим.
                        FireBird хорош, но слаб. Это фактически sqlite с PL и нормальной транзакционностью.
                        MS SQL Server… Приходилось как-то ставить и поддерживать 2005-ку. Знаете, когда инсерты перестали добавлять данные и при этом не выдавали никаких ошибок, я был очень удивлён. Оказывается размер базы вырос до максимально размера этой бесплатной версии (гиг? или два? не помню уже).
                        Oracle… И этим всё сказанно. Он, конечно, сильнее постгри, но и дороже (в бесконечное число раз, если учесть, что постгри бесплатна).

                        Единственное, что мне не нравится в постгри, это наличие кучи хлама, который она тащит с собой (географические типы, например). Было бы приятно увидеть хорошую модульность. Ну ещё отсутствие встраиваемой версии — иногда было бы офигительно удобно.
                          0
                          Эмнь, простите. Но геотипы не идут в стандартной поставке. Все что свыше, идет либо галочкой в инсталлере, либо рядышком в contrib папке.
                            0
                            В инсталляторе? Вот уж не помню, чтобы я хоть раз ставил постгри инсталлятором… Даже больше скажу, я не знал, что он есть.
                            Либо из сырцов собирал (я понимаю, что там, вполне возможно, есть ключик для configure, не собирающий гео...), либо из emerge/apt (где тоже всё сразу есть)…

                            Вообще, я гео привёл только для примера. Сейчас не вспомню, но там много всякого ненужного в девяти случаев из десяти.
                              0
                              Ну давайте, что-ли, выясним эти ненужности? :) Я не из вредности, а реально из интереса. Кстати, по итогам выяснений можно будет попробовать статью написать. Если будет про что, конечно.
                                0
                                Для этого нужно лезть в документацию. и вспоминать-разбираться.
                                Быть может позже. А то я вроде как бы работаю, а начальство моё тоже читает хабр… ;-)
                        • НЛО прилетело и опубликовало эту надпись здесь
                            0
                            Скоро сможете использовать «Exclusion Contraints», см. www.pgcon.org/2010/schedule/events/201.en.html

                            Это вам даст еще бОльшую гибкость!
                              0
                              К сожалению, автор не описал всю задачу целиком. Очень похоже, что логическое поле является признаком уникальности записи. (а может быть и нет — не хочу реверс-инженерить постановку задачи по ее решению).
                              Если это так, то почему бы не записывать в логическое поле NULL для таких строк?
                              И огород городить не придется.
                                0
                                К сожалению, узнать задачу автора вопроса у меня тоже нет никакой возможности. Но как говориться: «Ваша правда!» У каждой задачи есть несколько вариантов решения.
                                0
                                частичные индексы очень удобны при плохом распределении значений в колонке

                                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                Самое читаемое