Re: check row before insert

On Tue, Mar 15, 2005 at 12:48:57 +0000,
SG Edwards <s0460205(at)sms(dot)ed(dot)ac(dot)uk> 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 preset in the database. Is there a way to
> do this using an SQL command?

If the inserts don't need to be done in an all or nothing transaction and
there aren't so many that performance is an issue, then the simplest thing
to do is to do the inserts one at a time and ignore the duplicate key
errors.

A more complicated option is to load the data into a separate table using
COPY and then insert tuples from this table that aren't already in the
main table using INSERT INTO ... SELECT from ... (WHERE NOT EXISTS ...).
If anyone else might be adding or removing records from the main table
at about the same time, you probably want to do a lock table on it to
prevent an insert failure failing the whole transaction.