Re: LATIN9 - hex in varchar after convert

"Steve Tucknott (TuSol)" <steve(at)tusol(dot)co(dot)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