Re: Duplicate key insert question

On Tuesday 01 July 2003 08:10 pm, 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

Hi, not sure if this is answering your question, but I just asked similar
questions here. I asked about using INSERT WHERE NOT EXISTS (which you can do
in PostgreSQL). Here is what you can do:

INSERT INTO mytable
SELECT 'value1', 'value2'
WHERE NOT EXISTS
(SELECT NULL FROM mytable
WHERE mycondition)

This will just return 0 when fails, but it does check first. Don't know if you
can really afford that. Just for reference, this brought up some discussion
here. Here is a link to the archive:
http://marc.theaimsgroup.com/?l=postgresql-general&w=2&r=1&s=WHERE+NOT+EXISTS&q=b

Hope that helps.
RDB

--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------