>>> select 'A'::char(1)='A '::text;
>>> select 'A'::char(1)='A '::varchar(10);
>>>
>>> I would expect both queries to return "true", and this is also suggested by
>>> the documentation (section 8.3.), but the text comparison returns false.
>
> I see nothing in 8.3 addressing the question of which type has precedence
> for cross-type comparisons.
you are right, I did not read 8.3 careful enough. It says that "trailing
spaces are treated as semantically insignificant and disregarded when
comparing two values of type character", but nothing about mixed types.
The SQL standard 8,2 <comparison predicate>, Rule 3, b) says that
""
If the length in characters of X is not equal to the length in
characters of Y, then the shorter string is effectively replaced, for
the purposes of comparison, with a copy of itself that has been extended
to the length of the longer string by concatenation on the right of one
or more pad characters, where the pad character is chosen based on CS.
If CS has the NO PAD characteristic, then the pad character is an
implementation-dependent character different from any character in the
character set of X and Y that collates less than any string under CS.
Otherwise, the pad character is a <space>.
""
which would seem to suggest that spaces have to be ignore when comparing
CHAR with anything else (as the other string type is free to choose any
pad character, but CHAR will always pad with spaces as needed).
But of course you can deviate from that if you wish. I just found it
surprising that text behaved different from varchar, which can lead to
very subtle bugs.
Thomas
