473,544 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

most idiomatic way to "update or insert"?

So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.

I've been doing something like

delete from foo where name = 'xx';
insert into foo values('xx',1,2 ,...);

but I've been wondering if there's a more idiomatic or canonical
way to do this.

Many TIA,
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
20 18314
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

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Mark Harrison
Sent: Wednesday, August 04, 2004 4:26 PM
To: pg***********@p ostgresql.org
Subject: [GENERAL] most idiomatic way to "update or insert"?
So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.

I've been doing something like

delete from foo where name = 'xx';
insert into foo values('xx',1,2 ,...);

but I've been wondering if there's a more idiomatic or canonical
way to do this.

Many TIA,
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

I'll mention that often I do exactly what you're doing. I find deleting all
existing records and then inserting what I want to appear to be cleaner than
handling the various cases that can arise if you don't.

This happens most often when I have a list of items and have a UI that allows
the user to edit the entire list and commit a whole new list in one action.
It's much easier to simply delete the old list and insert the entire new list
in a single query than to try to figure out which rows to delete and which to
insert.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

I use stored procedures :

create function insertorupdate( ....)
UPDATE mytable WHERE ... SET ...
IF NOT FOUND THEN
INSERT INTO mytable ...
END IF;
You lose flecibility in your request though.

I wish Postgresql had an INSERT OR UPDATE like MySQL does. So far it's
the only thing that I regret from MySQL.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5
Mark Harrison wrote:
I've been doing something like

delete from foo where name = 'xx';
insert into foo values('xx',1,2 ,...);

but I've been wondering if there's a more idiomatic or canonical
way to do this.


The delete+insert isn't quite the same as an update since you might have
foreign keys referring to foo with "ON DELETE CASCADE" - oops, just lost
all your dependant rows. Other people have warned about race conditions
with insert/test/update.

An "update or insert" would be useful sometimes, but it's not always
necessary. Indeed, if I find I don't know whether I'm adding or updating
something I take a long hard look at my design - it ususally means I've
not thought clearly about something.

For a "running total" table it can make more sense to have an entry with
a total of 0 created automatically via a trigger. Likewise with some
other summary tables.

Can you give an actual example of where you need this?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #6
Hi,

I prefer to update and if the number of updated rows equals 0 do an insert. So
in case of update I need only one roundtrip. If insert is far more common in
this case it might be better try insert and catch the error. But I try to
avoid running on an error intentionally.

First delete and then insert works but needs 2 SQL-statements in every case.
And the database need to update indexes at least once. There might be also
problems with cascaded deletes.
Tommi

Am Donnerstag, 5. August 2004 01:25 schrieb Mark Harrison:
So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.

I've been doing something like

delete from foo where name = 'xx';
insert into foo values('xx',1,2 ,...);

but I've been wondering if there's a more idiomatic or canonical
way to do this.

Many TIA,
Mark


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #7
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{Val ue})
.. " WHERE Sample_ID=" . Quote($Args{Sam pleID}) . " AND Setting=" .
Quote($Args{Set ting}) . "; INSERT INTO Sample_Settings (Sample_ID, Setting,
Value) (SELECT " . Quote($Args{Sam pleID}) . ", " . Quote($Args{Set ting}) .
", " . Quote($Args{Val ue}) . " WHERE NOT EXISTS (SELECT 1 FROM
Sample_Settings WHERE Sample_ID=" . Quote($Args{Sam pleID}) . " AND Setting="
.. Quote($Args{Set ting}) . "));");

Thanks,
Peter Darley

-----Original Message-----
From: Lincoln Yeoh [mailto:ly***@po p.jaring.my]
Sent: Wednesday, August 04, 2004 6:49 PM
To: Peter Darley; Mark Harrison; pg***********@p ostgresql.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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #8
Peter,

The "does not work" part is not refering to the method not working at
all, but to the fact that it is not safe when you have multiple
transactions operating on the same row at the same time.
There are plenty of discussions about the insert-or-update race
conditions on this list, and the final conclusion was always that it is
not possible to solve the race condition without being prepared to catch
exceptions and retry the whole thing until it succedes...
The reason of the race condition: let's say 2 transactions A and B try
to insert-or-update the same row which does not exist. They do the
update statement at the same time, and BOTH OF THEM gets as a result
that no rows were updated, since the row does not exist yet. Now both
transactions try to insert the row, and obviously one of them will fail.
So your code must be prepared that the insert can fail, and in that case
it should retry with the update.
People tried to devise a method to avoid the race condition and throwing
exception, but it is just not possible.
Now the one bad thing in postgres which people complained about in this
context is that the transaction gets rolled back on any error, so
actually instead of just retrying the update, you will have to redo your
whole transaction.

