Обсуждение: Check before insert

Hi,

I have a table as follows:

protein_id | name
___________ ______

P04667      Albumin
P45366      Lactoglobulin
....
etc

I have a perl script that will insert data into this table from a file
containing a list of protein_id and names. However, I only want to insert
proteins where they are not already present in the database. Is there a way to
do this using an SQL command?


Thanks

    Firstly if it is important to only insert unique rows then I recommend 
using a unique constraint.  Creating a primary key will also accomplish 
this.  I do this even if I am going to be writing software to only 
insert unique data.  (I that way if I mess up I find out about it) I 
imagine that sooner or later you will be wanting to join other tables 
with this one or at least find records out of a long list of these 
things, so you will be needing an index anyway.  A unique constraint or 
primary key will create one for you automatically, just because they 
each (unique constraints and primary keys) need an index to find the 
record in a timely manor.
Secondly, no I do not know of a single command that will do this.  If 
performance is important I would write a stored proc to check to see if 
it exists and inserts it if it does not.
Note: After you do these do these I would be sure to call analyze 
after a few thousand records have been inserted, otherwise performance 
will probably go down hill fast.

on second thought, I guess something like

Insert (protein_id, name)
select 'P04667', 'Albumin'
wherenot exists(select * from protein_table_name where protein_id = 
'P04667')

might meet your needs too.  (Note: I assumed that protein_id was what 
you wanted to keep unique....you can keep either or both unique)
I would still put it in a stored proc.  (I like stored procs)

On Mar 15, 2005, at 4:07 PM, SG Edwards wrote:

>
>
> Hi,
>
> I have a table as follows:
>
> protein_id | name
> ___________ ______
>
> P04667      Albumin
> P45366      Lactoglobulin
> ....
> etc
>
> I have a perl script that will insert data into this table from a file
> containing a list of protein_id and names. However, I only want to 
> insert
> proteins where they are not already present in the database. Is there 
> a way to
> do this using an SQL command?
>
>
> Thanks
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Tue, Mar 15, 2005 at 04:51:20PM -0500, Robert Perry wrote:

> Insert (protein_id, name)
> select 'P04667', 'Albumin'
> where
>     not exists(select * from protein_table_name where protein_id = 
> 'P04667')

This should work if concurrency isn't an issue.  But if two concurrent
transactions execute the same statement, then they might both find
no existing row and thus both attempt the insert.  In that case,
in the presence of a unique index, one of the inserts will succeed
and the other transaction will block pending the first transaction's
completion.  If the first transaction rolls back then the second's
insert will succeed, but if the first transaction commits then the
second will fail with a duplicate key violation.  A program should
therefore be prepared to handle this situation.  In 8.0 and later
you could use a savepoint or a PL/pgSQL exception handler to recover
from the error without aborting the entire transaction.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/