Bulk upsert with Ruby on Rails

Question

I have a Rails 3 application where I need to ingest an XML file provided by an external system into a Postgres database. I would like to use something like ActiveRecord-Import but this does not appear to handle upsert capabilities for Postgres, and some of the records I will be ingesting will already exist, but will need to be updated.

Most of what I'm reading recommends writing SQL on the fly, but this seems like a problem that may have been solved already. I just can't find it.

Thanks.

Answer 1

You can do upserting on MySQL and PostgreSQL with upsert.

If you're looking for raw speed, you could use nokogiri and upsert.

It might be easier to import the data using data_miner, which uses nokogiri and upsert internally.

Answer 2

If you are on PostgreSQL 9.1 you should use writeable common table expressions. Something like:

WITH updates (id) AS (
     UPDATE mytable SET .....
      WHERE ....
     RETURNING id
)
INSERT INTO mytable (....)
SELECT ... 
  FROM mytemptable
 WHERE id NOT IN (select id from updates);

In this case you bulk process thins in a temp table first, then it will try to update the records from the temptable according to your logic, and insert the rest.