Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I get the following error when inserting data from mysql into postgres.

PG::Error: ERROR:  invalid byte sequence for encoding "UTF8": 0xe073
: INSERT INTO "places" ("accent_city", "city", "country", "created_at", "latitude", "longitude", "region", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"

I want to insert this file worldcitiespop.txt in my PG database as a rake task

namespace :places_db do
    desc "save cities in database"
    task save_places: :environment do
        File.open("lib/city_name/worldcitiespop.txt", "r").each_line do |row|
            row = row.force_encoding('ISO-8859-1').split(',')
            Place.create(country: row[0], city: row[1], accent_city: row[2], region: row[3], latitude: row[5], longitude: row[6])
        end 
    end
end
share|improve this question
What are you using to insert the data, and what character are you trying to insert? – Jon Skeet Oct 20 '12 at 8:32
(It's right in saying that 0xe073 is an invalid byte sequence in UTF-8 though...) – Jon Skeet Oct 20 '12 at 8:33
I updated my question – nadine1988 Oct 20 '12 at 8:47
The fact that you're explicitly using ISO-8859-1 in the Ruby code is somewhat worrying... it does sound like it's the Ruby/PG interaction which is the problem here. – Jon Skeet Oct 20 '12 at 9:40

1 Answer

up vote 1 down vote

Since the file is encoded with ISO-8859-1, the simplest way is to let the database do the conversion by issuing the following SQL query just before importing:

SET client_encoding=latin1;

To go back to the previous encoding after the import:

SET client_encoding=default;

This setting affects only the current session.

share|improve this answer
Between "row = row.force_encoding('ISO-8859-1').split(',')" and "Place.create..."? – nadine1988 Oct 22 '12 at 8:50
It would probably work, but doing it for each line would be overkill. I don't know ruby so not sure about the syntax, but this should be added outside of the loop. – Daniel Vérité Oct 22 '12 at 9:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.