From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | Kolus Maximiliano <Kolus(dot)maximiliano(at)bcr(dot)com(dot)ar> |
Subject: | Re: how do i avoid multiple sessions from inserting the |
Date: | 2003-02-24 21:14:45 |
Message-ID: | 3E5A8B45.7070407@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> 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"
>
> (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(at)doe(dot)com' where not exists (select 1 from users where email = 'john(at)doe(dot)com');
This should work (syntactically), but, as I mentioned earlier, I doubt it will solve your problem, because it is still possible that somebody
else is inserting the same row right this moment, in which case your subquery will not see it until the other quy commits anyway, 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 the error)
Dima
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Moreno | 2003-02-24 22:31:10 | Re: Strange error (Socket command option unknown) |
Previous Message | Tom Lane | 2003-02-24 21:12:41 | Re: 7.4? |