Thread: Insert Or update

Dear All,
I have a database which stores traffic data and to update the traffic
for the particular IP i have to select this ip from the table for this
period and if it is already in the database i should run an update
statement, but if it is not presented - i should insert the data. It was
OK when i have had 2-3 millions of records, but now it is 15 millions
and i'm not complaining that it is too slow to select - no, but it takes
some time anyway.
I know that this will be helpful to write a function that will do this
for me, but it will run the same time as my insertion tool that is
written in c or even slower. So my question is: is it possible to have
"UPDATE OR INSERT" statement in the PostgreSQL like it is possible in
Oracle (as i heard but did not check it by myself yet)?

Best regards,
Anton

What do you need to do more of, inserts or updates? If the answer is
updates, just do an update and then check for the number of rows affected.
If it is 0, follow it with an insert, if not, you are done.

You could do this in a stored procedure to save you the round trip of data
between the DB and your application and will be faster.

Cheers,
Bas.

On Fri, Apr 23, 2004 at 17:48:21 +0400,
  Anton Nikiforov <anton@nikiforov.ru> wrote:
> I know that this will be helpful to write a function that will do this
> for me, but it will run the same time as my insertion tool that is
> written in c or even slower. So my question is: is it possible to have
> "UPDATE OR INSERT" statement in the PostgreSQL like it is possible in
> Oracle (as i heard but did not check it by myself yet)?

This was discussed on the list over the last couple of days.
There is no update or insert statement in postgres.
You can do an update and check the number of rows affected and if it
is 0 do the insert.
However unless you lock the table while doing this, you have to be prepared
to handle errors.

On Friday 23 April 2004 17:53, Bas Scheffers wrote:
> What do you need to do more of, inserts or updates? If the answer is
> updates, just do an update and then check for the number of rows affected.
> If it is 0, follow it with an insert, if not, you are done.
>
> You could do this in a stored procedure to save you the round trip of data
> between the DB and your application and will be faster.

This workaround is ok but it requires additional programming instead of a
simple single query. Absence of this sort of thing moves some of naturally
database-side logic off to the application, and this sounds quite mysql-ish
to me =\
This feature was asked for for too many times, maybe it's time to implement it
in some form ?

For example, like this: INSERT OR UPDATE into TABLE [(list of attributes)]
values (list of values)

2 possibilities here:

a) target table has at least one unique constraint on which this insert fails,
i.e. the row is "already in the database", by the unique constraint's
definition. In this case, pg can UPDATE it's attributes from (list of
attributes) with data from (list of values).

b) no constraint failure => new row is inserted.

This definition uses unique constraints to define and answer the question "is
the row is in the table already?". I'm sure somebody would want to define
this via some subset of target table's attributes, like this:

INSERT OR UPDATE ON (list of target table's attributes) into TABLE [(list of
attributes)] values (list of values)

I dont know if there's anything about this topic in the SQL spec; those are
just my random thoughs. Personally, I've had several situations where I'd
benefit from having this feature in either form.

--
Best regards,
Igor Shevchenko

Bruno Wolff III <bruno@wolff.to> writes:

> This was discussed on the list over the last couple of days.
> There is no update or insert statement in postgres.
> You can do an update and check the number of rows affected and if it
> is 0 do the insert.

I prefer to do the insert and if it fails due to a unique key constraint then
do the update. If you don't have any deletes then this is safe from race
conditions whereas the update first method could fail if two people do updates
and then both try to insert.

In practice actually I rarely have to do this.

> However unless you lock the table while doing this, you have to be prepared
> to handle errors.

--
greg

On Fri, Apr 23, 2004 at 20:17:10 +0300,
  Igor Shevchenko <igor@carcass.ath.cx> wrote:
>
> This workaround is ok but it requires additional programming instead of a
> simple single query. Absence of this sort of thing moves some of naturally
> database-side logic off to the application, and this sounds quite mysql-ish
> to me =\
> This feature was asked for for too many times, maybe it's time to implement it
> in some form ?

I suspect most of the people doing this have something wrong with their
design in the first place.

> For example, like this: INSERT OR UPDATE into TABLE [(list of attributes)]
> values (list of values)

There is a command defined to do this in the latest SQL standard. There was
some discussion about this very recently on one of the lists. You might want
to look at it to see what the standard syntax is going to be. There were
also some comments on the semantics of the command.

On Friday 23 April 2004 20:41, Bruno Wolff III wrote:
> I suspect most of the people doing this have something wrong with their
> design in the first place.

Not really.

Here's a simple example. I have a set of mailboxes and I needed to implement a
gui widget to assign/remove them to/from a workspace. When the widget starts,
there are two lists: first with assigned, second with unassigned (all other)
mailboxes. User moves data from one list to another and some back, etc, and
hits "OK" button. It appeared that the easiest way to sync this widget's data
with the database was via insert-or-update path.

--
Best regards,
Igor Shevchenko

On Saturday 24 April 2004 00:09, you wrote:
> And in the proper way to do this in a relational database, those rows
> are locked by the application until the user presses the OK button.

This kind of change is very rare and is usually done by "admin" user. There's
no need to lock those rows between load and update.

> As an addendum to Bruno's comment (which is true), I'll add that the
> "insert or update" command (in MySQL it's a "replace" command) really
> suggests a broken architecture in your database.  It's okay, we often
> use relational databases for non-relational tasks simply because we have
> nothing else available, but don't be surprised when it breaks.

In my case, insert-or-update is just a handy shortcut for a longer operation.
It appears that this shortcut can be useful to many people in various
situations, as the requests for it are constantly appearing from time to
time.

mysql's "replace" is broken in a sence that for existing row, it's actually
DELETE+INSERT, and this breaks lots of things. For example, it'll assign new
ID for auto_increment column type. This is a big problem for foreign keys
(but not a problem for mysql due to obvious reasons:).

insert-or-update is useful in a general case when you need to merge some new
set of data (maybe coming as an update from some external source) with an
existing set of data in some table. Is it a relational or a non-relational
task ?

Are there any plans on adding this feature per sql 2003 spec ?

--
Best regards,
Igor Shevchenko