Обсуждение: utf8 database not dumping utf8 characters

On 04/06/2012 12:55 PM, Matt Williams wrote:

I have a database that is utf8 and displays utf8 values correctly in psql. When dumped, it displays the utf8 characters incorrectly. ie. ö turns into Ã

In the header of the dump file, I have:

SET client_encoding = 'UTF8';

So I'm not sure where the disconnect is?

Thoughts?

Thanks,

-- 

Matt Williams

Sent with Sparrow

With what are you viewing the dump file and is everything in the chain (terminal, less/vi/...) set to interpret/display that data as UTF8? You can always use a hex-dump program to see the actual bytes in the file and determine if they are what you expect for UTF8.

Cheers,
Steve

On Friday, April 6, 2012 at 4:03 PM, Steve Crawford wrote:

On 04/06/2012 12:55 PM, Matt Williams wrote:

I have a database that is utf8 and displays utf8 values correctly in psql. When dumped, it displays the utf8 characters incorrectly. ie. ö turns into Ã

In the header of the dump file, I have:

SET client_encoding = 'UTF8';

So I'm not sure where the disconnect is?

Thoughts?

Thanks,

-- 

Matt Williams

Sent with Sparrow

With what are you viewing the dump file and is everything in the chain (terminal, less/vi/...) set to interpret/display that data as UTF8? You can always use a hex-dump program to see the actual bytes in the file and determine if they are what you expect for UTF8.

Cheers,
Steve

On Friday, April 6, 2012 at 4:04 PM, Scott Whitney wrote:

What version, Matt? I had that problem back in 8.1x

---

I have a database that is utf8 and displays utf8 values correctly in psql. When dumped, it displays the utf8 characters incorrectly. ie. ö turns into Ã

In the header of the dump file, I have:

SET client_encoding = 'UTF8';

So I'm not sure where the disconnect is?

Thoughts?

Thanks,  

--  
Matt Williams
Sent with Sparrow (http://www.sparrowmailapp.com/?sig)

On 04/06/2012 01:10 PM, Matt Williams wrote:
> With that same dump file that is displaying incorrectly open in vim, I
> can paste in the utf8 character I provided as an example and it
> displays correctly.
I usually find a good first step is to run the file through something
that will give you a hex dump (i.e. xxd or similar) and so I *know* the
actual bytes in the file rather than relying on how they may be
interpreted somewhere else along the chain. Find the hex-byte(s) of your
suspect character and look it up.

Since you are in vim, it may be worth checking ":set termencoding",
":set encoding" and ":set fileencoding".

Cheers,
Steve

On Friday, April 6, 2012 at 6:19 PM, Steve Crawford wrote:

On 04/06/2012 01:10 PM, Matt Williams wrote:

With that same dump file that is displaying incorrectly open in vim, I

can paste in the utf8 character I provided as an example and it

displays correctly.

I usually find a good first step is to run the file through something

that will give you a hex dump (i.e. xxd or similar) and so I *know* the

actual bytes in the file rather than relying on how they may be

interpreted somewhere else along the chain. Find the hex-byte(s) of your

suspect character and look it up.

Since you are in vim, it may be worth checking ":set termencoding",

":set encoding" and ":set fileencoding".

Cheers,

Steve

Matt Williams wrote:
> I ran it through xxd and the hex-bytes are different than those of the proper utf8 character:
> 
> 03300a0: 7472 c383 c2b6 6d65 7209 3009 5c4e 0931  tr....mer.0.\N.1 (from the dump file)
> 
> ö : c3b6
> 
> ö : c383 c2b6

That looks like your database does not contain what you think it does.

True, there *are* UTF-8 characters in it, but not the ones you want,
even though everything looks OK on the surface.

Imagine this scenario:
- Database server encoding is UTF8
- Database client encoding is LATIN1
- Application feeds UTF-8 into PostgreSQL.

This can easily happen if the locale of the postgres user account
is ISO8859-1 and the application did not set the PGCLIENTENCODING
environment variable.

The Application stores 'trömer', i.e passes the following bytes to PostgreSQL:
74 72 c3 b6 6d 65 72

PostgreSQL client interprets these bytes as LATIN1, i.e. 'trömer'.

This is converted to UTF-8 and stored in the database as
74 72 c3 83 c2 b6 6d 65 72

When the application retrieves the string, it will get back what
it originally stored, and everybody is happy, that is until somebody
looks closer or wonders why full text search isn't working for
German umlauts.

As to fixing the situation (if the above is actually your problem),
dump the database with -E LATIN1, edit the dump, change LATIN1
to UTF8 in the "SET client_encoding" statement and load it again.

Yours,
Laurenz Albe