Уникальное ограничение для перестановок в нескольких столбцах

Учитывая следующие три столбца в базе данных Postgres: первый, второй, третий; как я могу создать ограничение, чтобы перестановки были уникальными?

Например. Если ('foo', 'bar', 'shiz') существует в базе данных, ('bar', 'shiz', 'foo') будет исключено как неуникальное.


postgresql sql constraints database-design unique-constraint
person KevDog    schedule 21.08.2013    source источник
comment
Я тоже хотел бы это знать. Просто из любопытства.   -  person ffflabs    schedule 21.08.2013
comment
Могут ли ваши столбцы быть NULL? Могут ли они быть пустыми? Определите уникальный тогда. Ваша версия Postgres? И я не вижу определения таблицы для тестирования в вашем вопросе?   -  person Erwin Brandstetter    schedule 21.08.2013
comment
Ненулевой. 9.2.2 Все значения текстовые.   -  person KevDog    schedule 21.08.2013
comment
Возможно, вы захотите обновиться до версии 9.2.4, чтобы устранить уязвимость безопасности, исправленную в версии 9.2.4. Дополнительные сведения см. на странице cve.mitre.org/cgi-bin. /cvename.cgi?name=CVE-2013-1899   -  person bma    schedule 21.08.2013

Ответы (4)


arrow_upward
6
arrow_downward

Вы можете использовать hstore для создания уникального индекса:

CREATE UNIQUE INDEX hidx ON test USING BTREE (hstore(ARRAY[a,b,c], ARRAY[a,b,c]));

Скрипка

ОБНОВИТЬ

Фактически

CREATE UNIQUE INDEX hidx ON test USING BTREE (hstore(ARRAY[a,b,c], ARRAY[null,null,null]));

может быть лучше, так как он будет работать так же, но займет меньше места (fiddle) .

person Jakub Kania    schedule 21.08.2013

arrow_upward
5
arrow_downward

Только для трех столбцов этот уникальный индекс, использующий только базовые выражения, должен работать очень хорошо. Никаких дополнительных модулей, таких как hstore или пользовательские функции, не требуется:

CREATE UNIQUE INDEX t_abc_uni_idx ON t (
  LEAST(a,b,c)
, GREATEST(LEAST(a,b), LEAST(b,c), LEAST(a,c))
, GREATEST(a,b,c)
);

скрипт SQL

Также требуется минимум места на диске:

SELECT pg_column_size(row(hstore(t))) AS hst_row
      ,pg_column_size(row(hstore(ARRAY[a,b,c], ARRAY[a,b,c]))) AS hst1
      ,pg_column_size(row(hstore(ARRAY[a,b,c], ARRAY[null,null,null]))) AS hst2
      ,pg_column_size(row(ARRAY[a,b,c])) AS arr
      ,pg_column_size(row(LEAST(a,b,c)
                        , GREATEST(LEAST(a,b), LEAST(b,c), LEAST(a,c))
                        , GREATEST(a,b,c))) AS columns
FROM t;

 hst_row | hst1 | hst2 | arr | columns
---------+------+------+-----+---------
      59 |   59 |   56 |  69 |      30

Числа — это байты для строки индекса в примере в скрипке, измеренные с помощью . В моем примере используются только одиночные символы, разница в размере постоянна.

person Erwin Brandstetter    schedule 21.08.2013
comment
Умное использование БОЛЬШОГО и НАИМЕНЬШЕГО. - person bma; 21.08.2013

arrow_upward
3
arrow_downward

Вы можете сделать это, создав уникальный индекс для функции, которая возвращает отсортированный массив значений в столбцах:

CREATE OR REPLACE FUNCTION sorted_array(anyarray)
RETURNS anyarray
AS $BODY$
  SELECT array_agg(x) FROM (SELECT unnest($1) AS x FROM test ORDER BY x) AS y;
$BODY$
LANGUAGE sql IMMUTABLE;

CREATE UNIQUE index ON test (sorted_array(array[first,second,third]));
person qqx    schedule 21.08.2013
comment
Возможно, стоит проверить, как повлияет на производительность пара вложенных выражений CASE для ручной сортировки. Только с 3 столбцами, которые должны быть жизнеспособными. Хотя, возможно, не стоит тратить дополнительное время. - person Richard Huxton; 21.08.2013
comment
Ричард, производительность здесь не играет роли. Небольшое приложение, может быть 20-100 вставок в день. - person KevDog; 21.08.2013

arrow_upward
0
arrow_downward

Предложение коллеги, вариант идеи @julien:

Отсортируйте термины в алфавитном порядке и поместите разделитель с обеих сторон каждого термина. Объедините их и поместите в отдельное поле, которое станет первичным ключом.

Почему разделитель? Так что "а", "аа", "ааа" и "аа", "аа", "аа" могут быть вставлены оба.

person KevDog    schedule 21.08.2013