Thread: LATIN9 - hex in varchar after convert

Postgresql 11.7 - client/server encoding LATIN9

Good morning,

I have made a couple of stupid mistakes.

I have a table with a varchar(5000) that contains general text. The table is typically maintained via a GUI, but on this occasion I received a spreadsheet with data and loaded it - via copy - from a csv extracted from that. The data looked fine in psql, but when looking at the data in the GUI, characters such as single quote marks (') appeared as a series of special characters. I assumed that the spreadsheet then had some different encoding (UTF8?) and that I then needed to 'translate' the characters.

I saw there was a convert_to/from function in postgresql, and thought I'd try that to see what it would do, but (the two mistakes) I didn't start a transaction and where I had intended to run the command on one row, I had a brain freeze and ran the update across the table.

The command was:

update gamespubquiz set question = convert_to(question,'LATIN9');

What I now appear to have in the question column is the hex equivalent of what was there originally.

Two questions:

1) How can I convert the hext back to 'text'?

2) How can I convert the (what appears to be) incorrect coding of the single quote (')?

Thanks in advance,

Steve T

"Steve Tucknott (TuSol)" <steve@tusol.co.uk> writes:
> I saw there was a convert_to/from function in postgresql,  and thought
> I'd try that to see what it would do, but (the two mistakes) I didn't
> start a transaction and where I had intended to run the command on one
> row, I had a brain freeze and ran the update across the table.
> The command was:
> update gamespubquiz set question = convert_to(question,'LATIN9');

Ooops.

> Two questions:
> 1) How can I convert the hext back to 'text'?
> 2) How can I convert the (what appears to be) incorrect coding of the
> single quote (')?

Don't have an answer for (2) offhand, but you can undo (1).
What bit you is that there's an implicit cast from text to
bytea, but not vice versa.

regression=# create table foo (f1 text);
CREATE TABLE
regression=# insert into foo values ('this is a test');
INSERT 0 1
regression=# update foo set f1 = convert_to(f1,'LATIN9');
UPDATE 1
regression=# table foo;
               f1
--------------------------------
 \x7468697320697320612074657374
(1 row)

You can't just apply convert_from:

regression=# update foo set f1 = convert_from(f1,'LATIN9');
ERROR:  function convert_from(text, unknown) does not exist
LINE 1: update foo set f1 = convert_from(f1,'LATIN9');
                            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

but forcing it with a cast works:

regression=# update foo set f1 = convert_from(f1::bytea,'LATIN9');
UPDATE 1
regression=# table foo;
       f1
----------------
 this is a test
(1 row)

As for (2), maybe replace() would help you.

            regards, tom lane

"Steve Tucknott (TuSol)" <

steve@tusol.co.uk

> writes:

I saw there was a convert_to/from function in postgresql,  and thought

I'd try that to see what it would do, but (the two mistakes) I didn't

start a transaction and where I had intended to run the command on one

row, I had a brain freeze and ran the update across the table.

The command was:

update gamespubquiz set question = convert_to(question,'LATIN9');

Ooops.

Two questions:

1) How can I convert the hext back to 'text'?

regression=# update foo set f1 = convert_from(f1::bytea,'LATIN9');

UPDATE 1

regression=# table foo;

       f1       

----------------

 this is a test

(1 row)

As for (2), maybe replace() would help you.

			regards, tom lane

On Sat, 2020-04-25 at 10:41 +0100, Steve Tucknott (TuSol) wrote:
> I have a table with a varchar(5000) that contains general text. The table is typically
> maintained via a GUI, but on this occasion I received a spreadsheet with data and
> loaded it - via copy - from a csv extracted from that. The data looked fine in psql,
> but when looking at the data in the GUI, characters such as single quote marks (')
> appeared as a series of special characters. I assumed that the spreadsheet then had
> some different encoding (UTF8?) and that I then needed to 'translate' the characters.

Very likely, the characters were not really single quotes, but "curly quotes"
(UNICODE 201C and 201E) characters.

One of the following scenarios must have taken place:

1. The file was encoded in UTF-8, but when you copied the data in, the encoding
   you specified (or had by default) was a single-gyte encoding like LATIN9.

   The curly quotes are more than one byte in UTF-8, but each byte was interpreted as
   a LATIN9 character.

   The solution would be to specify ENCODING 'UTF8' with COPY.

2. The characters are actually fine in the database, and you loaded them correctly,
   and your database client encoding is UTF8, but your terminal is in LATIN9.

   The characters were displayed correctly, but your terminal interpreted each
   byte as a character.

To determine which was the case, look what bytes are in the database:

SELECT badcol, badcol::bytea FROM tab WHERE id = 12345;

Yours,
Laurenz Albe

-- 
Cybertec | https://www.cybertec-postgresql.com

On Sat, 2020-04-25 at 10:41 +0100, Steve Tucknott (TuSol) wrote:

I have a table with a varchar(5000) that contains general text. The table is typically

maintained via a GUI, but on this occasion I received a spreadsheet with data and

loaded it - via copy - from a csv extracted from that. The data looked fine in psql,

but when looking at the data in the GUI, characters such as single quote marks (')

appeared as a series of special characters. I assumed that the spreadsheet then had

some different encoding (UTF8?) and that I then needed to 'translate' the characters.

Very likely, the characters were not really single quotes, but "curly quotes"

(UNICODE 201C and 201E) characters.

One of the following scenarios must have taken place:

1. The file was encoded in UTF-8, but when you copied the data in, the encoding

   you specified (or had by default) was a single-gyte encoding like LATIN9.

   The curly quotes are more than one byte in UTF-8, but each byte was interpreted as

   a LATIN9 character.

   The solution would be to specify ENCODING 'UTF8' with COPY.

2. The characters are actually fine in the database, and you loaded them correctly,

   and your database client encoding is UTF8, but your terminal is in LATIN9.

   The characters were displayed correctly, but your terminal interpreted each

   byte as a character.

To determine which was the case, look what bytes are in the database:

SELECT badcol, badcol::bytea FROM tab WHERE id = 12345;

Yours,

Laurenz Albe