Re: Easy form of "insert if it isn't already there"?

Hi,
similar topic is in NOVICE mailing list:
http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php

e.g. You can use BEGIN... EXCEPTION.... END, good example of
such approach is there:
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
;

Regards,
Bartek

2012/2/15 Chris Angelico <rosuav(at)gmail(dot)com>

> Periodically I find myself wanting to insert into some table,
> specifying the primary key column(s), but to simply ignore the request
> if it's already there. Currently I have two options:
>
> 1) Do the insert as normal, but suppress errors.
> SAVEPOINT foo;
> INSERT INTO table (col1,col2,col3) VALUES (val1,val2,val3);
> (if error) ROLLBACK TO SAVEPOINT foo;
>
> 2) Use INSERT... SELECT:
> INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT
> EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2)
>
> The former makes unnecessary log entries, the latter feels clunky. Is
> there some better way?
>
> All tips appreciated!
>
> Chris Angelico
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>