PostgreSQL可以在数组元素上有唯一性约束吗？

Question

我正在尝试设计一个 PostgreSQL 数据库架构来存储当前在 LDAP 存储中的主机数据。其中一部分数据是主机名称列表，该属性通常是大多数人用来查找主机记录的关键字。

迁移这些数据到 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}';

当我在hostname属性上设置唯一性约束时，它将整个名称列表作为唯一值处理，而不是每个名称。是否有一种方法使每个名称在每一行中都是唯一的？

如果没有，有人知道其他更合理的数据建模方法吗？

- Lars Damerow

---

请参阅此问题/答案中的“索引数组”，以及pg9.3“数组元素外键”的此问题。 - Peter Krauss

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 键可以使许多操作更快。创建一个外键约束来链接到表 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+

从pg9.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)上有索引，这应该是主键，那么唯一性约束hostname(hostname)会自动实现所需的其他索引。}

在Postgres9.2+中，如果可以使用仅索引扫描，多列索引将更好：

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 提供了测试运行。

在数组列 host.hostnames 上使用GIN索引和数组操作符进行操作：

• 为什么我的PostgreSQL数组索引没有被使用（Rails 4）？
• 检查给定的值数组中是否存在于Postgres数组中

- Erwin Brandstetter

---

请参阅此问题/答案中的“索引数组”，以及pg9.3“数组元素外键”的此问题。 - Peter Krauss

@PeterKrauss：“数组元素外键”由于运算符兼容性和性能方面的严重问题自2012年以来一直处于停滞状态。因此，它既不在pg 9.3中，也不在pg 9.4中。 - Erwin Brandstetter

Answer 2

如果还有人需要原问题中的内容，请参考以下信息：

CREATE TABLE testtable(
    id serial PRIMARY KEY,
    refs integer[],
    EXCLUDE USING gist( refs WITH && )
);

INSERT INTO testtable( refs ) VALUES( ARRAY[100,200] );
INSERT INTO testtable( refs ) VALUES( ARRAY[200,300] );

这将会给你：

ERROR:  conflicting key value violates exclusion constraint "testtable_refs_excl"
DETAIL:  Key (refs)=({200,300}) conflicts with existing key (refs)=({100,200}).

在Windows上的Postgres 9.5中进行了检查。

请注意，这将使用运算符&&创建索引。因此，在使用testtable时，检查ARRAY[x] && refs比检查x = ANY( refs )要快几倍。

P.S.总的来说，我同意以上答案。在99%的情况下，您更喜欢规范化模式。请尽量避免在生产中使用“hacky”方法。

- volvpavl

---

结果发现它不适用于字符串/文本类型：https://www.postgresql.org/message-id/[e-mail]不过还是谢谢您的发帖！！我在研究这个问题时学到了很多关于排除约束、索引和操作符类的知识。 :) - vergenzt

在我的Linux Debian Stretch，PostgreSQL 10.3上无法工作。错误：ERROR: data type integer[] has no default operator class for access method "gist"。提示：您必须为索引指定运算符类或为数据类型定义默认运算符类。 - Enthusiasmus

@Enthusiasmus 或许你应该看看这个。 - volvpavl

@Enthusiasmus：这个解决方案依赖于gist__int_ops操作符类（它是一个整数数组的操作符类，也称为integer[]或_int4），可以通过intarray扩展提供。由@volvpavl链接的btree_gist扩展提供了一个gist_int_ops操作符类，它是一个integer或int4的操作符类，在这里没有帮助。 - tbussmann

@vergenzt：你可以使用一个将文本转换为整数数组的哈希函数来处理 text[]。一些实现可以在 hashlib 扩展中找到（或通过有意未记录的内部函数 hashtext()）。例如：CREATE FUNCTION hash_text_arr(text[]) RETURNS integer[] LANGUAGE sql IMMUTABLE AS 'SELECT array_agg(hashtext(v)) FROM unnest($1) v';，以及一个排除约束 EXCLUDE USING gist( hash_text_arr(refs) WITH && )，但这种方法容易出现哈希冲突，需要小心处理。 - tbussmann

请注意，排除约束只检查与行内其他行的冲突，而不是行内。INSERT INTO testtable( refs ) VALUES( ARRAY[400,400] ); 是可能的。 - tbussmann