very basic SQL question
Hi,
I'm using Pg for bioinformatic work and I want to be able to insert,
uniquely, biological sequences into a table returning the sequence id -
this part is fine. However, if the sequence already exists in the table
I want to return to id.
At the moment it seems to me that I should do a
SELECT id FROM table WHERE seq = '<sequence>';
and if that gives NULL then
INSERT .... RETURNING id;
But this seems long winded. Is there a better way to achieve what I
want?
thanks
A similar question was discussed here about 3 weeks ago :
http://archives.postgresql.org/pgsql-general/2010-11/msg00110.php
The "UPSERT" facility not being implemented yet, I don't think it is
possible to do much better than what you came up with.
Assuming the column(s) holding the distinctive sequence data are defined
with the UNIQUE constraint, you could adapt the example function given
in the link to the documentation in the previous thread to achieve what
you want.
Hope this helps,
Matthieu
Le 23/11/2010 04:39, Dan Kortschak a écrit :
Show quoted text
Hi,
I'm using Pg for bioinformatic work and I want to be able to insert,
uniquely, biological sequences into a table returning the sequence id -
this part is fine. However, if the sequence already exists in the table
I want to return to id.At the moment it seems to me that I should do a
SELECT id FROM table WHERE seq = '<sequence>';
and if that gives NULL then
INSERT .... RETURNING id;
But this seems long winded. Is there a better way to achieve what I
want?thanks
On Tue, Nov 23, 2010 at 02:09:19PM +1030, Dan Kortschak wrote:
I want to be able to insert,
uniquely, biological sequences into a table returning the sequence id -
this part is fine. However, if the sequence already exists in the table
I want to return to id.
The term you're looking for is "UPSERT", the following looks relevant:
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
--
Sam http://samason.me.uk/
Thanks for that - yes very helpful. Good to know what is possible.
Dan
Show quoted text
On Tue, 2010-11-23 at 10:27 +0100, Matthieu Huin wrote:
A similar question was discussed here about 3 weeks ago :
http://archives.postgresql.org/pgsql-general/2010-11/msg00110.phpThe "UPSERT" facility not being implemented yet, I don't think it is
possible to do much better than what you came up with.Assuming the column(s) holding the distinctive sequence data are defined
with the UNIQUE constraint, you could adapt the example function given
in the link to the documentation in the previous thread to achieve what
you want.Hope this helps,
Matthieu