On Sat, 2020-04-25 at 10:35 -0400, Tom Lane wrote:
"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
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.