Jason’s postings and stuff

I admit it. I am bad. I run raw SQL in my migrations. The dataset I am working with requires it of me. It must be punished. To aid in its punishment, I found a post explaining how to get at PostgreSQL’s COPY from within the Postgres Ruby driver.

For a Rails migration, there isn’t much extra to do.

class PunishMe < ActiveRecord::Migration
def self.up
	c = Property.connection.raw_connection
	c.exec(%q{COPY cities (city, state_id, lat, long) FROM STDIN})
	data = File.open(File.dirname(__FILE__) + '/places_cities.pgdump') {|f| f.read }
	data.each_line {|line| c.putline(line) }
	c.endcopy
end
end #class

To obtain a suitable dataset, tab delimited by default, you may call upon psql.

$ echo 'COPY places_cities TO STDIN;' |\
  psql db > places_cities.pgdump
$head -1 places_cities.pgdump
Lowgap  Populated Place NC      36.5256863      -80.8672961