pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PGUser2020 <pg@diorite.uk>
To: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: How can I return a UTF8 string from a hex representation in a latin9 database?
Date: Mon, 20 Nov 2023 07:24:45 +0000 (GMT)
Message-ID: <E1r4yeD-0004Mk-9R@rmmprod06.runbox> (raw)
In-Reply-To: <q3w55c2ihmd62x2kpdw6sreazrolt2hsi5t4yjd6myoqw5y6sj@iimlufjzgkhw>


On 2023-11-20 02:59 +00:00 GMT, "Erik Wienhold" <ewie@ewie.name> wrote:
> On 2023-11-19 21:37 +0100, PGUser2020 wrote:

> 
> Technically speaking, UTF-8 is an 8-bit encoding.  But I guess that
> application would then show mojibake if UTF-8 were stored.
> 

Yes sorry, I should have said single byte rather than 8 bit. There must be no possibility that a single character occupies more than one byte as the (e.g.) varchar(10) and char(5) fields overflow lengths otherwise.

> 
> Do you have to use existing Latin-9 text columns to store UTF-8?  If not
> then I'd go with bytea instead of text (varchar) if possible and also
> supported by your client.  Otherwise it may be difficult to distinguish
> between "normal" Latin-9 text and the hex- or base64-encoded UTF-8.
> Although bytea could also store anything, not just UTF-8, so you'd have
> to deal with invalid data anyway.
> 

I do have to use existing columns yes, and they are varchar latin9 columns.

> Is the same client sending and reading that data?  If yes, why can't the
> client do the hex-encoding of the UTF-8 string and only send/read those
> encoded strings so that database won't event see UTF-8?  Why must the
> database be involved in this custom encoding scheme instead of just
> storing BLOBs (either as bytea or some encoded text)?
> 

So one of the external clients applications which is interacting with this database will do just that -- it will make a hex string from its utf8 input and store that in a varchar

> 
> The client can disable encoding conversion by setting client_encoding to
> sql_ascii:
> 
> 	latin9_test=# show server_encoding;
> 	 server_encoding 
> 	-----------------
> 	 LATIN9
> 	(1 row)
> 	
> 	latin9_test=# set client_encoding to sql_ascii;
> 	SET
> 	latin9_test=# show client_encoding;
> 	 client_encoding 
> 	-----------------
> 	 SQL_ASCII
> 	(1 row)
> 	
> 	latin9_test=# select convert_from(decode('ceb120ceb220ceb320ceb420ceb520cf83cf84', 'hex'), 'sql_ascii');
> 	    convert_from     
> 	---------------------
> 	 α β γ δ ε στ
> 	(1 row)
> 
> Maybe that's also an option for your client.
> 

It is very useful and exactly what I was looking for thanks. 

This technique should allow me to create a login, mask a table with a view containing this decode, and use search_path to get the view returned in preference to the base table.




view thread (3+ messages)

Message-ID: <E1r4yeD-0004Mk-9R@rmmprod06.runbox>
Permalink:  ../E1r4yeD-0004Mk-9R@rmmprod06.runbox/
Also on:    postgresql.org/message-id/E1r4yeD-0004Mk-9R@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, ewie@ewie.name
  Subject: Re: How can I return a UTF8 string from a hex representation in a latin9 database?
  In-Reply-To: <E1r4yeD-0004Mk-9R@rmmprod06.runbox>
  References: <q3w55c2ihmd62x2kpdw6sreazrolt2hsi5t4yjd6myoqw5y6sj@iimlufjzgkhw> <E1r4yeD-0004Mk-9R@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