Re: LATIN9 - hex in varchar after convert

On Sat, 2020-04-25 at 10:35 -0400, Tom Lane wrote:
> "Steve Tucknott (TuSol)" <steve(at)tusol(dot)co(dot)uk> writes:
> > I saw there was a convert_to/from function in postgresql, and
> > thoughtI'd try that to see what it would do, but (the two mistakes)
> > I didn'tstart a transaction and where I had intended to run the
> > command on onerow, 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 1regression=# table
> foo; f1 ---------------- this is a test(1 row)
> As for (2), maybe replace() would help you.
> regards, tom lane

Tom
Thanks. I tried the convert from and got the unknown function error and
then tried again and applied the cast, but I applied it after the
bracket ie
convert_from(question,'LATIN9')::bytea. I scare myself at times - but
my excuse is that I haven't used a database in earnest for a fair few
years. Poor excuse I know.

I'll clone the table with the problem before playing with it again to
try to fix the quote encoding issue - and I'll make sure I'm in a
transaction anyway so I can roll back the attempts.

Thanks again.