Menu
I am inserting record data in a collection in memory into postgres and want the database to ignore any record that already exists in the database (by virtue of having the same primary key) but keep going with the rest of my inserts.
I'm using clojure and hugsql, btw, but I'm guessing the answer might be language agnostic.
As I'm essentially treating the database as a set in this way I may be engaging in an antipattern.
609
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
If you choose Ignore duplicate key, SQL Server will issue a warning message, ignore the offending incoming row and try to insert the remaining rows of the bulk insert operation. If you do not choose Ignore duplicate key, SQL Server will issue an error message and roll back the entire bulk insert operation.
If you're using Postgres 9.5 or newer (which I assume you are, since it was released back in January 2016), there's a very useful ON CONFLICT cluase you can use:
INSERT INTO mytable (id, col1, col2)
VALUES (123, 'some_value', 'some_other_value')
ON CONFLICT (id) DO NOTHING
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
