Thoughtlessly Thought Thoughts

I stumbled over this and could not find a good example for it so here is my solution.

If you want to import a lot of data into a PostgreSQL database then most propably you want to use one COPY … FROM statement instead of a INSERT statement for every record to create. Let’s assume we want to import some data from Ruby using the raw postgres library.

You can COPY from file which is propably the simplest way of using it:

COPY articles
FROM FILE 'articles.csv'
WITH DELIMITER AS ',';

However, this only works if you are a PostgreSQL super user and the file is on the server. So: How do we solve it? We use COPY FROM STDIN! But, wait… we are inside our Ruby script - what is STDIN? Reading PostgreSQL’s documentation yields that it is the connection from our client library to the server. How can we write to this stream?

The solution is to send the COPY … FROM STDIN statement to the server. The server will then wait for the data. We send the CVS data with PGconn#putline and close the data submission with PGConn#endcopy. That’s it.

Let’s look at an example:

require 'postgres'
conn = PGconn.open('dbname' => 'my_database')
res  = conn.exec(%Q{COPY articles FROM STDIN WITH DELIMITER AS ','})

file_contents = File.open('articles.csv', 'r') { |f| f.read }
file_contents.each_line { |line| conn.putline(line) }
conn.endcopy
conn.close

This entry was posted on Friday, January 5th, 2007 at 7:25 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.