Ruby PG Gem example of inserting multiple records with a single query

Question

I don't see any methods that specifically perform multiple inserts at once. I suppose you could create the statement string (with bind parameters) and params array iteratively, but there has to be a better way, right?

Answer 1

Assuming you're inserting data from outside the database, there is a better way: the COPY command.

The pg gem has direct support for this via the PG::Connection#put_copy_data and #put_copy_end methods. Here's a (stripped-down) example from the samples/ directory of the pg source:

#!/usr/bin/env ruby

require 'pg'
require 'stringio'

$stderr.puts "Opening database connection ..."
conn = PG.connect( :dbname => 'test' )

conn.exec( <<END_SQL )
DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
    client_ip inet,
    username text,
    ts timestamp,
    request text,
    status smallint,
    bytes int
);
END_SQL

copy_data = StringIO.new( <<"END_DATA" )
"127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /manual/ HTTP/1.1",404,205
"127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /favicon.ico HTTP/1.1",404,209
"127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /favicon.ico HTTP/1.1",404,209
"127.0.0.1","","30/Aug/2010:08:22:29 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:08:22:38 -0700","GET /manual/index.html HTTP/1.1",200,725
"127.0.0.1","","30/Aug/2010:08:27:56 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:08:27:57 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/index.html HTTP/1.1",200,7709
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/images/feather.gif HTTP/1.1",200,6471
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/images/left.gif HTTP/1.1",200,60
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/style/css/manual.css HTTP/1.1",200,18674
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/style/css/manual-print.css HTTP/1.1",200,13200
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/images/favicon.ico HTTP/1.1",200,1078
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/style/css/manual-loose-100pc.css HTTP/1.1",200,3065
"127.0.0.1","","30/Aug/2010:08:28:14 -0700","OPTIONS * HTTP/1.0",200,0
"127.0.0.1","","30/Aug/2010:08:28:15 -0700","OPTIONS * HTTP/1.0",200,0
"127.0.0.1","","30/Aug/2010:08:28:47 -0700","GET /manual/mod/directives.html HTTP/1.1",200,33561
"127.0.0.1","","30/Aug/2010:08:28:53 -0700","GET /manual/mod/mpm_common.html HTTP/1.1",200,67683
"127.0.0.1","","30/Aug/2010:08:28:53 -0700","GET /manual/images/down.gif HTTP/1.1",200,56
"127.0.0.1","","30/Aug/2010:08:28:53 -0700","GET /manual/images/up.gif HTTP/1.1",200,57
"127.0.0.1","","30/Aug/2010:09:19:58 -0700","GET /manual/mod/mod_log_config.html HTTP/1.1",200,28307
"127.0.0.1","","30/Aug/2010:09:20:19 -0700","GET /manual/mod/core.html HTTP/1.1",200,194144
"127.0.0.1","","30/Aug/2010:16:02:56 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:16:03:00 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:16:06:16 -0700","GET /manual/mod/mod_dir.html HTTP/1.1",200,10583
"127.0.0.1","","30/Aug/2010:16:06:44 -0700","GET /manual/ HTTP/1.1",200,7709
END_DATA

buf = ''
conn.transaction do
    conn.exec( "COPY logs FROM STDIN WITH csv" )
    begin
        while copy_data.read( 256, buf )
            until conn.put_copy_data( buf )
                sleep 0.1
            end
        end
    rescue Errno => err
        errmsg = "%s while reading copy data: %s" % [ err.class.name, err.message ]
        conn.put_copy_end( errmsg )
    else
        conn.put_copy_end
        while res = conn.get_result
            $stderr.puts "Result of COPY is: %s" % [ res.res_status(res.result_status) ]
        end
    end
end


conn.finish

This copies many rows of data into a table in single statement. It's using a StringIO to simulate reading from a file, but you can (of course) read from a file on disk, a socket, or another data source that yields rows in a regular text format that you can send via #put_copy_data. This example uses CSV format, but you can also import it from custom data formats with a little more work. For example, if you had tab-separated columns, you can omit the WITH csv from the COPY logs FROM STDIN statement at the beginning, or you can also define custom column delimiters, character encodings, quote characters, etc.

If you'd rather use a higher-level library, Sequel has excellent support for this mechanism via the #copy_into method of Postgres-ish Sequel::Database objects.