Trying to insert into postgresql: getting error

Question

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

Please provide the data you are inserting in the line you get the error. — Angelo Fuchs
– Angelo Fuchs, Commented May 3, 2012 at 17:20
This entry caused the issue Ron􏻊Zalko􏻊Total Body Fitness & Yoga — banditKing
– banditKing, Commented May 3, 2012 at 17:42
You can open your csv file in a text editor that tells you what encoding you see (and you can modify it. Try gVim you can change the encoding with :encoding UTF-8) and then you'll see what encoding you try to put into the database. — Angelo Fuchs
– Angelo Fuchs, Commented May 3, 2012 at 17:49
Great. Thanks for pointing me in that direction. Looks like Il have to play around with it. Appreciate your help. — banditKing
– banditKing, Commented May 3, 2012 at 17:57
PostgreSQL has very strict checking of UTF8 codes - so there are two possibilities: a) you have wrong specification of client encoding, b) you have broken data — Pavel Stehule
– Pavel Stehule, Commented May 3, 2012 at 18:00

Daniel Lyons · Accepted Answer · 2012-05-03 17:27:56Z

1

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.

answered May 3, 2012 at 17:27

Daniel Lyons

22.9k3 gold badges54 silver badges82 bronze badges

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

2 Comments

banditKing Over a year ago

Hi Thanks for your response. Im very new to this. This is my first time performing this action. The data orginated in a excel file was then converted to a .csv which was imported into a sqlite3 db and now its being inserted into a pgsql. It didn't originate on a webpage. Im trying to prepopulate my rails app with this data. So I wasn't aware of this until it actually broke. Since Im just learning could you please tell me how I can inspect my rows in the db for incorrect formats? I mean when I look at the row how would I know that it isn't in the righ format?what format is the right format?

Daniel Lyons Over a year ago

Open the CSV file in your text editor in UTF-8 encoding. Look for a problem character. Re-open the file in different encodings until the character looks right. Then use iconv from the command line to convert from that encoding to the right one. Then reimport into sqlite and then it should reimport to PostgreSQL successfully.

derobert · Accepted Answer · 2012-05-03 17:33:24Z

0

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.

answered May 3, 2012 at 17:33

derobert

51.4k15 gold badges98 silver badges126 bronze badges

3 Comments

banditKing Over a year ago

Thanks, yes. Im manually going through the databases. In the future, I will check for the character encodings of the db before migrations. In sqlite browsers any idea where I can find this?

derobert Over a year ago

@banditKing Well, if you find that row in your SQLite browser, it'll probably display weirdly. But I think you'd be better off doing this in Ruby; just check each character column (of each row) to see if its valid UTF-8, and spit out the primary key of any column that isn't, so you can manually investigate. Depending on how bad it is, you may find you have to manually check the entire database—or, considering your description of where the data came from, fix the from-Excel import.

banditKing Over a year ago

Thanks for your pointers. Yes, I will need to work on it a bit. This was my 1st time, I had no idea. I will keep thins in mind. Appreciate your help.

Collectives™ on Stack Overflow

Trying to insert into postgresql: getting error

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related