pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PGUser2020 <pg@diorite.uk>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: How can I return a UTF8 string from a hex representation in a latin9 database?
Date: Sun, 19 Nov 2023 20:37:45 +0000 (GMT)
Message-ID: <E1r4oY5-00060N-4c@rmmprod06.runbox> (raw)

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.




view thread (3+ messages)  latest in thread

Message-ID: <E1r4oY5-00060N-4c@rmmprod06.runbox>
Permalink:  ../E1r4oY5-00060N-4c@rmmprod06.runbox/
Also on:    postgresql.org/message-id/E1r4oY5-00060N-4c@rmmprod06.runbox

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: pg@diorite.uk
  Subject: Re: How can I return a UTF8 string from a hex representation in a latin9 database?
  In-Reply-To: <E1r4oY5-00060N-4c@rmmprod06.runbox>
  References: <E1r4oY5-00060N-4c@rmmprod06.runbox>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox