Re: INSERT or UPDATE

> -----Original Message-----
> From: James B. Byrne [mailto:byrnejb(at)harte-lyne(dot)ca]
> Sent: Monday, April 06, 2009 5:16 PM
> To: Dann Corbit
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: RE: [GENERAL] INSERT or UPDATE
>
>
> On Mon, April 6, 2009 17:15, Dann Corbit wrote:
>
> >
> > The pedagogic solution for this type of problem is called merge.
> > The last I knew, PostgreSQL did not directly support merge.
> > So you can accomplish the same thing in two stages:
> > 1. Check for existence and perform an update if the key is present
> > 2. If the key is not present, then perform an insert.
> >
> > Again, this may or may not be the right thing to do.
> >
>
> Forgive my obtuseness, but what does the preliminary SELECT
> accomplish? When the trigger fires we already know whether or not
> the entities row existed previously, what we are deciding is how to
> handle the concurrent identifiers table entry.
>
> I initially thought along these lines (select insert/update
> depending on the return value) but I gradually realized that it did
> not matter whether the identifier row was already there or not. If
> it exists then an UNIQUE key constraint prevents duplicates. If it
> does not exist then the INSERT succeeds. The previous identifier
> associated with the original common name has to remain on file to
> allow lookups by former names. Thus, we never update an identifier
> row in this fashion.

If a transaction involves rows where some succeed and some fail, all
will roll back.
If that is the desired behavior, or if all operations are singleton,
then you won't see any problems.