Insert or update postgresql en mass

Question

I have 16000 items in a ruby hash. (downloaded as XML and converted to hash) I need to push these into the database. Once a week some of them change, but I can't tell which ones.

What I've seen suggested is to go right to SQL, because active record was (on that site) 70 times slower on just a straight insert.. Not even thinking about the update/insert

I'm wondering what approach would be best.. Has anyone received a huge (or well smallish) chunk of data that they had to repeatedly insert/update?
Could you offer suggestions.

Falco · Accepted Answer · 2012-03-01 01:01:55Z

2

The fastest way to load massive amounts of data into PostgreSQL is the COPY command.

Just generate a delimited file with all data, TRUNCATE your table, drop any indexes and constraints, and then load your data with COPY.

After this, run ANALYZE on the target table, and then create indexes and constraints.

http://www.postgresql.org/docs/9.1/static/sql-copy.html

answered Mar 1, 2012 at 1:01

Falco

1628 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:03:12Z

1

I have a very similar use case. I read the XML file into the database directly, parse it with xpath() into a temporary table and do all the checking and upgrading with good all SQL. Works very well (and fast) for me.

I posted the code for that recently in a related answer here.
In case you have to deal with non-unique items in XML nodes, here is some more.

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Mar 1, 2012 at 0:47

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Insert or update postgresql en mass

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related