Обсуждение: Re: [pgsql-ru-general] Частично уникальное поле

Пользователь вводит некие данные.

Если он их не вводил, там NULL. Если он вводит эти данные, то они
должны быть уникальными между всеми введенными данными.

что-то я не пойму как построить ограничение, которое будет отслеживать
уникальность только между теми значениями что не NULL.

индекс построил

CREATE UNIQUE INDEX "tbli" ON "tbl" ("col") WHERE "col" IS NOT NULL;

а как построить CONSTRAINT? не хочется RULE приделывать

2011/3/13 Dmitry E. Oboukhov <unera@debian.org>

Пользователь вводит некие данные.

Если он их не вводил, там NULL. Если он вводит эти данные, то они
должны быть уникальными между всеми введенными данными.

что-то я не пойму как построить ограничение, которое будет отслеживать
уникальность только между теми значениями что не NULL.

индекс построил

CREATE UNIQUE INDEX "tbli" ON "tbl" ("col") WHERE "col" IS NOT NULL;

а как построить CONSTRAINT? не хочется RULE приделывать

UNIQUE INDEX  это уже и есть ограничение целостности (constraint).

IS NOT NULL можно смело опустить, т.к. проверки на уникальность в юник-индексе и так всегда проводятся по NOT NULL значениям.

2011/3/13 Dmitry E. Oboukhov <unera@debian.org>

Пользователь вводит некие данные.

Если он их не вводил, там NULL. Если он вводит эти данные, то они
должны быть уникальными между всеми введенными данными.

что-то я не пойму как построить ограничение, которое будет отслеживать
уникальность только между теми значениями что не NULL.

индекс построил

CREATE UNIQUE INDEX "tbli" ON "tbl" ("col") WHERE "col" IS NOT NULL;

а как построить CONSTRAINT? не хочется RULE приделывать

UNIQUE INDEX  это уже и есть ограничение целостности (constraint).

IS NOT NULL можно смело опустить, т.к. проверки на уникальность в юник-индексе и так всегда проводятся по NOT NULL значениям.

13 марта 2011 г. 2:00 пользователь Nikolay Samokhvalov <samokhvalov@gmail.com> написал:

UNIQUE INDEX  это уже и есть ограничение целостности (constraint).

А вот это не так. Ограничение уникальности (UNIQUE CONSTRAINT)
является не индексом, а его зависимостью. Т.е. невозможно удалить
уникальный индекс, порожденный неявно при создании ограничения
уникальности.
Видимо, именно такой объект и хочет создать автор. Однако следует
иметь в виду, что индекс, автоматически создаваемый при создании
ограничения уникальности будет не частичным.

2011/3/13 Dmitriy Igrishin <dmitigr@gmail.com>

13 марта 2011 г. 2:00 пользователь Nikolay Samokhvalov <samokhvalov@gmail.com> написал:

UNIQUE INDEX  это уже и есть ограничение целостности (constraint).

А вот это не так. Ограничение уникальности (UNIQUE CONSTRAINT)
является не индексом, а его зависимостью. Т.е. невозможно удалить
уникальный индекс, порожденный неявно при создании ограничения
уникальности.
Видимо, именно такой объект и хочет создать автор. Однако следует
иметь в виду, что индекс, автоматически создаваемый при создании
ограничения уникальности будет не частичным.

Приветствую ценителя теории )) 

Уникальный индекс -- это уже и есть ограничение целостности. Можно ещё раз повторю? И ещё, на ночь, чтобы запомнилось.

Где тут "не так"? ))

Для новичков нужно запоминать именно так, чтобы как раз и не возникало вопросов "а как создать constraint". 

Про размер индекса -- верно, частичный индекс экономит место, если NULL-ов ожидается много. Но при этом надо понимать, что будут последствия для оптимайзера (всё же это индекс и он может пригодиться не только как ограничение целостности).

По поводу что-то не так делаю и спать пора -- желательно привести пример, показывающий суть вопроса (что за одинаковые 'col'). Может, там не в collation дело, а в какой-нибудь кириллической буковке, смахивающей на латинницу )

Приветствую,

13 марта 2011 г. 2:54 пользователь Nikolay Samokhvalov <samokhvalov@gmail.com> написал:

Уникальный индекс -- это уже и есть ограничение целостности. Можно ещё раз повторю? И ещё, на ночь, чтобы запомнилось.

... 

or group of columns used in the constraint.". Надеюсь, что после этого Вы не станете
утверждать, что ограничение уникальности является уникальным (btree) индексом.
Я всегда ратовал и буду ратовать за корректность.

Для новичков нужно запоминать именно так, чтобы как раз и не возникало вопросов "а как создать constraint". 

Ой, да ладно про "спец курсы для новичков". :-) 

Про размер индекса -- верно, частичный индекс экономит место, если NULL-ов ожидается много. Но при этом надо понимать, что будут последствия для оптимайзера (всё же это индекс и он может пригодиться не только как ограничение целостности).

