Re: What is the postgres version of mysql's "ON DUPLICATE KEY" - Mailing list pgsql-general

Pierre-Frédéric Caillaud wrote:
> > INSERT INTO related_products (product_id,related_product_id)
> > SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
> >                                                        FROM
> > related_products
> >                                                        WHERE
> > product_id = 1 AND related_product_id = 2)
>
>     Should not the SELECT be FOR UPDATE ?
>     because if no insert is done, the OP wanted to UPDATE the row, so it
> should not be deleted by another transaction in-between...
>
>     Can the above query fail if another transaction inserts a row
> between the SELECT and the INSERT or postgres guarantee that this
> won't happen ?

There is no "between" a single statement.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/