Re: 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