Postgres COPY from STDIN

rubyonrails-talk
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

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?