Re: Duplicate key insert question

On Wed, 2 Jul 2003, Jean-Christian Imbeault wrote:

> I have a table with a primary field and a few other fields. What is the
> fastest way to do an insert into that table assuming that sometimes I
> might try to insert a record with a duplicate primary key and want that
> to fail?
>
> I know that if I try a plain insert this will work, but in the case
> where I am trying to insert a duplicate key, the insert fails (as it
> should) and an error is logged.
>
> I could first do a check to see if there is already an entry with the
> same key as the one I am trying to insert but that would mean quite a
> few extra operations.
>
> Is there a quick and clean way of doing something like:
>
> INSERT into table values(...) IF there isn't already a row with pkey=x
>
> Thanks,
>
> Jean-Christian Imbeault
>
> PS The reason I am asking is that an open source project is using MySQL
> as their DB and they have a port to PG that isn't very clean b/c the DB
> code keeps trying to insert duplicate primary keys. According to them
> MySQL doesn't complain and just drops the insert whereas PG (as is
> right) complains. I've offered to clean up their PG insertion code but
> they say that they don't want too many extra checks as their app writes
> to the DB a *lot* and any extra check is going to slow down the
> application noticeably ...

Skipping ahead without reading all the messages in this thread (got to rush
out) what about using a before insert trigger, doing the check in there,
returning null if the insert would fail and see if they complain about the slow
down :)

Of course it's still got the race condition for the application unless you also
lock the table and it'll screw up any use of currval(sequence) afterwards that
expects to get the id of the row inserted with a id obtained from
nextval(sequence)

Nigel Andrews