pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedCheck before insert
3+ messages / 3 participants
[nested] [flat]
* Check before insert
@ 2005-03-15 21:07 SG Edwards <s0460205@sms.ed.ac.uk>
2005-03-15 21:51 ` Re: Check before insert Robert Perry <rlperry@lodestonetechnologies.com>
0 siblings, 1 reply; 3+ messages in thread
From: SG Edwards @ 2005-03-15 21:07 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Check before insert
2005-03-15 21:07 Check before insert SG Edwards <s0460205@sms.ed.ac.uk>
@ 2005-03-15 21:51 ` Robert Perry <rlperry@lodestonetechnologies.com>
2005-03-15 22:23 ` Re: Check before insert Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Perry @ 2005-03-15 21:51 UTC (permalink / raw)
To: 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'
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Check before insert
2005-03-15 21:07 Check before insert SG Edwards <s0460205@sms.ed.ac.uk>
2005-03-15 21:51 ` Re: Check before insert Robert Perry <rlperry@lodestonetechnologies.com>
@ 2005-03-15 22:23 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-03-15 22:23 UTC (permalink / raw)
To: Robert Perry <rlperry@lodestonetechnologies.com>; +Cc: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-15 22:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 21:07 Check before insert SG Edwards <s0460205@sms.ed.ac.uk>
2005-03-15 21:51 ` Robert Perry <rlperry@lodestonetechnologies.com>
2005-03-15 22:23 ` Michael Fuhr <mike@fuhr.org>
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