From: Robert Perry <rlperry@lodestonetechnologies.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: Check before insert
Date: Tue, 15 Mar 2005 16:51:20 -0500
Message-ID: <55762d9369dc9fd5b85b8662ccdf8557@lodestonetechnologies.com> (raw)
In-Reply-To: <1110920863.42374e9f75aa1@sms.ed.ac.uk>
References: <1110920863.42374e9f75aa1@sms.ed.ac.uk>
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'
where
not 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: rlperry@lodestonetechnologies.com
Subject: Re: Check before insert
In-Reply-To: <55762d9369dc9fd5b85b8662ccdf8557@lodestonetechnologies.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox