Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4yeM-00BD7g-VZ for pgsql-general@arkaria.postgresql.org; Mon, 20 Nov 2023 07:24:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r4yeL-006GAw-Jy for pgsql-general@arkaria.postgresql.org; Mon, 20 Nov 2023 07:24:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4yeL-006GAm-6h for pgsql-general@lists.postgresql.org; Mon, 20 Nov 2023 07:24:53 +0000 Received: from mailtransmit04.runbox.com ([2a0c:5a00:149::25]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4yeI-006R6v-34 for pgsql-general@postgresql.org; Mon, 20 Nov 2023 07:24:51 +0000 Received: from mailtransmit03.runbox ([10.9.9.163] helo=aibo.runbox.com) by mailtransmit04.runbox.com with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.93) (envelope-from ) id 1r4yeD-00BZNq-M3; Mon, 20 Nov 2023 08:24:45 +0100 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=diorite.uk; s=selector1; h=Message-Id:In-Reply-To:Date:Subject:CC:To:From:MIME-Version: Content-Transfer-Encoding:Content-Type; bh=n8vPU2FgYGsbLnUqCbHy4E/UXsv1R1C392aTtqjdrRE=; b=UeDoU7vN649pHqMkhuhYsw7lFS aapdLwUT/OpUdbCOMKdqTTPM6BHy02bObYE9MBnprt7aSh5PnhTbm5AIWXXIZ5ZVyztWf7DYzrm1r WS7Wbz8YCcRDtAlnJE6/srL8qcbrzfuX1ilXjnpJye0K0mWrFjqUaLGZPQRi3dLHSl17VmzFUkpwx NKGauTSBazSe5FraM5Fataq8dVuvlLcXj9Djvk9sNGmhPqlO0jKBAFaFTH4pFbaOGzSXYT3cV5hHY YWCQExnGnxQA0RCkRuCsfNaSwOOFkfLzs4TX8J2FldrHu2eKCUAisqaGzH6nSW8tJTDHC0lQRQ099 NPniBz6w==; Received: from [10.9.9.128] (helo=rmmprod06.runbox) by mailtransmit03.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1r4yeD-0000Dw-AR; Mon, 20 Nov 2023 08:24:45 +0100 Received: from mail by rmmprod06.runbox with local (Exim 4.86_2) (envelope-from ) id 1r4yeD-0004Mk-9R; Mon, 20 Nov 2023 08:24:45 +0100 Content-Type: text/plain; charset="utf-8" Content-Disposition: inline Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 Received: from [Authenticated alias (1182204)] by runbox.com with http (RMM6); Mon, 20 Nov 2023 07:24:45 GMT From: "PGUser2020" To: "Erik Wienhold" CC: "pgsql-general" 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) X-RMM-Aliasid: 1182204 X-Mailer: RMM6 In-Reply-To: Message-Id: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2023-11-20 02:59 +00:00 GMT, "Erik Wienhold" wrote: > On 2023-11-19 21:37 +0100, PGUser2020 wrote: >=20 > Technically speaking, UTF-8 is an 8-bit encoding. But I guess that > application would then show mojibake if UTF-8 were stored. >=20 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. >=20 > 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. >=20 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)? >=20 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 >=20 > The client can disable encoding conversion by setting client_encoding to > sql_ascii: >=20 > latin9_test=3D# show server_encoding; > server_encoding=20 > ----------------- > LATIN9 > (1 row) >=20=09 > latin9_test=3D# set client_encoding to sql_ascii; > SET > latin9_test=3D# show client_encoding; > client_encoding=20 > ----------------- > SQL_ASCII > (1 row) >=20=09 > latin9_test=3D# select convert_from(decode('ceb120ceb220ceb320ceb420ceb5= 20cf83cf84', 'hex'), 'sql_ascii'); > convert_from=20=20=20=20=20 > --------------------- > =CE=B1 =CE=B2 =CE=B3 =CE=B4 =CE=B5 =CF=83=CF=84 > (1 row) >=20 > Maybe that's also an option for your client. >=20 It is very useful and exactly what I was looking for thanks.=20 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 pre= ference to the base table.=