Re: LATIN9 - hex in varchar after convert

On Sat, 2020-04-25 at 10:41 +0100, Steve Tucknott (TuSol) wrote:
> 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
>

...for example ....as taken from psql:

Original text: ‘With the lights out, it’s less dangerous. Here we are
now, entertain us.’

After
convert: \xe280985769746820746865206c6967687473206f75742c206974e280997
3206c6573732064616e6765726f75732e204865726520776520617265206e6f772c2065
6e7465727461696e2075732ee28099

PS apologies for any inconvenience for posting with 'read receipt'
request turned on earlier.