Duplicate key insert question

[Jean-Christian Imbeault]

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 ...
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Wed, Jul 02, 2003 at 12:08:56 +0900,
Jean-Christian Imbeault <[e-mail]> wrote:

TAL=# insert into b select 'b', select 'b';
ERROR: parser: parse error at or near "select" at character 27

You probably want:
insert into b select 'b', 'b';

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Nigel J. Andrews]

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(sequenc e) afterwards that
expects to get the id of the row inserted with a id obtained from
nextval(sequenc e)

Nigel Andrews

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)