Thread

1. Errors attempting to insert duplicate values

   Bryan White <bryan@arcamax.com> — 2000-06-05T19:37:48Z

   I have a table with a unique index.  When I go to insert a new value into
   the table my code does not know if the record is already there.  To me there
   are two choices:
   
   1) Do a select for the record and insert only if not found.
   
   2) Do a blind insert and relay on the unique index to fail the insert if
   there is a duplicate.
   
   I have chosen to do #2 and it has worked well for a long time.  It seems
   better to keep the number of database interactions to a minimum.  The only
   problem is everytime a duplicate insertion is attempted an error is printed
   in the log file.
   
   The anal-retentive part of my personality is bugged by this.  What do other
   people think?  Is the technique legitimate and should the database be
   reporting errors for legitimate operations?
   
   
   

2. Re: Errors attempting to insert duplicate values

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-06-06T14:19:45Z

   Bryan White wrote:
   > 
   > I have a table with a unique index.  When I go to insert a new value into
   > the table my code does not know if the record is already there.  To me there
   > are two choices:
   > 
   > 1) Do a select for the record and insert only if not found.
   > 
   > 2) Do a blind insert and relay on the unique index to fail the insert if
   > there is a duplicate.
   
   3) Use a sequence to generate unique indicies for you?
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/
   
   

3. Re: Errors attempting to insert duplicate values

   Bryan White <bryan@arcamax.com> — 2000-06-09T14:09:09Z

   
   > Bryan White wrote:
   > >
   > > I have a table with a unique index.  When I go to insert a new value
   into
   > > the table my code does not know if the record is already there.  To me
   there
   > > are two choices:
   > >
   > > 1) Do a select for the record and insert only if not found.
   > >
   > > 2) Do a blind insert and relay on the unique index to fail the insert if
   > > there is a duplicate.
   >
   > 3) Use a sequence to generate unique indicies for you?
   
   Sorry but I was not clear.  I need to insert a record only if it does not
   exist.  If it does exists then I do nothing.  However I do need to know if
   it was inserted.  Its seems most efficient to just attempt the insert and
   look at the result.  My only complaint is the log file is littered the error
   messages about attempting to insert a duplicate.
   
   
   

4. Re: Errors attempting to insert duplicate values

   Ed Loehr <eloehr@austin.rr.com> — 2000-06-09T14:46:46Z

   Bryan White wrote:
   > 
   > I need to insert a record only if it does not
   > exist.  If it does exists then I do nothing.  However I do need to know if
   > it was inserted.  Its seems most efficient to just attempt the insert and
   > look at the result.  My only complaint is the log file is littered the error
   > messages about attempting to insert a duplicate.
   
   How about something like this?
   
   	INSERT INTO foo (id, ...)
   		SELECT 14, ... <=== literal insert values
   		FROM foo
   		WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 14)
   
   It'll insert 1 or 0 records, and it won't try to insert a duplicate...
   
   Regards,
   Ed Loehr