Re: 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.

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3