Добавьте ограничение уникальности в содержимое текстового массива Postgres [duplicate]

Праведный путь

Возможно, вам захочется пересмотреть нормализацию вашей схемы. Каждому не нужно «присоединяться даже к самому простому запросу». Создайте для этого VIEW .

Таблица может выглядеть так:

CREATE TABLE hostname (
 hostname_id serial PRIMARY KEY
,host_id     int    REFERENCES host(host_id) ON UPDATE CASCADE ON DELETE CASCADE
,hostname    text   UNIQUE
);

Первичный первичный ключ hostname_id является опциональным . Я предпочитаю иметь его. В вашем случае hostname может быть основным ключом. Но многие операции выполняются быстрее с помощью простого маленького ключа integer. Создайте ограничение внешнего ключа для ссылки на таблицу host. Создайте представление следующим образом:

CREATE VIEW v_host AS
SELECT h.*
      ,array_agg(hn.hostname) AS hostnames
--    ,string_agg(hn.hostname, ', ') AS hostnames  -- text instead of array
FROM   host h
JOIN   hostname hn USING (host_id)
GROUP  BY h.host_id;   -- works in v9.1+

Начиная с pg 9.1, первичный ключ в GROUP BY охватывает все столбцы этой таблицы в списке SELECT. Замечания по выпуску для версии 9.1 :

Разрешить столбцы не GROUP BY в списке целей запроса, когда первичный ключ указан в предложении GROUP BY

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

SELECT *
FROM   host h
JOIN   hostname hn USING (host_id)
WHERE  hn.hostname = 'foobar';

Если у вас есть индекс на host(host_id), это должно быть так, как должно быть первичным ключом. Кроме того, ограничение UNIQUE на hostname(hostname) автоматически обновляет другой необходимый индекс.

В Postgres 9.2+ показатель многоколоночности будет еще лучше, если вы сможете получить только индексное сканирование :

CREATE INDEX hn_multi_idx ON hostname (hostname, host_id)

Начиная с Postgres 9.3, вы можете использовать MATERIALIZED VIEW , если позволяют обстоятельства. Особенно, если вы читаете гораздо чаще, чем вы пишете в таблицу.

Темная сторона (что вы на самом деле просили)

Если я не могу убедить вас в праведном пути, я Я тоже помогу на темной стороне. Я гибкий. :)

Вот демонстрация того, как обеспечить уникальность имен хостов. Я использую таблицу hostname для сбора имен хостов и триггера в таблице host, чтобы обновлять его.

Функция триггера

CREATE OR REPLACE FUNCTION trg_host_insupdelbef()
  RETURNS trigger AS
$func$
BEGIN
-- split UPDATE into DELETE & INSERT
IF TG_OP = 'UPDATE' THEN
   IF OLD.hostnames IS DISTINCT FROM NEW.hostnames THEN  -- keep going
   ELSE RETURN NEW;  -- exit, nothing to do
   END IF;
END IF;

IF TG_OP IN ('DELETE', 'UPDATE') THEN
   DELETE FROM hostname h
   USING  unnest(OLD.hostnames) d(x)
   WHERE  h.hostname = d.x;

   IF TG_OP = 'DELETE' THEN RETURN OLD;  -- exit, we are done
   END IF;
END IF;

-- control only reaches here for INSERT or UPDATE (with actual changes)
INSERT INTO hostname(hostname)
SELECT h
FROM   unnest(NEW.hostnames) h;

RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Триггер:

CREATE TRIGGER host_insupdelbef
BEFORE INSERT OR DELETE OR UPDATE OF hostnames ON host
FOR EACH ROW EXECUTE PROCEDURE trg_host_insupdelbef();

SQL Fiddle с тестовым прогоном.

Для работы с ним используйте индекс GIN для операторов массива host.hostnames и :

• Почему мой индекс индекса PostgreSQL не используется (Rails 4)?
• Проверьте, присутствует ли какой-либо из заданного массива значений в массиве Postgres