Thread: insert/update

I seemed to remember being able to do this but I can't find the docs.

Can I run a sql query to insert new or update existing rows in one query?

Otherwise I have to run a select query to see if it's there and then
another one to update/insert.

What I'm trying to do is create a counter for each key, insert a value
of 1 or increment the value by 1 and then set another specific row
(where key = $key) to always increment by 1.

And the more I type, the more this sounds like the answer is going to be
part function, part trigger....  Maybe I should post to 'novice' for a
while!  ;)

On 26/05/2004 11:54 Tom Allison wrote:
> I seemed to remember being able to do this but I can't find the docs.
>
> Can I run a sql query to insert new or update existing rows in one query?
>
> Otherwise I have to run a select query to see if it's there and then
> another one to update/insert.
>
> What I'm trying to do is create a counter for each key, insert a value
> of 1 or increment the value by 1 and then set another specific row
> (where key = $key) to always increment by 1.
>
> And the more I type, the more this sounds like the answer is going to be
> part function, part trigger....  Maybe I should post to 'novice' for a
> while!  ;)

Use a sequence.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business             |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Paul Thomas wrote:
> On 26/05/2004 11:54 Tom Allison wrote:

>> What I'm trying to do is create a counter for each key, insert a value
>> of 1 or increment the value by 1 and then set another specific row
>> (where key = $key) to always increment by 1.

> Use a sequence.

Not sure it's going to help him here. Looks like a specific count is needed.

Tom - you don't say precisely what you're trying to do, but I like to
keep my code simple by making sure there is always a row available.

Example (a poor one, perhaps):
  cart_details (cart_id, owner, ...)
  cart_summary (cart_id, num_items, tot_value)
  cart_items (cart_id, item_id, quantity)

Create a trigger on cart_details that after inserting a new row, inserts
zeroed totals into cart_summary. That way when you add new items to the
cart, you know there is always a total to update.

On the other hand, you might need cart_summary to be something like:
   cart_summary (cart_id, item_category, num_items, tot_value)
In this case you either create zeroed totals for every value of
"item_category" or you need a trigger on cart_items rather than
cart_details. If the trigger is on cart_items and you can have more than
one user adding items to the cart at the same time, then you'll need to
think about concurrency issues and locking.

Useful sections of the manual are "Procedural Languages:pl/pgsql" and
"SQL command reference". You can probably find example triggers via the
techdocs site.

--
   Richard Huxton
   Archonet Ltd

--- Tom Allison <tallison@tacocat.net> wrote:
> I seemed to remember being able to do this but I
> can't find the docs.
>
> Can I run a sql query to insert new or update
> existing rows in one query?
>
> Otherwise I have to run a select query to see if
> it's there and then
> another one to update/insert.

This is what you have to do.

This question comes up a lot on the lists.  You can
read endless discussions about it if you want to
search the archives.

The issue is concurrency, i.e. multiple users
accessing the data at the same time, and perhaps two
of them wanting to do the same update-else-insert
combination at the same time.  Then you have the so
called "race condition", i.e. user1 does a select,
finds the record does not exist, attempts to insert;
in between those, user2 inserts the row.  So, you now
either have duplicate data (bad), or user1's insert
fails because of a unique constraint (also bad,
because the operation has failed).

The only way to guarantee against this is to lock the
table for the duration of the exercise, which prevents
any concurrent access at all.  This may be acceptable
if you have few users, or a low insert/update load,
but may be a performance killer otherwise.

Every now and then someone pops up on the list(s)
claiming to have found some new miracle method for
getting around these limitations, but no such has yet
been proven.

>
> What I'm trying to do is create a counter for each
> key, insert a value
> of 1 or increment the value by 1 and then set
> another specific row
> (where key = $key) to always increment by 1.
>
> And the more I type, the more this sounds like the
> answer is going to be
> part function, part trigger....  Maybe I should post
> to 'novice' for a
> while!  ;)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
cleanly





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

Richard Huxton <dev@archonet.com> writes:

> Tom - you don't say precisely what you're trying to do, but I like to keep my
> code simple by making sure there is always a row available.

Or alternatively you could always try to insert the record with a count of 0
then increment. If the insert fails due to a duplicate key violation you could
just ignore the error.

That suffers from doing twice as many queries as necessary all the time. You
could try doing the update then check the result to see how many records were
updated, if 0 then try doing the insert ignoring any errors and then repeat
the update.

But then your code is getting kind of complex... And both of these assume
nobody's deleting records.

The more usual solution is to always try either the update or the insert, and
in the case of a duplicate key violation or 0 updated rows, then try the
other. To do this properly you have to do it in a loop, since some other
process could be inserting or deleting between the two queries.

FWIW the feature you're looking for is indeed a new feature in the latest SQL
standard and there's been some talk of how to implement it in a future version
of Postgres. I would expect to see it come along sometime, though probably not
in 7.5.

--
greg

Jeff Eckermann wrote:
> --- Tom Allison <tallison@tacocat.net> wrote:
>
>>I seemed to remember being able to do this but I
>>can't find the docs.
>>
>>Can I run a sql query to insert new or update
>>existing rows in one query?
>>
>>Otherwise I have to run a select query to see if
>>it's there and then
>>another one to update/insert.
>
>
> This is what you have to do.
>
> This question comes up a lot on the lists.  You can
> read endless discussions about it if you want to
> search the archives.
>
> The issue is concurrency, i.e. multiple users
> accessing the data at the same time, and perhaps two
> of them wanting to do the same update-else-insert
> combination at the same time.  Then you have the so
> called "race condition", i.e. user1 does a select,
> finds the record does not exist, attempts to insert;
> in between those, user2 inserts the row.  So, you now
> either have duplicate data (bad), or user1's insert
> fails because of a unique constraint (also bad,
> because the operation has failed).
>
> The only way to guarantee against this is to lock the
> table for the duration of the exercise, which prevents
> any concurrent access at all.  This may be acceptable
> if you have few users, or a low insert/update load,
> but may be a performance killer otherwise.
>

So I have to watch out for transactions on this?
Essentially what I'm trying to do is one of the following two:

if exists update a field to field+1 on one record
if it doesn't exist, insert a row with field = 1

--- Tom Allison <tallison@tacocat.net> wrote:
> Jeff Eckermann wrote:
> > --- Tom Allison <tallison@tacocat.net> wrote:
> >
> >>I seemed to remember being able to do this but I
> >>can't find the docs.
> >>
> >>Can I run a sql query to insert new or update
> >>existing rows in one query?
>
> So I have to watch out for transactions on this?
> Essentially what I'm trying to do is one of the
> following two:
>
> if exists update a field to field+1 on one record
> if it doesn't exist, insert a row with field = 1
>

I'm not sure what you are asking here that is not
already covered.  I suggest you spend some time
reading the documentation on concurrency, and
searching the archives for some of the lengthy past
discussions on this topic.




__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/