From: | "PGUser2020" <pg(at)diorite(dot)uk> |
---|---|
To: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | How can I return a UTF8 string from a hex representation in a latin9 database? |
Date: | 2023-11-19 20:37:45 |
Message-ID: | E1r4oY5-00060N-4c@rmmprod06.runbox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello. Hopefully I can explain myself
I have a database for a legacy application that requires an 8 bit database (i.e. the application itself won't function on a UTF8 database).
Looking at ways to extend the functionality to be able to handle a few specified fields in Unicode.
Had the idea to store a UTF8 string as either hex pairs or Base64 inside a VARCHAR field, which is fine. I can do that.
What needs to happen though, is to build a view, that will return the decoded hex (or b64) as a UTF8 string to a client which has specified client encoding UTF8.
I've tried various combinations of convert_from, and convert_to, and convert, but I just can't seem to get it to return the string a UTF8 select to the client.
So if I have this data:
select * from mytable;
mycolumn
------------------------------------------
ceb120ceb220ceb320ceb420ceb520cf83cf84
Then:
select convert_from(decode(mycolumn, 'hex')::bytea, 'utf-8') from mytable where usr='BATCH';
ERROR: character with byte sequence 0xce 0xb1 in encoding "UTF8" has no equivalent in encoding "LATIN9"
So the database encoding is still relevant , this is expected by the description of convert_from in the documentation of course.
Is there some combination of functions I can use to have a client select this column from this table in a LATIN9 database and get a UTF8 string back? Any thoughts appreciated, thank you.
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2023-11-20 02:59:35 | Re: How can I return a UTF8 string from a hex representation in a latin9 database? |
Previous Message | David G. Johnston | 2023-11-19 19:40:12 | Re: Prepared statements versus stored procedures |