Re: Insert Or update

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