Re: What is the postgres version of mysql's "ON DUPLICATE KEY"

> There is no "between" a single statement.

    Yes, I know, even if the statement involves mutiple subqueries...

    I meant :
    The OP wants to UPDATE if the row already exists, and to INSERT otherwise
; we have the INSERT bit here, but to UPDATE he needs to check if the
insert really took place, and if not, issue an UPDATE statement.. so that
makes it two statements.

    By the way, do several consecutive queries inside a plpgsql function
count as one statement (the function call) or as several statements (ie.
inside a function are transactions like SERIALIZED ?)



On Sat, 11 Sep 2004 13:56:26 +0200, Peter Eisentraut <peter_e@gmx.net>
wrote:

> 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 ?
>