postgresql 在 postgres 上使用什么代替“INSERT ... ON CONFLICT DO NOTHING”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34845260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What to use instead of "INSERT ... ON CONFLICT DO NOTHING" on postgres
提问by Clémentine
I have the following query, which I use with postgres 9.5:
我有以下查询，我在 postgres 9.5 中使用它：
INSERT INTO knowledge_state
(SELECT learnerid learner_id, lo_id FROM qb_lo_tag WHERE qb_id = NEW.qb_id)
ON CONFLICT DO NOTHING ;
Unfortunately I can't use postgres 9.5 on some servers, and I need to convert it to a pre - 9.5 friendly query. I have built the following query instead, but it seems much more complicated to me, and I thought something simpler might be possible..
不幸的是，我无法在某些服务器上使用 postgres 9.5，我需要将其转换为 9.5 之前的友好查询。我已经构建了以下查询，但对我来说似乎要复杂得多，我认为可能更简单的事情..
FOR rows IN SELECT lo_id FROM knowledge_state
WHERE learner_id = learnerid
AND lo_id IN (SELECT lo_id FROM qb_lo_tags WHERE qb_id = New.qb_id) LOOP
INSERT INTO knowledge_state (lo_id, learner_id) SELECT rows.lo_id, learnerid
WHERE NOT EXISTS (SELECT * FROM knowledge_state WHERE lo_id = rows.lo_id AND learner_id = learnerid);
END LOOP;
I would love to hear ideas on how to simplify this query.
我很想听听有关如何简化此查询的想法。
采纳答案by tpdi
Just do what you're doing, without the loop:
做你正在做的事情，没有循环：
INSERT INTO knowledge_state (lo_id, learner_id)
SELECT a.lo_id, a.learnerid
FROM qb_lo_tag a
WHERE a.qb_id = NEW.qb_id
and NOT EXISTS (SELECT * FROM knowledge_state b
WHERE b.lo_id = a.lo_id AND b.learner_id = a.learnerid);
Of course, you can add an index on knowledge_state (lo_id, learner_id) to make it faster (On Conflict implies a unique constraint or other constraint, and a unique constraint implies an index).
当然，你可以在knowledge_state(lo_id,learner_id)上加一个索引，让它更快（On Conflict隐含唯一约束或其他约束，唯一约束隐含索引）。
