From: PFC <lists@boutiquenumerique.com>
To: tobbe <tobbe@tripnet.se>
To: pgsql-sql@postgresql.org
Subject: Re: automatic update or insert
Date: Tue, 25 Oct 2005 10:07:39 +0200
Message-ID: <op.sy6zi1w0th1vuj@localhost> (raw)
In-Reply-To: <1129790184.351579.302550@g47g2000cwa.googlegroups.com>
References: <1129790184.351579.302550@g47g2000cwa.googlegroups.com>
> In a system of mine i need to insert records into table [tbStat], and
> if the records exist i need to update them instead and increase a
> column [cQuantity] for every update.
>
> I.e. the first insert sets cQuantity to 1, and for every other run
> cQuantity is increased.
>
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
There are two ways you can do this :
* If you will have more updates than inserts (ie. more items with a
quantity >1 than 1) :
UPDATE
If the update updated no rows, then INSERT
* If you have more inserts than updates (ie. more items with quantity 1
than >1) :
INSERT
if it fails due to violating the unique constraint, then UPDATE
None of these involve a SELECT. The first one is very cheap if you end up
doing more updates than inserts, because it just does the update.
You will of course need a UNIQUE index to identify your rows, and prevent
insertion of duplicates. I suppose you have this already.
There is a subtility in the second form : the INSERT will fail on
duplicate key, so you have to either rollback the transaction if you send
the queries raw from your app, or catch the exception in your plpgsql
function.
Also a race condition might exist if someone deletes a row in-between, or
the first procedure is executed twice at the same time by different
threads. Be prepared to retry your transaction.
Something like the ON DUPLICATE KEY UPDATE in MySQL would be nice to have.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@boutiquenumerique.com, tobbe@tripnet.se
Subject: Re: automatic update or insert
In-Reply-To: <op.sy6zi1w0th1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox