4

We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like:

SELECT symptoms from client
- -
"huvudvärke"
- -

where "ä" should be "ä". I could handle it with replace, ie.:

UPDATE client SET symptoms=replace(symptoms, 'ä', 'ä');

I tried a couple of solutions I found, like using convert and convert_to but that leaves the data looking like "huvudv\303\244rke".

Is there an existing solution to my problem?

EDIT:

SELECT symptoms, symptoms::bytea from client
- -
"huvudvärke";"huvudv\303\203\302\244rke" 
- -

After the UPDATE above, query with symptoms::bytea outputs:

SELECT symptoms, symptoms::bytea from client
- -
"huvudvärke";"huvudv\303\244rke" 
- -

EDIT2:

select version();
"PostgreSQL 9.2.9, compiled by Visual C++ build 1600, 64-bit"

show client_encoding; --I'm using pgAdmin III 1.18.1
"UNICODE"

show server_encoding;
"UTF8"

SELECT symptoms, convert(convert_to(symptoms, 'utf-8'), 'latin-1', 'utf-8')::text from client;
"huvudvärke","huvudv\303\203\302\203\303\202\302\244rke"
  • 1
    What're the actual bytes from the string? Show symptoms::bytea please. – Craig Ringer Oct 29 '14 at 9:01
  • Updated the requested info to the original question. – James Brown Oct 30 '14 at 7:47
  • Ah, you're also on an old PostgreSQL version, since bytea_output seems to be set to escape not the new hex format. Please always mention your PostgreSQL version in questions. – Craig Ringer Oct 30 '14 at 9:14
  • Show SHOW client_encoding; from the client, and SHOW server_encoding;, also SELECT version() and from your terminal run the locale command please. – Craig Ringer Oct 30 '14 at 9:18
  • I updated the question regarding your request. Sorry for the lack of them. – James Brown Oct 30 '14 at 10:15
7

It looks like whatever client you are using is confused about the text encoding; it's sending utf-8 bytes as if they were latin-1, probably.

Check:

  • SHOW client_encoding;
  • SHOW server_encoding;
  • locale command in your terminal, if using psql

Your update is substituting the octal bytes \303\244 which are the utf-8 encoding for "ä" (U+00E4). You're not substituting latin-1 encoded data where you think you are.

Observe:

regress=> SELECT convert_from(BYTEA 'huvudv\303\244rke', 'latin-1');
 convert_from 
--------------
 huvudvärke
(1 row)

regress=> SELECT convert_from(BYTEA 'huvudv\303\244rke', 'utf-8');
 convert_from 
--------------
 huvudvärke
(1 row)

Not only that, but your replace could only have matched in the first place if the replace target was the utf-8 encoded byte sequence for ä interpreted as latin-1, i.e. \303\203\302\244.

It's hard to be more specific without details about the Pg version, the client being used, etc, but the root cause is clearly your client doing something totally borked with encodings on I/O.

Your original text is totally mangled, it's not valid in UTF-8 or latin-1. It looks like someone's taken some UTF-8 data, decoded it as latin-1, and then encoded it as utf-8 again.

Yep, sure enough:

regress=> SELECT convert(convert_to('huvudvärke', 'utf-8'), 'latin-1', 'utf-8');
          convert          
---------------------------
 huvudv\303\203\302\244rke
(1 row)

there's your explanation.

I'd say you probably have a bunch of mis-encoded data in the DB already, and you noticed this one because it got mangled twice. You're probably doing something like routinely jamming utf-8 bytes into latin-1 encoded fields, but you usually get away with it because you decode them as utf-8 again - and this time you did something different.

If you want to get from your mangled text to the original, you simply have to reverse the incorrect encoding process. Decode the utf-8 and output latin-1, then re-interpet the latin-1 as utf-8 and decode again, e.g:

regress=> SELECT  convert_from(convert(BYTEA 'huvudv\303\203\302\244rke', 'utf-8', 'latin-1'), 'utf-8');
 convert_from 
--------------
 huvudvärke
(1 row)
  • Yeah, this used to be (or maybe still is) a problem for Hungarian texts. For a time, you could see many websites with the same misinterpreted characters (well, the browsers were also confused...) Once I could relatively easily read all these ä and similar vowels :) – dezso Oct 30 '14 at 10:09
0

Convert string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-7 for available conversions.

See Other String Functions

convert(string bytea, src_encoding name, dest_encoding name)
convert_to(text_in_database,'LATIN1')
  • It did not work, like I mentioned in my original question. 'ä' only converts to '\303\244'. – James Brown Oct 29 '14 at 8:01
  • 1
    Good try, but you should actually test the answer. You'll find that the text provided is not valid latin-1 or utf-8, per my answer. – Craig Ringer Oct 30 '14 at 9:24
  • 1
    I did. The test was included in my original question. – James Brown Oct 30 '14 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.