Search Postgresql Archives

Re: encoding confusion

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Sim Zacks wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

{BACKGROUND]
I am testing dbmail for our corporate email solution.

We originally tested it on mysql and now we are migrating it to postgresql.

The messages are stored in a longblob field on mysql and a bytea field
in postgresql.

I set the database up as UTF-8

Not relevant if you're using bytea. Encoding is for character-based types (varchar, text) not byte-based types.

[snip]
When I used pygresql's escape_bytea function to copy the data, it went
smoothly, but the data was corrupt.
When I tried the escape_string function it died because the data it was
moving was not UTF-8.

Your Python script seems to think it's dealing it's dealing with text rather than a stream of bytes too. I'm not a Python programmer, but I'd guess it's treating one of the database fields (either MySQL or PostgreSQL) as text not bytes. You'll need to check the docs for binary-data handling in your Python libraries.

I'm puzzled as to how the data was corrupted with escape_bytea() - I can't imagine it would be that difficult for the library to get right. I'd be suspicious that the source data wasn't what I thought it was.

[CONFUSION]
What I don't understand, is that if that database can't handle the non
UTF characters, how does it allow them in when I receive an email
(tested, it works) and when I restored the backup.
I also don't understand why the data transfer didn't work to a UTF
database, but it worked to an ASCII database, if the data can go into a
UTF database from an ascii database.

Whatever is going on, it's nothing to do with the bytea type.

Lastly, I wanted to know if anybody has experience moving data from
mysql to postgresql and if what I did is considered normal, or if there
is a better way of doing this.

I think that something in the process is trying to be clever and treating the blobs as text.

--
  Richard Huxton
  Archonet Ltd


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux