Postgres COPY from STDIN

[Tim Uckun]

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.

[Philip Hallstrom]

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: http://stackoverflow.com/a/6780870/91830

-philip

[Tim Uckun]

> 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?