Re: I often have to do "update if exist, else insert", is my database design wrong? - Mailing list pgsql-general

On Jul 25, 2008, at 11:46 AM, Francisco Reyes wrote:

>> Is this normal or are there something else I could do so I don't have
>> to check if it exists?
>
> I would say that it is normal.
>
>
>> For the developers: a combined  insert/update command would be nice
>> :-)
>
> Mysql has such a beast along a some other non SQL compliant
> extensions.

MERGE isn't trivial, but the developers are well aware of the desire
for it.

>
>
> One possible approach to what you are trying to do would be:
> update existing table with new values
> delete from new table all records that already exist
> insert remaining new records
>
> Which can be done all in pure sql. No need to write a program.

Well... the race conditions may bite you there, if you have concurrent
access.

This - http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
  - is the usual approach suggested for a concurrent-access safe
upsert/merge right now.

Cheers,
   Steve