Re: INSERT or UPDATE

Dann Corbit wrote on 06.04.2009 23:15:
>>> I guess that for some collisions, sharing the name is OK.
>>>
>> I failed to explicitly state what the PK looked like.
>>
>> entity_id(entities.id) +
>> identifier_type ('AKNA') +
>> identifier_value(entities.common_name)
>>
>> There will only be a PK collision when we attempt to add a duplicate
>> common name for the same entity, which means it already exists and
>> does not need to be added again.
>
> 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.

You don't actually need to check for existence. Just do the update, if no rows
were updated, you can insert (UPDATE will do an existence check anyway)

Thomas