Re: how do i avoid multiple sessions from inserting the

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