Re: BUG #17258: Unexpected results in CHAR(1) data type

> On Oct 29, 2021, at 2:52 PM, David M. Calascibetta <david(at)calascibetta(dot)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 that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to be greater 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 pattern matching, 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 insignificant and disregarded when comparing two values of type character." and "Trailing spaces are removed when converting a 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 involving char(n) will involve first casting to text before handing the resultant text off to a function, with the cast triggering the "spaces are removed when converting" bit, and those functions that do directly take a bpchar type for comparison purposes trigger the "semantically insignificant and disregarded" bit. For practical purposes you will frequently hit the one bit or the other.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company