Обсуждение: Q: insert/update in same statement

Is it possible to make a single SQL statement that will insert a row
if it does not already exist, otherwise it will just update the
fields?

Actually, just a statement that will not give an error about duplicate
keys would be enough for my application.

Something like

INSERT INTO SOME_TABLE (FIELD1, ...) VALUES (VALUE1, ...) WHERE
KEY_FIELD DOES NOT EXIST;

I am working in PostgreSQL 7.3.x.

Thanks, Ralph

On Fri, Sep 12, 2003 at 10:55:17 -0700, "G. Ralph Kuntz, MD" <grk@usa.net> wrote:
> Is it possible to make a single SQL statement that will insert a row
> if it does not already exist, otherwise it will just update the
> fields?

No.

> Actually, just a statement that will not give an error about duplicate
> keys would be enough for my application.

This you can do by using a select statement that checks if the primary
key is a duplicate on the insert statement. An example was posted on
one of the lists about a week ago.

Note that there are conncurrency issues with doing this. Either you want
to exclusively lock the table or use serializable isolation and be
prepared to retry after failures.