CODEWITHSUNDEEP
databasepostgresqlcharacter-encoding
Xin Zhang
Xin Zhang

Reputation: 51

How to insert a character with its encoded value in a PostgreSQL non-unicode database?

I create a database and a table in PostgreSQL with the statements below:

CREATE DATABASE pg_euc_cn WITH ENCODING = 'EUC_CN' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE=template0;

CREATE TABLE t1 (VALUE VARCHAR(3));

I want to insert the character '单' into this table, with its euc-cn encoded value 'B5A5' or its unicode code point '5355' instead of the character itself.

I tried with

insert into t1 values (x'B5A5')

and

insert into t1 values (0xB5BA)

but they do not work for PostgreSQL. What it the correct statement to do that?

Upvotes: 0

Views: 1026

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

The documentation covers that:

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. [...]

\uxxxx, \Uxxxxxxxx (x = 0–9, A–F) — 16 or 32-bit hexadecimal Unicode character value

So E'\u5355' for '单'.

There is also another way to write Unicode-escaped strings.

Upvotes: 1

Related Questions