why do i get 2 as answer for select length('aa '::char(6));

[john snow]

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

[Andrej]

Post by john snow
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?

[john snow]

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

Post by Andrej

Post by john snow
as well as select length('aa'::char(6));
i thought if the string to be stored is shorter than specified length ,

it

Post by john snow
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

[David G. Johnston]

Post by john snow
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.

[john snow]

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 <

Post by David G. Johnston

Post by john snow
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

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.

[Tom Lane]

Post by john snow
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]

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
:-)

Post by Tom Lane

Post by john snow
as well as select length('aa'::char(6));
i thought if the string to be stored is shorter than specified length ,

it

Post by john snow
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

[Tom Lane]

Post by john snow
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

[john snow]

thanks! you are right. we're porting a 20 year old xbase app and it's
painful

Post by Tom Lane

Post by john snow
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

[RABIN NATHAN]

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 painful

Post by Tom Lane

Post by john snow
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