Re: INSERT or UPDATE

> -----Original Message-----
> From: James B. Byrne [mailto:byrnejb(at)harte-lyne(dot)ca]
> Sent: Monday, April 06, 2009 2:06 PM
> To: Dann Corbit
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: RE: [GENERAL] INSERT or UPDATE
>
> On Mon, April 6, 2009 17:00, Dann Corbit wrote:
> .
> >
> > It is a difficult question.
> >
> > For instance, there are many possibilities when a collision occurs.
> >
> > 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.

Again, this may or may not be the right thing to do.