Какие такие последствия?

2011/3/13 Dmitriy Igrishin <dmitigr@gmail.com>

Приветствую,

13 марта 2011 г. 2:54 пользователь Nikolay Samokhvalov <samokhvalov@gmail.com> написал:

Уникальный индекс -- это уже и есть ограничение целостности. Можно ещё раз повторю? И ещё, на ночь, чтобы запомнилось.

... 

or group of columns used in the constraint.". Надеюсь, что после этого Вы не станете
утверждать, что ограничение уникальности является уникальным (btree) индексом.
Я всегда ратовал и буду ратовать за корректность.

Ни разу такого не утверждал.

Раз любите корректность, включите ещё логику)

Из "А является Б" вовсе не следует утверждение "Б является А".

 

Для новичков нужно запоминать именно так, чтобы как раз и не возникало вопросов "а как создать constraint". 

Ой, да ладно про "спец курсы для новичков". :-) 

Не смешно. Порог входа у Постгреса очень высокий. И чем больше мы стараемся умничать, тем выше он становится.

 

Про размер индекса -- верно, частичный индекс экономит место, если NULL-ов ожидается много. Но при этом надо понимать, что будут последствия для оптимайзера (всё же это индекс и он может пригодиться не только как ограничение целостности).

Какие такие последствия?

Берём две таблички

m=# \d a

       Table "public.a"

 Column |  Type   | Modifiers 

--------+---------+-----------

 num    | integer | 

 txt    | text    | 

Indexes:

    "a_txt_key" UNIQUE, btree (txt)

m=# \d b

       Table "public.b"

 Column |  Type   | Modifiers 

--------+---------+-----------

 num    | integer | 

 txt    | text    | 

Indexes:

    "u_b" UNIQUE, btree (txt) WHERE txt IS NOT NULL

Заполняем данными

m=# insert into a select 1, random()::text from generate_series(1, 10000);

INSERT 0 10000

m=# insert into a select 1, NULL from generate_series(1, 1000);

INSERT 0 1000

m=# insert into b select 1, random()::text from generate_series(1, 10000);

INSERT 0 10000

m=# insert into b select 1, NULL from generate_series(1, 1000);

INSERT 0 1000

Ну и финальное, перед сном)

m=# explain select * from a order by txt limit 10;

                                  QUERY PLAN                                   

-------------------------------------------------------------------------------

 Limit  (cost=0.00..0.84 rows=10 width=36)

   ->  Index Scan using a_txt_key on a  (cost=0.00..711.56 rows=8487 width=36)

(2 rows)

m=# explain select * from b order by txt limit 10;

                             QUERY PLAN                             

--------------------------------------------------------------------

 Limit  (cost=416.71..416.73 rows=10 width=36)

   ->  Sort  (cost=416.71..444.21 rows=11000 width=36)

         Sort Key: txt

         ->  Seq Scan on b  (cost=0.00..179.00 rows=11000 width=36)

(4 rows)

NS> Берём две таблички

ну это же очевидно, что если индекс частичный, то эффективно он
работать будет только по той выборке по которой он построен.

а тут выборка из таблицы полей всех целиком по сути. и индексированных
и не индексированных. соответственно во втором случае что индекс есть
что его нет :)

13 марта 2011 г. 3:48 пользователь Nikolay Samokhvalov <samokhvalov@gmail.com> написал:

О чем вообще тут может быть речь? Читайте внимательно 5.3.3 документации.
Вы правы по своему в том смысле, что ограничение уникальности, создаваемое
ключевым словом UNIQUE, _обеспечивается_ с помощью уникального индекса,
который для этого и создаётся.
А вообще, ограничение уникальности можно обеспечить и без создания индекса -
триггером. Сложно, но можно.

Это последствия? Я думал, что это результат работы парсера :-) 

2011/3/13 Dmitriy Igrishin <dmitigr@gmail.com>

13 марта 2011 г. 3:48 пользователь Nikolay Samokhvalov <samokhvalov@gmail.com> написал:

О чем вообще тут может быть речь? Читайте внимательно 5.3.3 документации.
Вы правы по своему в том смысле, что ограничение уникальности, создаваемое
ключевым словом UNIQUE, _обеспечивается_ с помощью уникального индекса,
который для этого и создаётся.
А вообще, ограничение уникальности можно обеспечить и без создания индекса -
триггером. Сложно, но можно.

Об этом и была речь. 

По-прежнему не ясно, какие мои слова вызвали такую реакцию (которая наконец-то стала затихать)).

 

Это последствия? Я думал, что это результат работы парсера :-) 

Оптимайзера.

RULE (упоминался в первом сообщении темы), парсер -- ЧУР вас, уважаемые Дмитрии! Не стоит их касаться в этой теме, что за тяга совсем запутать себя и других ))