Thread: BUG #15637: Problem insert data 【Japanese】

The following bug has been logged on the website:

Bug reference:      15637
Logged by:          Thanh Huynh Van
Email address:      thanhhv26@gmail.com
PostgreSQL version: 11.1
Operating system:   Window Server 2012 Standard
Description:

I created table has a column　【"C_VAL" character varying(10)】　after that I
insert data【'不具合管理報告書': length=24】. It was inserted successfully => this is
problem.
* Details:
-- Step 1: Create table. Data length of "C_VAL" column = 10
create table "USER02"."T_331_VARCHAR" (
  "ID" numeric not null
  , "C_VAL" character varying(10)
  , primary key ("ID")
);
-- Step 2: Check data length. OCTET_LENGTH('不具合管理報告書') = 24
SELECT OCTET_LENGTH('不具合管理報告書');
-- Step 3: Insert data. Length '不具合管理報告書' = 24
INSERT INTO "USER02"."T_331_VARCHAR"("ID", "C_VAL")
VALUES(1,'不具合管理報告書');
-- Step 4: Check length of inserted data
SELECT OCTET_LENGTH("C_VAL"), * FROM "USER02"."T_331_VARCHAR";
=> Why 【Step 3】was successful ?. Can you help me ?
Thank you !

Am 15.02.2019 um 03:43 schrieb PG Bug reporting form:
> I created table has a column　【"C_VAL" character varying(10)】　after that I
> insert data【'不具合管理報告書': length=24】. It was inserted successfully => this is
> problem.
> * Details:
> -- Step 1: Create table. Data length of "C_VAL" column = 10
> create table "USER02"."T_331_VARCHAR" (
>    "ID" numeric not null
>    , "C_VAL" character varying(10)
>    , primary key ("ID")
> );
> -- Step 2: Check data length. OCTET_LENGTH('不具合管理報告書') = 24
> SELECT OCTET_LENGTH('不具合管理報告書');
> -- Step 3: Insert data. Length '不具合管理報告書' = 24
> INSERT INTO "USER02"."T_331_VARCHAR"("ID", "C_VAL")
> VALUES(1,'不具合管理報告書');
> -- Step 4: Check length of inserted data
> SELECT OCTET_LENGTH("C_VAL"), * FROM "USER02"."T_331_VARCHAR";
> => Why 【Step 3】was successful ?. Can you help me ?
> Thank you !

I think it works because a multibyte character is still considered a single character, even if it is 
represented internally with more than one byte. This means your octet_length() can be bigger than 
the character length of your string returned by length(). The string in your example is still only 8 
characters and should fit into your colum.

Marc

On Fri, Feb 15, 2019 at 12:50 PM PG Bug reporting form
<noreply@postgresql.org> wrote:

It's been answered before, but..
> I created table has a column　【"C_VAL" character varying(10)】

The first sentence of the first paragraph of the "8.3 character types"
of the manual reads:

"SQL defines two primary character types: character varying(n) and
character(n), where n is a positive integer. Both of these types can
store strings up to n characters (not bytes) in length. "

> -- Step 2: Check data length. OCTET_LENGTH('不具合管理報告書') = 24
> SELECT OCTET_LENGTH('不具合管理報告書');
> -- Step 3: Insert data. Length '不具合管理報告書' = 24
> INSERT INTO "USER02"."T_331_VARCHAR"("ID", "C_VAL")
> VALUES(1,'不具合管理報告書');
> -- Step 4: Check length of inserted data
> SELECT OCTET_LENGTH("C_VAL"), * FROM "USER02"."T_331_VARCHAR";
> => Why 【Step 3】was successful ?. Can you help me ?

Try asking for the SQL standard
"char_length(string) or character_length(string) int Number of
characters in string char_length('jose')"
or the additional ( but the one which I mostly use )
"length(string) int Number of characters in string"
instead of the SQL standard
"octet_length(string) int Number of bytes in string"

( quoted from the first two tables of 9.4-String functions and operators)
( I suspect length is listed in the standard specifically to avoid
doubts about whether it returns bytes, chars or codepoints or whatever
)

Francisco Olarte.