"value too long for type character varying" Is Displayed When VARCHAR(n) Stores Chinese Characters

Symptom

The VARCHAR(18) field cannot store eight Chinese characters. The following error is reported:

1

org.postgresql.util.PSQLException: ERROR: value too long for type character varying(18)

Possible Causes

Take UTF-8 encoding as an example. A Chinese character is 3 to 4 bytes long. Eight Chinese characters are 24 to 32 bytes long, which exceeds the maximum length (18 bytes) of VARCHAR(18).

Common scenario: Customers import data from the source MySQL database to DWS. n in varchar(n) indicates the number of characters in MySQL and the number of bytes in DWS. If there are Chinese characters, an error is reported that the field length is insufficient when the length is the same.

If a column contains Chinese characters, you can use the char_length or length function to query the character length and use the lengthb function to query the byte length.

Handling Procedure

varchar(n) is used to store variable length value as a string, here n denotes the string length in bytes. A Chinese character is usually 3 to 4 bytes long.

Increase the value length of this field based on the actual Chinese character length. For example, to store eight Chinese characters in a field, n must be set to at least 32, that is, varchar(32).