Thread: Re: how do i avoid multiple sessions from inserting the

> Why not something like that ?
[...]
> 2) IF NULL -> INSERT INTO users values (....'blah' )  WHERE NOT EXISTS ....
>                -> SELECT id FROM users WHERE email = 'blah'

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"

(Btw, i didnt know that INSERT would accept a WHERE clause)
 

You might have duplicate john doeos with diffent ID's

2/24/2003 9:51:40 AM, Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar> wrote:

>
>
>  From:   Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar>
>
>  To:     patrick.fiche@aqsacom.com, pgsql-general@postgresql.org
>  Subject:Re: [GENERAL] how do i avoid multiple sessions from inserting the
>  Date:   Mon, 24 Feb 2003 14:51:40 -0300
>
>
>
>
>  > Why not something like that ?
>
>  [...]
>  > 2) IF NULL -> INSERT INTO users values (....'blah' )  WHERE NOT EXISTS
>  ....
>  >                -> SELECT id FROM users WHERE email = 'blah'
>
>
>  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"
>
>
>  (Btw, i didnt know that INSERT would accept a WHERE clause)
>

>
> 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"
>
> (Btw, i didnt know that INSERT would accept a WHERE clause)
>

Exactly. It will not.

You might do something like

insert into users (email)
select 'john@doe.com' where not exists (select 1 from users where email = 'john@doe.com');

This should work (syntactically), but, as I mentioned earlier, I doubt it will solve your problem, because it is still
possiblethat somebody 
else is inserting the same row right this moment, in which case your subquery will not see it until the other quy
commitsanyway, and you will have 
the same problem as before...

You have to either lock the table before checking if the row exists, or be able to handle the error you get after
insert
(in which case, you do not really need to check if it exists first - go straight to insert, and, if it fails, ignore
theerror) 

Dima

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