Re: how do i avoid multiple sessions from inserting the

Kolus Maximiliano <Kolus(dot)maximiliano(at)bcr(dot)com(dot)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(at)doe(dot)com')
> WHERE NOT EXISTS
> (SELECT id FROM users WHERE email='john(at)doe(dot)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(at)doe(dot)com'
WHERE NOT EXISTS
(SELECT id FROM users WHERE email='john(at)doe(dot)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