Pierre-Alexandre Moller Pierre-Alexandre Moller - 2 months ago 12
SQL Question

Convert INSERT IGNORE to Postgres INSERT

I'm currently working on a regex to transform sql syntax on Postgres syntax. I'm using geany to make replace text. For now, the syntax I try to change is this one :

INSERT IGNORE INTO item_question_ (question_fk_,item_fk_) VALUES(1002,162);


Into :

INSERT INTO item_question_ (question_fk_,item_fk_) VALUES (1002,151)
WHERE NOT EXISTS
(SELECT 1 FROM item_questionnaire_ WHERE question_fk_=1002 AND item_fk_ = 151)


I'm close but definitely not enought. I have this regex :

(INSERT IGNORE INTO (.*)_ (.*) VALUES(.*);) //Find information
INSERT INTO \2_ \3 VALUES \4 WHERE NOT EXISTS (SELECT 1 FROM \2_ WHERE )// Transform information


The fact is that I'm missing the most important part : the
WHERE
clause.

How can I get the value
question_fk_,item_fk_
and
1002,151
to build my where clause ?

Additionnal note : Postgres version is under 9.5. I can't use
ON CONFLICT IGNORE

Answer

As others mentioned, just use ON CONFLICT IGNORE from Postgres 9.5+. But since you say that DB version is less than 9.5, this is your regex solution for this task:

INSERT IGNORE INTO (.*) \((.*),(.*)\) VALUES \((.*),(.*)\); // capture

INSERT INTO \1 (\2, \3) VALUES (\4, \5) WHERE NOT EXISTS (SELECT 1 FROM \1 WHERE \2 = \4 AND \3 = \5) // replace

But keep in mind that this regex will work only for inserting exactly 2 items.