What to use instead of "INSERT ... ON CONFLICT DO NOTHING" on postgres

Question

I have the following query, which I use with 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..

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.

Answer 1

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