涉及整数[]的PostgreSQL查询

Question

我有 2 个表：

CREATE TABLE article (  
  id serial NOT NULL,
  title text,
  tags integer[] -- array of tag id's from TAG table
)

CREATE TABLE tag (
  id serial NOT NULL,
  description character varying(250) NOT NULL
)

...并且需要根据文章标题从 ARTICLE 的“tags integer[]”中保存的 TAG 表中选择标签。

所以尝试了类似

SELECT *  
  FROM tag 
 WHERE tag.id IN ( (select article.tags::int4 
                      from article 
                     where article.title = 'some title' ) );

...这给了我

错误：无法将整数[]类型转换为整数
第 1 行：...FROM tag WHERE tag.id IN (（从...中选择article.tags::int4

我在开发和生产环境中都陷入了 PostgreSql 8.3 的困境。

Answer 1

使用数组重叠运算符&&：

SELECT *
    FROM tag
    WHERE ARRAY[id] && ANY (SELECT tags FROM article WHERE title = '...');

使用contrib/intarray，您甚至可以很好地索引此类内容。

Answer 2

看一下“8.14.5. 在数组中搜索”，但请考虑该部分末尾的提示：

提示：数组不是集合；搜索特定的数组元素可能是数据库设计错误的标志。考虑使用一个单独的表，其中每个项目都有一行，该项目将成为数组元素。这将更容易搜索，并且对于大量元素来说可能会更好地扩展。

Answer 3

你没有提到你的 Postgres 版本，所以我假设你使用的是最新版本（8.4，9.0）

这应该可以工作：

SELECT *  
  FROM tag 
 WHERE tag.id IN ( select unnest(tags)
                     from article 
                    where title = 'some title' );

但是你真的应该考虑改变你的表设计。

编辑

对于 8.3，可以轻松添加 unnest() 函数，请参阅此 wiki 页面：
http://wiki.postgresql.org/wiki/Array_Unnest