Re: most idiomatic way to "update or insert"?

Lincoln,
It works for me...
I think what you said is wrong because it updates first (if there is a row
to update), then inserts. If there is a row to update the insert won't
insert anything. If there is no row to update the insert inserts a row.
Either way, the insert is the last thing in the transaction. Plus, as shown
in the code to follow, I have almost this exact thing in my application and
I know that it does work for me. :)

Code (Perl):
$Neo::DB::Neo->do ("UPDATE Sample_Settings SET Value=" . Quote($Args{Value})
. " WHERE Sample_ID=" . Quote($Args{SampleID}) . " AND Setting=" .
Quote($Args{Setting}) . "; INSERT INTO Sample_Settings (Sample_ID, Setting,
Value) (SELECT " . Quote($Args{SampleID}) . ", " . Quote($Args{Setting}) .
", " . Quote($Args{Value}) . " WHERE NOT EXISTS (SELECT 1 FROM
Sample_Settings WHERE Sample_ID=" . Quote($Args{SampleID}) . " AND Setting="
. Quote($Args{Setting}) . "));");

Thanks,
Peter Darley

-----Original Message-----
From: Lincoln Yeoh [mailto:lyeoh(at)pop(dot)jaring(dot)my]
Sent: Wednesday, August 04, 2004 6:49 PM
To: Peter Darley; Mark Harrison; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] most idiomatic way to "update or insert"?

I don't think that works - there's a race condition if you do not do any
locking.

Why:
Before a transaction that inserts rows is committed, other transactions are
not aware of the inserted rows, so the select returns no rows.

So:
You can either create a unique index and catch insert duplicate failures.

Or:
lock the relevant tables, then do the select ... update/insert or insert
... select , or whatever it is you want to do.

Or:
both.

Test it out yourself.

At 07:51 AM 8/5/2004, Peter Darley wrote:
>Mark,
> It's not canonical by any means, but what I do is:
>
>update foo set thing='stuff' where name = 'xx' and thing<>'stuff';
>insert into foo (name, thing) (select 'xx' as name, 'stuff' as thing where
>not exists (select 1 from foo where name='xx'));
>
> I believe if you put these on the same line it will be a single
>transaction. It has the benefit of not updating the row if there aren't
>real changes. It's plenty quick too, if name is indexed.
>
>Thanks,
>Peter Darley