Amit Langote <amitlangote09@gmail.com> writes:
> On Thu, Jan 8, 2015 at 6:39 AM, <tneumann@users.sourceforge.net> wrote:
>> The following two queries return different results:
>>
>> 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.
> Quick inspection of pg_operator suggests there is no '=' operator that
> takes varchar as either of the operands. Not sure why that is so. It
> appears the comparison proceeds using =(char, char) operator with the
> varchar operand appropriately coerced. Hence the result.
The available operators are char=char and text=text (varchar has no
operators of its own, it just uses text's operators). For an input
of the form char=text, the text=text operator wins on the grounds of
text being a preferred type; see
http://www.postgresql.org/docs/9.4/static/typeconv-oper.html
rule 3d. So we coerce char to text and compare using text's rules,
in which trailing spaces are significant. But if the input is
char=varchar, only the char=char operator has any exact matches, so it
wins at rule 3c. Then we'll coerce varchar to char and compare using
char's rules, in which trailing spaces are not significant.
So it's behaving as expected and documented. Whether this behavior is
desirable is a different question of course. I read the SQL spec as
requiring us to use PAD SPACE semantics for char=varchar comparisons,
so that case is forced. char=text is outside the bounds of the spec
(since text isn't in the spec) so we don't have to do it the same way;
and we choose not to because text's semantics are generally more natural.
regards, tom lane
