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);
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
How can I get the value
1002,151 to build my where clause ?
Additionnal note : Postgres version is under 9.5. I can't use
ON CONFLICT IGNORE
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.