HTH,
Csaba.
On Thu, 2004-08-05 at 15:28, Peter Darley wrote:
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{Val ue})
. " WHERE Sample_ID=" . Quote($Args{Sam pleID}) . " AND Setting=" .
Quote($Args{Set ting}) . "; INSERT INTO Sample_Settings (Sample_ID, Setting,
Value) (SELECT " . Quote($Args{Sam pleID}) . ", " . Quote($Args{Set ting}) .
", " . Quote($Args{Val ue}) . " WHERE NOT EXISTS (SELECT 1 FROM
Sample_Settings WHERE Sample_ID=" . Quote($Args{Sam pleID}) . " AND Setting="
. Quote($Args{Set ting}) . "));");

Thanks,
Peter Darley

-----Original Message-----
From: Lincoln Yeoh [mailto:ly***@po p.jaring.my]
Sent: Wednesday, August 04, 2004 6:49 PM
To: Peter Darley; Mark Harrison; pg***********@p ostgresql.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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #9
Mike,
Ahha! I didn't understand what the objection was. I guess I am getting
lucky. :)
It seems to me that this is true with any concurrent inserts, isn't it?
One will succeed and one will fail.
Thanks,
Peter Darley

-----Original Message-----
From: Mike Mascari [mailto:ma*****@ mascari.com]
Sent: Thursday, August 05, 2004 6:51 AM
To: Peter Darley
Cc: Lincoln Yeoh; Mark Harrison; pg***********@p ostgresql.org
Subject: Re: [GENERAL] most idiomatic way to "update or insert"?
Peter Darley wrote:
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. :)


You're getting lucky. I suggested the same thing four years ago. The
race condition is still there:

http://groups.google.com/groups?hl=e...78158285%40sss
..pgh.pa.us

HTH,

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
6024
by: KathyB | last post by:
Hi, I've been reading all sorts of info on the ntext field. I need this to store xml documents in sql server via a stored proc. Because of its size, I apparently can not use SET (as in UPDATE) therefore I'm trying to do an INSERT of the row with this field (after deleting the old row). CREATE PROCEDURE dbo.UpdateXmlWF ( @varWO...
8
11208
by: RC | last post by:
In my Access 2002 form, I have a combo box and on the AfterUpdate event I use DoCmd.RunSQL ("UPDATE .... to update records in a table. When it starts to run I get a message "You are about to update 3 row(s)." Is there a way to prevent the message from popping up?
6
1931
by: Rich | last post by:
Hello, I have to create a table in an Access mdb (remotely) on the fly. Create Table tbl1(fld1 Integer, fld2 varchar(10), fld3...) Then I have to insert data: Insert Into tbl1 Values(" & val1 & ", '" & val2 & "',...")"
1
5136
by: H5N1 | last post by:
hi there the topic says it all. I have a outer join select statement in tableadapter that populates GridView, I want to make it updatetable, so I need to provide an update command for table adapter. the problem is that not all rows in gridview exist in database (since it's an outer join) so I want to UPDATE statement to insert the row if...
1
2107
by: ComputerGuyCJ | last post by:
I have an application that I've used click-once deployment to publish out to a shared network path. From there I installed the app on a few client machines, including my own. Since then I published a few updates that were recognized by the app, and the users installed them, so everyone's on the most current version. A couple of glitches are...
5
7603
by: Lennart | last post by:
I really like the construction: select * from new table (update ....) X but I noticed that it cant be used as: insert into T select * from new table (update ....) X because of:
4
13188
by: Bart op de grote markt | last post by:
Hello I used to work in a Sybase database environment. When I had to insert/ update records in the database, I always used "insert on existing update", in this way, you didn't have to check whether a record already existed (avoid errors) and you were always sure that after running the scripts, the last version was in the database. Now...
4
2880
by: justice750 | last post by:
Hi All, I am using a FormView control. The allows me to update records in the database. However, when a database field is null I can not update the field on the form. It works fine when the field is not a null value. I am not using any code behind (C#) to bind the data or manipulate the data. I have read that when there is a null value in...
0
7424
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7607
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7709
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5909
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5297
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4918
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3409
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
988
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
661
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.