On 2012-02-15, Chris Angelico <rosuav@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
