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