inserts bypass encoding conversion

From: "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com>
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: inserts bypass encoding conversion
Date: 2023-08-16 07:06:51
Message-ID: PH0PR11MB5191EB32087DE307374EB2C4D615A@PH0PR11MB5191.namprd11.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,
With client_encoding=UTF8 and server_encoding=LATIN1, looks like insert into value with chr(codepoint) bypass encoding conversion , is it expected ? test as below ,

jamet=# delete from testutf8;
DELETE 1
jamet=# show client_encoding;
client_encoding
-----------------
UTF8
(1 row)

jamet=# show server_encoding;
server_encoding
-----------------
LATIN1
(1 row)

jamet=# \d testutf8
Table "public.testutf8"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
test | character varying(128) | | |

jamet=# insert into testutf8 values('...');
ERROR: character with byte sequence 0xe2 0x80 0xa6 in encoding "UTF8" has no equivalent in encoding "LATIN1" <<< here it's expected to see encoding conversion error
jamet=# insert into testutf8 values(chr(226)||chr(128)||chr(166)); <<< here, looks like using chr(codepoint) works, it bypass encoding_conversion ?
INSERT 0 1
jamet=# set client_encoding='LATIN1';
SET
jamet=# show client_encoding;
client_encoding
-----------------
LATIN1
(1 row)

jamet=# show server_encoding;
server_encoding
-----------------
LATIN1
(1 row)

jamet=# select * from testutf8;
test
------
...
(1 row)

jamet=# insert into testutf8 values('...'); <<< here, with client and server same LATIN1, no any encoding conversion , and the data got inserted.
INSERT 0 1
jamet=# select * from testutf8;
test
------
...
...
(2 rows)

jamet=# select encode(test::bytea,'hex') from testutf8; <<< both show same value
encode
--------
e280a6
e280a6
(2 rows)

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2023-08-16 14:02:55 Re: inserts bypass encoding conversion
Previous Message Stephen Frost 2023-08-13 23:22:25 Re: .history': No such file or directory - a symptom of ?