Postgres Pro
Downloads
Home   >   mailing lists

Re: why do i get 2 as answer for select length('aa '::char(6)); - Mailing list pgsql-novice

From john snow
Subject Re: why do i get 2 as answer for select length('aa '::char(6));
Date
Msg-id CAE67tvW9_tjMPJE-p3cJw_bw6JtnV8NAAY9JXwYy8x2Lhc++nQ@mail.gmail.com
Whole thread Raw
In response to Re: why do i get 2 as answer for select length('aa '::char(6));  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-novice
Tree view
thanks!

you might have just missed my other post, but could i trouble you for additional info (if you have any) re:

select length('aa      '::varchar(6));  //answers 6

select char_length('aa      '::varchar(6)); //answers 6

select char_length('aa      '::char(6));  //answers 2 even though the input string has 6 characters as was the case with the varchar input string

select length('aa      '::char(6));  //answers 2 even though the input string has 6 characters as was the case with the varchar input string

are the results as expected? the last two strike me as unexpected


On Wed, Jan 17, 2018 at 10:52 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, January 16, 2018, john snow <ofbizfanster@gmail.com> wrote:
as well as select length('aa'::char(6));   

i thought if the string to be stored is shorter than specified length , it will be padded with spaces?

i'm using version 10.0 on windows 10

The docs could use more detail here but in short the sentence:

 However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character.

In turn results in the length test only counting semantically significant spaces and thus returning two regardless of the number of input spaces originally present.  postgreSQL pads the spaces but then basically pretends they don't exist except for printing.

I'm not sure why it even bothers to store the spaces given that...but I suppose it's more efficient than looking up the typmod all of the time.

David J.

pgsql-novice by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: why do i get 2 as answer for select length('aa '::char(6));
Next
From: Tom Lane
Date:
Subject: Re: why do i get 2 as answer for select length('aa '::char(6));