using COPY FROM in a Rails app on Heroku with the Postgresql backend

Question

I want to give users the option of uploading files in my Ruby on Rails 3.2 application, with the data going into the db. I wanted to use the COPY FROM command since it is faster than inserting ruby objects.

If I do

User.connection.execute("COPY users (name, taxon_id, created_at, updated_at) FROM 'a.txt'")

I get

ActiveRecord::StatementInvalid: PG::Error: ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

However, \copy does not work in a db connection either. How do you easily upload data from flat files in Rails with PostgreSQL on Heroku? Can you get superuser permissions?

Use STDIN to read it... see stackoverflow.com/questions/6779925/… — Philip Hallstrom
– Philip Hallstrom, Commented Jan 25, 2013 at 17:27

highBandWidth · Accepted Answer · 2013-01-25 20:27:44Z

4

Thanks to @PhilipHallstrom's link, I used COPY FROM STDIN like this:

rc = User.connection.raw_connection
rc.exec("COPY users (name, taxon_id, updated_at, created_at) FROM STDIN")
begin
  until rc.put_copy_data( data )
    $stderr.puts "  waiting for connection to be writable..."
    sleep 0.1
  end
rescue Errno => err
  @errmsg = @errmsg + "%s while reading copy data: %s" % [ err.class.name, err.message ]
  error = true
else
  rc.put_copy_end
  while res = rc.get_result
    if (res.result_status != 1)
      error = true
      @errmsg = @errmsg + "Result of COPY is: %s" % [ res.res_status(res.result_status) ]
    end
  end
end

answered Jan 25, 2013 at 20:27

highBandWidth

17.4k23 gold badges87 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RubyRedGrapefruit Over a year ago

I don't understand. Where does "data" come from? What format must it be in? Is it an array? String? Is there a loop somewhere where the data is being read?

Fernando Fabreti Over a year ago

Get a better idea from where data variable comes from in here: kadrmasconcepts.com/blog/2013/12/15/…

Diogo Biazus · Accepted Answer · 2014-06-18 02:17:58Z

1

You can use the gem https://github.com/diogob/postgres-copy Assuming data is an IO object.

User.copy_from data, columns: [:name, :taxon_id, :updated_at, :created_at]

Or using the path to the uploaded file directly:

User.copy_from "/tmp/uploaded_file.csv", columns: [:name, :taxon_id, :updated_at, :created_at]

answered Jun 18, 2014 at 2:17

Diogo Biazus

6323 silver badges12 bronze badges

Collectives™ on Stack Overflow

using COPY FROM in a Rails app on Heroku with the Postgresql backend

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related