Уникално ограничение за пермутации в множество колони

Дадени са следните три колони в база данни на Postgres: първа, втора, трета; как мога да създам ограничение, така че пермутациите да са уникални?

напр. Ако ('foo', 'bar', 'shiz') съществува в db, ('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]));

Fiddle

АКТУАЛИЗИРАНЕ

Всъщност

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

Числата са байтове за индексния ред в примера във цигулката, измерени с pg_column_size() . Моят пример използва само единични знаци, разликата в размера е постоянна.

person Erwin Brandstetter    schedule 21.08.2013
comment
Умно използване на GREATEST и LEST. - 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:

Сортирайте термините по азбучен ред и поставете разделител от двете страни на всеки термин. Свържете ги и ги поставете в отделно поле, което става първичен ключ.

Защо разделителят? Така че "a", "aa", "aaa" и "aa", "aa", "aa" могат да бъдат вмъкнати и двете.

person KevDog    schedule 21.08.2013