Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar> writes:
> I tried, but it's giving me an error, as if INSERT wouldn't like the WHERE:
> INSERT INTO users (email) VALUES ('john@doe.com')
> WHERE NOT EXISTS
> (SELECT id FROM users WHERE email='john@doe.com');
> ERROR: parser: parse error at or near "WHERE"
This is not correct syntax: INSERT...VALUES doesn't take WHERE.
But INSERT...SELECT does:
INSERT INTO users (email)
SELECT 'john@doe.com'
WHERE NOT EXISTS
(SELECT id FROM users WHERE email='john@doe.com');
However, as a method of avoiding duplicate-key errors this is useless :-(
In my opinion the easiest approach is to just go ahead and roll back
the transaction when you get a dup-key error, and try again from the
top. So:
try UPDATE; if succeed then done
else try INSERT; if succeed then done
else ROLLBACK, start again
In practice rollbacks are going to be pretty infrequent, so this is
not inefficient --- certainly no worse than any other solution.
regards, tom lane