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

On 2012-02-15, Chris Angelico <rosuav@gmail.com> wrote:
> 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?

neither of those work all of the time.

It's not until the transaction is committed that you can know that it
was successful (ignoring 3-phase for the sake of clarity)

the best way is probably method 2 but remember to handle the errors
that you will still get sometimes.

--
⚂⚃ 100% natural