Skip site navigation (1) Skip section navigation (2)

Re: Check before insert

From: Robert Perry <rlperry(at)lodestonetechnologies(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: Check before insert
Date: 2005-03-15 21:51:20
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-interfaces
	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'
	not exists(select * from protein_table_name where protein_id = 

might meet your needs too.  (Note: I assumed that protein_id was what 
you wanted to keep 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

In response to


pgsql-interfaces by date

Next:From: Michael FuhrDate: 2005-03-15 22:23:29
Subject: Re: Check before insert
Previous:From: SG EdwardsDate: 2005-03-15 21:07:43
Subject: Check before insert

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group