PostgreSQL可以对数组元素有唯一性约束吗?

Question

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

我想从将这些数据移动到RDBMS中得到的一件事是能够在主机名列上设置唯一性约束，这样就不能分配重复的主机名。如果主机只能有一个名称，这将很容易，但由于它们可以有多个名称，这就更复杂了。

我意识到这样做的完全规范化的方式是有一个hostnames表，外键指向hosts表，但我想避免让每个人都需要执行连接，即使是最简单的查询:

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

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

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

当我在hostnames属性上设置唯一性约束时，它当然会将整个名称列表视为唯一值，而不是将每个名称视为唯一值。是否有一种方法可以使每个名称在每行中唯一?

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

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+

从pg 9.1开始，GROUP BY 中的主键覆盖SELECT列表中该表的所有列。9.1版本的发行说明:

允许非GROUP BY列在查询目标列表中key在GROUP BY子句

中指定

查询可以像使用表一样使用视图。搜索主机名将更快:

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

^{假设您在host(host_id)上有一个索引，因为它应该是主键。另外，hostname(hostname)上的UNIQUE约束自动实现了其他所需的索引。}

在Postgres 9.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 with test run.

在数组列host.hostnames上使用GIN索引和数组操作符来处理它:

• 为什么我的PostgreSQL数组索引被使用(Rails 4)?
• 检查Postgres数组中是否存在一个给定的值数组

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}).

在Postgres 9.5中检查。

注意，这将使用操作符&&创建索引。因此，当您使用testtable时，检查ARRAY[x] && refs将比检查x = ANY( refs )快几倍。

注:总的来说，我同意上面的答案。在99%的情况下，您更喜欢规范化模式。请尽量避免在生产环境中使用"hacky"的东西。