PgCsv

Fast Ruby PG csv export. Uses pg function 'copy to csv'. Effective on millions rows.

Gemfile:

gem 'pg_csv'

Usage:

PgCsv.new(opts).export(to, opts)

'to' is a stream or filename

Options:

:sql         => plain sql ("select id, name from users")
:connection  => ActiveRecord::Base.connection or PG::Connection(gem pg)
:delimiter   => ["\t", ",", ]
:header      => boolean, use pg header for fields?
:logger      => logger
:columns     => array of column names, ignore :header option
:encoding    => encoding (default is pg_default), list of encodings: http://www.postgresql.org/docs/8.4/static/multibyte.html#CHARSET-TABLE
:force_quote => boolean, force quotes around all non-NULL data?

:temp_file   => boolean, generate throught temp file? final file appears by mv
:temp_dir    => for :temp_file, ex: '/tmp'

:type        => :plain - return full string
             => :gzip  - save file to gzip
             => :stream - save to stream
             => :file - just save to file = default
             => :yield - return each row to block

Examples:

PgCsv.new(sql: sql).export('a1.csv')
PgCsv.new(sql: sql).export('a2.gz', type: :gzip)
PgCsv.new(sql: sql).export('a3.csv', temp_file: true)
PgCsv.new(sql: sql, type: :plain).export
File.open("a4.csv", 'a'){ |f| PgCsv.new(sql: "select * from users").\
    export(f, type: :stream) }
PgCsv.new(sql: sql).export('a5.csv', delimiter: "\t")
PgCsv.new(sql: sql).export('a6.csv', header: true)
PgCsv.new(sql: sql).export('a7.csv', columns: %w(id a b c))
PgCsv.new(sql: sql, connection: SomeDb.connection, columns: %w(id a b c), delimiter: "|").\
    export('a8.gz', type: :gzip, temp_file: true)

# example collect from shards
Zlib::GzipWriter.open('some.gz') do |stream|
  e = PgCsv.new(sql: sql, type: :stream)
  ConnectionPool.each_shard do |connection|
    e.export(stream, connection: connection)
  end
end

# yield example
PgCsv.new(sql: sql, type: :yield).export do |row|
  puts row
end