Postgres error on insert - PG::Error: ERROR: invalid byte sequence for encoding "UTF8": 0xe073

Question

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

What are you using to insert the data, and what character are you trying to insert? — Jon Skeet
– Jon Skeet, Commented Oct 20, 2012 at 8:32
(It's right in saying that 0xe073 is an invalid byte sequence in UTF-8 though...) — Jon Skeet
– Jon Skeet, Commented Oct 20, 2012 at 8:33
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
– Jon Skeet, Commented Oct 20, 2012 at 9:40

Daniel Vérité · Accepted Answer · 2012-10-20 11:08:00Z

1

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.

answered Oct 20, 2012 at 11:08

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

2 Comments

nadine1988 Over a year ago

Between "row = row.force_encoding('ISO-8859-1').split(',')" and "Place.create..."?

Daniel Vérité Over a year ago

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.

Collectives™ on Stack Overflow

Postgres error on insert - PG::Error: ERROR: invalid byte sequence for encoding "UTF8": 0xe073

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related