Re: How to insert record only if primary key does not exist

On Mon, Jul 2, 2012 at 12:19 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
> How to insert record to this table only if primary key does not exist ?
>
>
> You want an operation that's called an UPSERT or MERGE operation. PostgreSQL
> doesn't have any native support to do this for you. Doing it right is
> surprisingly tricky. This is the best article I've seen on the topic:
>
> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

Though that's aiming for a perfectly universal solution. There are
simpler solutions that work in restricted circumstances; the easiest
may be simply:

SAVEPOINT tryinsert
INSERT .... -- as normal
-- if error:
ROLLBACK TO SAVEPOINT tryinsert

Question: Is it better to simply do the insert as-is, or to have a
WHERE clause that will often, though not always, prevent duplicate
insertions?

ChrisA