用于更新元素标记的高效查询

Question

我正试图在一个动作中创建/添加/删除所有标签。我想获得一个标记名称和元素ID的数组，并有效地更新该元素，使其在数组中只有标记。

我有三张桌子，如下所示。

要素：

|id|stuff|
|--|-----|
|1 | ... |
|2 | ... |

标签：

|id|name|
|--|----|
|1 | pg |
|2 |node|

标签映射：

|id|element_id|tag_id|
|--|----------|------|
|1 |    2     |  1   |
|2 |    2     |  2   |

在操作开始时，元素可以有任意数量的标记分配给它。操作将以标记名称和元素ID的数组开始。在操作结束时，我希望具有该元素ID的元素只分配给该数组中传入的标记。阵列可能具有尚未在标签表中创建的标签，因此需要插入。

这是我的愚蠢的，不是优化的解决方案。

BEGIN;
INSERT INTO tags (name)
VALUES (''),(''),('')...
ON CONFLICT DO NOTHING;
DELETE FROM tag_map
WHERE element_id = 'myElemID';
WITH tag_ids AS (
SELECT id FROM tags
WHERE name IN ('','',''...)
)
INSERT INTO tag_map (element_id, tag_id)
SELECT ('myElemID', tag_ids);
COMMIT;

我相信还有更有效的方法来完成同样的事情。甚至可能在一个查询中完成？如有任何帮助，将不胜感激

Answer 1

假设一个示例结构：

create table elements (id serial primary key, stuff text);
create table tags     (id serial primary key, name text);
create table tag_map  (id serial primary key,
element_id integer references elements(id),
tag_id     integer references tags(id));
insert into elements (stuff) values ('e1'),('e2'),('e3');
insert into tags     (name)  values ('t1'),('t2'),('t3'),('t4');
insert into tag_map  (element_id, tag_id)  values (1,1),(1,2),(2,4);

您可以使用WITH(公共表表达式(在一个操作中完成此操作：

with 
delete_tag_map_by_element as (  
delete from tag_map tm 
using elements e
where tm.element_id=e.id 
and   e.stuff='e2'
returning 'deleted' as operation,tm.*)
,tag_ids_from_names as (
select id
from   tags
where   name in ('t2','t3'))--tag list goes here
,insert_tag_map as (
insert into tag_map (element_id, tag_id)
select 3, --target element to be tagged goes here
id
from tag_ids_from_names
returning 'inserted' as operation,*)
select * from delete_tag_map_by_element
union
select * from insert_tag_map;

在线演示。

---

关于注释：PostgreSQL 12中的一些PostgreSQL 15MERGE功能以insert...on conflict do update的形式提供，但在这里没有意义，因为tag_map.id字段，我认为它是表主键。如果你放弃了那个专栏，把pk设置为

create table tag_map2 (
element_id integer references elements(id),
tag_id integer references tags(id),
primary key (element_id,tag_id));

您可以使用MERGE中的WHEN MATCHED和WHEN NOT MATCHED处理来删除新集合中缺少的标记元素映射。insert...on conflict do update可以保留现有的映射并添加新的映射，但如果没有CTE，它就无法删除过时的映射——这就是为什么它通常被称为UPSERT，而不是完全涵盖MERGE的所有用途。