> On Oct 29, 2021, at 2:52 PM, David M. Calascibetta <david@calascibetta.com> wrote:
>
> In every other rdbms, if it's fixed length character, values will always be that fixed length.
> PG behaves that way unless the value is blank. I believe this is in violation of the spec,
> which is why I reported it. Even the PG doc says it will pad with blanks. It does not give
> an exception for blank values.
Taken from the postgres docs:
> Values of type character are physically padded with spaces to the specified width n, and are stored and displayed
thatway. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values
oftype character. In collations where whitespace is significant, this behavior can produce unexpected results; for
exampleSELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to
begreater than a newline. Trailing spaces are removed when converting a character value to one of the other string
types.Note that trailing spaces are semantically significant in character varying and text values, and when using
patternmatching, that is LIKE and regular expressions.
The two parts of that which at least hint at the behavior are "However, trailing spaces are treated as semantically
insignificantand disregarded when comparing two values of type character." and "Trailing spaces are removed when
convertinga character value to one of the other string types."
There aren't that many functions which directly accept a bpchar type (aka, a char(n)), so most of the functionality
involvingchar(n) will involve first casting to text before handing the resultant text off to a function, with the cast
triggeringthe "spaces are removed when converting" bit, and those functions that do directly take a bpchar type for
comparisonpurposes trigger the "semantically insignificant and disregarded" bit. For practical purposes you will
frequentlyhit the one bit or the other.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company