PostgreSQL Character set problem causes garbled characters

When you use the PostgreSQL database, when you enter Chinese, you encounter an error "Error:invalid byte sequence for encoding" UTF8 ": 0xd6d0" because the client character set is not configured correctly.

The cause of the problem:

By default, PostgreSQL is not a character set, if your database is a UTF8 character set, the general terminal's Chinese character set will be GBK, or en_us (view the terminal's character set can see the settings of the lang environment variable), so you enter the Chinese is GBK encoding, This code is not converted into the database, and the database is UTF8, PostgreSQL see no such UTF8 code, so of course the error.

The workarounds are:

Method One: Set PostgreSQL client code to GBK, then PostgreSQL know that the input is GBK encoded, so PostgreSQL database will automatically do the character set conversion, convert it to UTF8 encoding.

Method Two: Directly set the terminal's character set encoding to UTF8, so that the input encoding is directly UTF8, not GBK.

See my specific Demo:

Method One: Set the client code for PostgreSQL:

There are two ways to set the Psql client character set to GBK, one is to enter "\encoding GBK" in Psql, and the other is to set the environment variable "export PGCLIENTENCODING=GBK" to demonstrate:

[Email protected] ~]$ psql-d testdb03

Psql.bin (9.5.9)

Type ' help ' for help.

testdb03=# \l

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

testdb03=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' School ', ' 43 ', ' 10 ', ' 2.0 ', ' 1994-12-11 ');

INSERT 0 1

testdb03=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' university ', ' 43 ', ' 10 ', ' 2.0 ', ' 1994-12-11 ');

INSERT 0 1

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

School |      43 |    10 | 2 | 1994-12-11

University |      43 |    10 | 2 | 1994-12-11

Modify the Pgsql client character set to GBK and view the table contents again:

testdb03=# \encoding GBK

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

у|      43 |    10 | 2 | 1994-12-11

′|      43 |    10 | 2 | 1994-12-11

(7 rows)

garbled because the Psql database is specified in the initialization of the character set is UTF8.

Insert Chinese error again:

testdb03=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' primary ', ' 43 ', ' 10 ', ' 2.0 ', ' 1994-12-11 ');

Error:character with byte sequence 0xad 0xa6 in encoding "GBK" have no equivalent in encoding "UTF8"

Switch Psql the character set of the client to the UTF8 character set again inserts no longer an error:

testdb03=# \encoding UTF8

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

School |      43 |    10 | 2 | 1994-12-11

University |      43 |    10 | 2 | 1994-12-11

(7 rows)

testdb03=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' primary ', ' 43 ', ' 10 ', ' 2.0 ', ' 1994-12-11 ');

INSERT 0 1

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

School |      43 |    10 | 2 | 1994-12-11

University |      43 |    10 | 2 | 1994-12-11

Primary School |      43 |    10 | 2 | 1994-12-11

(8 rows)

testdb03=#

[Email protected] ~]$ export PGCLIENTENCODING=GBK

[Email protected] ~]$ psql-d testdb03

Psql.bin (9.5.9)

Type ' help ' for help.

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

у|      43 |    10 | 2 | 1994-12-11

′|      43 |    10 | 2 | 1994-12-11

с|      43 |    10 | 2 | 1994-12-11

(8 rows)

testdb03=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' primary ', ' 43 ', ' 10 ', ' 2.0 ', ' 1994-12-11 ');

Error:character with byte sequence 0xad 0xa6 in encoding "GBK" have no equivalent in encoding "UTF8"

Modifying the back source character set UTF8

[Email protected] ~]$ export Pgclientencoding=utf8

[Email protected] ~]$

[Email protected] ~]$ psql-d testdb03

Psql.bin (9.5.9)

Type ' help ' for help.

testdb03=# \d

List of relations

Schema | Name |  Type | Owner

--------+--------------+-------+----------

Public | Postgres_log | Table | Postgres

Public | Weather | Table | Postgres

(2 rows)

testdb03=# select * from weather;

City | Temp_lo | Temp_hi |    PRCP | Date

----------+---------+---------+------+------------

China07 |      49 |    61 | 3 | 1994-12-17

School |      43 |    10 | 2 | 1994-12-11

University |      43 |    10 | 2 | 1994-12-11

Primary School |      43 |    10 | 2 | 1994-12-11

(8 rows)

Method Two: Set the terminal encoding to UTF8:

[Email protected] ~]$ export LANG=ZH_CN. UTF8

Then modify the terminal software character set encoding, I am using SECURECRT, modify the method is:

Option->session option-> appearance and character encoding, change the contents of that drop-down box to "UTF8":

Then insert the data test:

[Email protected] ~]$ psql-d testdb03

Psql (8.4.3)

Type ' help ' for help.

testdb03=# select * from T;

ID | Name

----+----------

1 | China

2 | My Chinese

(2 rows)

testdb03=# INSERT into t values (3, ' my China ');

INSERT 0 1

testdb03=# select * from T;

ID | Name

----+----------

1 | China

2 | My Chinese

3 | My Chinese

(3 rows)

This article from "10931853" blog, declined reprint!

PostgreSQL Character set problem causes garbled characters