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 ...