CODEWITHSUNDEEP
ruby-on-railspostgresqlencodingutf-8data-migration
banditKing
banditKing

Reputation: 9579

Trying to insert into postgresql: getting error

On invocation of 

http://localhost:3000/tempbizs

Im having the index method of the tempbiz_controller migrate data from a legacy database (sqlite3) to another postgresql inside my rails app.

It has inserted a few records. However I see that it threw the following error which is showing up on my browser:

ActiveRecord::StatementInvalid in TempbizsController#index

PG::Error: ERROR:  invalid byte sequence for encoding "UTF8": 0xca5a
: INSERT INTO "tempbizs" ("BusinessName", "BusinessSubType", "BusinessTradeName", 
"BusinessType", "City", "Country", "House", "Latitude", "LocalArea", "Longitude",     
"ZIP", "State", "Street", "Unit", "UnitType", "created_at", "updated_at") 
VALUES     ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17) 
RETURNING "id"

The data I'm trying to insert is: Ron􏻊Zalko􏻊Total Body Fitness & Yoga

Upvotes: 0

Views: 1137

Answers (2)

derobert
derobert

Reputation: 51147

You've told PostgreSQL that one of your varchar columns is encoded in UTF-8. However, the data you're trying to put into it (0xCA5A) isn't valid UTF-8.

So, you'd need to figure out what encoding that value actually is in, then convert before inserting (or, alternatively, tell PostgreSQL to use that encoding).

If 0xCA5A isn't supposed to be text, then you need to use a binary—not text—type in PostgreSQL.

You didn't run into this in SQLite because SQLite doesn't do much as far as enforcing data validity.

Its also possible that 0xCA5A is just corruption, because it doesn't look right in any common encoding:

  • ISO-8859-1, -15, and cp1252: ÊZ
  • UTF16 (big endian): 쩚
  • UTF16 (little endian): 嫊
  • shiftjis: ﾊZ

If its just corruption, you'll need to filter out the invalid records.

Upvotes: 0

Daniel Lyons
Daniel Lyons

Reputation: 22803

You have text in a different encoding than UTF-8 and you're trying to insert it into a UTF-8 database. SQLite doesn't do anything special for text encodings, so the data is fine in SQLite but not valid for PostgreSQL. You need to find records that are not really UTF-8 and convert them manually in order to migrate the data.

The underlying problem is that your application isn't handling encodings properly. The user was able to submit data in a different encoding than UTF-8 and it made it all the way to your database. You need to go through and make sure your pages are being rendered in UTF-8 and the browser is using that encoding for form submissions. You may need to check your model too. Look everywhere this table is accessed. I doubt you're going to find a magic solution on S.O. other than go digging through your code and double check everything.

Upvotes: 1

Related Questions