Postgres COPY from STDIN

Rails
1

I have done some googling to try and figure out how I can use COPY
FROM STDIN with rails and have ran into some posts on stackoverflow or
the mailing list but none of them seem to be working with rails 3.2
Does anybody have a working example of using COPY FROM STDIN?

Some things I have tried

https://bitbucket.org/ged/ruby-pg/src/tip/sample/copyfrom.rb
http://blog.edseek.com/archives/2009/04/26/putline-undefined-for-pgconn/

and similar variations. Nothing seems to be working though.

Thanks.

2

On Mar 20, 2012, at 8:49 PM, Tim U. wrote:

and similar variations. Nothing seems to be working though.
I didn’t do this on 3.2, but 3.0.x… not sure if this is one of the
stackoverflow options you tried or not…

conn = ActiveRecord::Base.connection_pool.checkout
raw = conn.raw_connection
raw.exec(“COPY tablename (col1, col2, col3) FROM STDIN”)

open up your CSV file looping through line by line and getting the

line into a format suitable for pg’s COPY…
rc.put_copy_data line

once all done…

rc.put_copy_end
while res = rc.get_result do; end # very important to do this after a
copy
ActiveRecord::Base.connection_pool.checkin(conn)

Source: Problems with postgresql COPY command with Rails on different server - Stack Overflow

-philip

3

conn = ActiveRecord::Base.connection_pool.checkout
raw = conn.raw_connection
raw.exec(“COPY tablename (col1, col2, col3) FROM STDIN”)

open up your CSV file looping through line by line and getting the line into a

format suitable for pg’s COPY…

rc.put_copy_data line

once all done…

rc.put_copy_end
while res = rc.get_result do; end # very important to do this after a copy
ActiveRecord::Base.connection_pool.checkin(conn)

I did try that and it doesn’t work. Not only doesn’t it work but it
fails silently. I wrote a more elaborate routine which I can get to
run without errors but it still doesn’t add the records in the table.

conn.transaction do
rc = conn.raw_connection
rc.exec “TRUNCATE TABLE #{table_name};” if options[:truncate]
sql = "COPY #{table_name} (#{field_list.join(’,’)}) FROM STDIN
#{sql_parameters} "
p sql
rc.exec(sql)
begin

      if method == 1
        rc.put_copy_data text + "\\.\n"
      else
        text.each_line { |line| rc.put_copy_data(line) }
      end
    rescue Errno => err
      errmsg = "%s while reading copy data: %s" % [err.class.name,

err.message]
puts “an error occured”
end
if errmsg
rc.put_copy_end(errmsg)
puts “ERROR #{errmsg}”
else
rc.put_copy_end
end
while res = rc.get_result
puts “Result of COPY is: %s” %
[res.res_status(res.result_status)]
end
puts “end”
end #transaction

Maybe it’s a bug in the PG gem?