Thread: 'UPDATE OR INSERT' command

Is there a postgresql SQL idiom to perform an UPDATE, which becomes an
INSERT if the primary key does not exist?

I'm not sure I *should* use it in my application, I just want to know if
it can be done. Thanks.

On Thu, Jul 15, 2004 at 13:20:57 -0500, Jeff Kowalczyk <jtk@yahoo.com> wrote:
> Is there a postgresql SQL idiom to perform an UPDATE, which becomes an
> INSERT if the primary key does not exist?
> 
> I'm not sure I *should* use it in my application, I just want to know if
> it can be done. Thanks.

There isn't a single statement that does this.

This has been discussed a number of times. The archives will have some
different examples.

You end up needing to either lock the table or check for failure since
postgres doesn't have predicate locking. Which technique is best will
depend on the details of your situation.

* Jeff Kowalczyk (jtk@yahoo.com) wrote:
> Is there a postgresql SQL idiom to perform an UPDATE, which becomes an
> INSERT if the primary key does not exist?
>
> I'm not sure I *should* use it in my application, I just want to know if
> it can be done. Thanks.

Unfortunately, I don't believe there's one in PostgreSQL yet.  MERGE is
similar to this, and is defined in the latest SQL spec.  Hopefully it'll
be implemented in PostgreSQL sometime soon.
Stephen