Thread: 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

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/

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 :
> 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
>
>

Thanks for that - yes very helpful. Good to know what is possible.

Dan

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.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