arrays - PostgreSQL 可以对数组元素进行唯一性约束吗？

Question

我正在尝试为当前位于 LDAP 存储中的主机数据提出一个 PostgreSQL 模式。该数据的一部分是一台机器可以拥有的主机名列表，该属性通常是大多数人用来查找主机记录的关键。

我想摆脱将此数据移动到 RDBMS 的一件事是能够在主机名列上设置唯一性约束，以便无法分配重复的主机名。如果主机只能有一个名字，这会很容易，但由于他们可以有多个名字，所以会更复杂。

我意识到这样做的完全规范化的方法是有一个主机名表，外键指向主机表，但我想避免每个人都需要为最简单的查询做连接:

select hostnames.name,hosts.*
  from hostnames,hosts
 where hostnames.name = 'foobar'
   and hostnames.host_id = hosts.id;

我认为使用 PostgreSQL 数组可以解决这个问题，它们确实使简单的查询变得简单:

select * from hosts where names @> '{foobar}';

但是，当我在主机名属性上设置唯一性约束时，它当然会将整个名称列表视为唯一值，而不是每个名称。有没有办法让每个名称在每一行中都是唯一的？

如果没有，是否有人知道另一种更有意义的数据建模方法？

Answer 1

正道

您可能需要重新考虑规范化您的架构。没有必要让每个人都“即使是最简单的查询也加入”。创建一个 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 会更快 key 。创建外键约束以链接到表 host .
创建这样的 View :

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 中该表的所有列列表。 release notes for version 9.1 :

Allow non-GROUP BY columns in the query target list when the primarykey is specified in the GROUP BY clause

查询可以像使用表一样使用 View 。通过这种方式搜索主机名会大大更快:

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

^{前提是您有关于 host(host_id) 的索引，应该是这种情况，因为它应该是主键。另外，UNIQUE对 hostname(hostname) 的约束自动实现其他需要的索引。}

在 Postgres 9.2+ 中，如果您可以获得 index-only scan，多列索引会更好从中:

CREATE INDEX hn_multi_idx ON hostname (hostname, host_id);

从 Postgres 9.3 开始，您可以使用 MATERIALIZED VIEW ，情况允许。尤其是当您在表格中阅读的次数远多于在表格中写的次数时。

阴暗面(你实际问的是什么)

如果我不能说服你相信正义的道路，这里有一些对黑暗面的帮助:

这是一个如何强制主机名唯一性的演示。我用一个表 hostname在表 host 上收集主机名和触发器使其保持最新状态。独特的违规行为引发异常并中止操作。

CREATE TABLE host(hostnames text[]);
CREATE TABLE hostname(hostname text PRIMARY KEY);  --  pk enforces uniqueness

触发函数:

CREATE OR REPLACE FUNCTION trg_host_insupdelbef()
  RETURNS trigger
  LANGUAGE plpgsql 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$;

触发器:

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

SQL Fiddle进行试运行。

在数组列上使用GIN 索引 host.hostnames和 array operators使用它:

• Why isn't my PostgreSQL array index getting used (Rails 4)?
• Check if any of a given array of values are present in a Postgres array