Thread: why do i get 2 as answer for select length('aa '::char(6));
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
On 17 January 2018 at 14:59, 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?
What made you think that?
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html
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:14 AM, Andrej <andrej.groups@gmail.com> wrote:
On 17 January 2018 at 14:59, 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?
What made you think that?
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html
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.
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 10The 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 typecharacter.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.
john snow <ofbizfanster@gmail.com> writes:
> 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?
It *is* padded, as you can verify with other functions such as
octet_length or pg_column_size. But length() disregards trailing
spaces in char-type values, on the theory that they're semantically
insignificant.
regards, tom lane
when you say char-type values, do you include varchar? if so, the other results (see later post from first post) i get are inconsistent with the assertion that length() disregards trailing spaces in char-type values, and i don't understand why it's inconsistent. i hope i'm not becoming annoying :-)
On Wed, Jan 17, 2018 at 11:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
john snow <ofbizfanster@gmail.com> writes:
> 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?
It *is* padded, as you can verify with other functions such as
octet_length or pg_column_size. But length() disregards trailing
spaces in char-type values, on the theory that they're semantically
insignificant.
regards, tom lane
john snow <ofbizfanster@gmail.com> writes:
> when you say char-type values, do you include varchar?
No. varchar and text consider trailing spaces to be significant.
To my mind, there are very few situations where char(n) is actually
a reasonable choice of datatype. Maybe for US state abbreviations
or the like. If you're even asking this question, it suggests that
you ought to be using varchar/text.
char(n) basically exists to support fixed-field-width data designs that
should have died along with the punched cards that inspired them.
regards, tom lane
thanks! you are right. we're porting a 20 year old xbase app and it's painful
On Wed, Jan 17, 2018 at 11:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
john snow <ofbizfanster@gmail.com> writes:
> when you say char-type values, do you include varchar?
No. varchar and text consider trailing spaces to be significant.
To my mind, there are very few situations where char(n) is actually
a reasonable choice of datatype. Maybe for US state abbreviations
or the like. If you're even asking this question, it suggests that
you ought to be using varchar/text.
char(n) basically exists to support fixed-field-width data designs that
should have died along with the punched cards that inspired them.
regards, tom lane
Does anybody know how I get of this list?
Rabindra Nathan from mobile device.
thanks! you are right. we're porting a 20 year old xbase app and it's painfulOn Wed, Jan 17, 2018 at 11:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:john snow <ofbizfanster@gmail.com> writes:
> when you say char-type values, do you include varchar?
No. varchar and text consider trailing spaces to be significant.
To my mind, there are very few situations where char(n) is actually
a reasonable choice of datatype. Maybe for US state abbreviations
or the like. If you're even asking this question, it suggests that
you ought to be using varchar/text.
char(n) basically exists to support fixed-field-width data designs that
should have died along with the punched cards that inspired them.
regards, tom lane