arrays - 如何检查列表的元素是否存在于postgres表中

问题描述

我的申请中有一个清单：

tags = ["nature", "science", "funny", "politics", "news"]

我想检查我的 Tags.name 表字段中是否存在所有这些元素。这个想法是用户无法添加系统中尚不存在的任何新标签。

目前，我正在尝试在查询中运行 .foreach 循环；

DO $$
BEGIN
  FOREACH itag IN ARRAY {'nature', 'politics'} LOOP
    IF EXISTS (SELECT 1 FROM tags WHERE name = itag) THEN
      INSERT INTO TAGS (name, post_id) values itag, 'post01' ;
    ELSE
      RAISE EXCEPTION 'Tag % doesnt exists in table', itag;
    END IF;
  END LOOP;
END; $$

这给出了错误；

ERROR:  syntax error at or near "{"
LINE 3:   FOREACH itag SLICE 1 IN ARRAY {'nature', 'politics'} LOOP

我不确定如何在 postgres 中查询列表。我不想通过应用程序代码执行此操作并触发多个查询，因为我的列表中可能有很多元素。我希望在单个查询中对照表值检查列表。

另外，如果可能的话，有没有办法优化我的查询？

编辑：我使用@a_horse_with_no_name 的答案提出了类似于我正在寻找的流程。如果我的表中存在所有标签，我将添加这些条目，否则我将抛出异常。

DO $$
BEGIN
 IF (with to_check (itag) as (
   values ('nature'),('science'),('politics'),('scary')
  )
  select bool_and(exists (select * from tags t where t.name = tc.itag)) as all_tags_present
  from to_check tc) THEN
  RAISE INFO 'ALL GOOD. Here I will add the insert statement in my app';
ELSE
  RAISE EXCEPTION 'One or more tags are not present';
END IF;

END; $$

标签： arrayspostgresqlloopsforeach

解决方案

不需要 PL/pgSQL 或循环。您可以使用标签列表并在单个语句中检查每个标签的存在：

with to_check (itag) as (
   values ('nature'),('science'),('funny'),('politics'),('news')
)
select tc.itag, 
       exists (select * from tags t where t.name = tc.itag) as tag_exists
from to_check tc;

如果您只想要一个标志来告诉您是否至少缺少一个标签，您可以使用以下内容：

with to_check (itag) as (
   values ('nature'),('science'),('funny'),('politics'),('news')
)
select bool_and(exists (select * from tags t where t.name = tc.itag)) as all_tags_present
from to_check tc;

bool_and只有当所有值都为真时才会返回真。

您得到的错误是因为{'nature', 'politics'}是无效的数组文字。您要么需要使用array构造函数

array['nature', 'politics']

或可以转换为数组的字符串文字

'{nature, politics}'::text[]

（注意大括号在字符串内部）。

我更喜欢数组构造函数，因为我不必担心嵌套字符串文字。

这个想法是用户无法添加系统中尚不存在的任何新标签

这个问题的正确解决方案是，拥有一个包含标签定义的表，并确保每个标签名称只使用一次：

create table tag_definition
(
   name   varchar(50) primary key
);

然后在您的标签表中引用 tag_definition：

create table tags
(
   name     varchar(50) not null references tag_definition, 
   post_id  integer  not null references posts
);

现在不可能向tags表中插入不存在的标签。

您现在需要做的就是在插入行时捕获异常。插入前无需检查标签。

tags您可以通过为表使用生成的主键tag_definition（例如 a ）来节省空间并使表变得更小，并将其用于表serial中的引用tags。

鉴于您问题中的插入语句，您可以使用单个插入语句实现相同的目的：

with to_check (itag) as (
   values ('nature'),('politics')
)
insert into tags (tag, post_id)
select tc.itag, 'post01'
from to_check tc 
where not exists (select itag
                  from to_check
                  except
                  select t.name
                  from tags t);

但是，如果标签表增长，那将不会很好地扩展。如果所有标签都存在，则子选择将不返回任何内容to_check，因此该not exists条件将使 INSERT 返回所有内容。如果至少一个标签不存在，则不会插入任何内容。

为了使这个（有点）高效，你需要一个关于`tags（name）的索引。

arrays - 如何检查列表的元素是否存在于postgres表中

问题描述

解决方案

推荐阅读