jorsol@gmail.com writes:
> TEXT type is the preferred data type for the category String, but I'm having
> a hard time using it with bpchar:
I believe what you're showing here can be reduced to these cases:
regression=# select 'c'::char(3) = 'c'::text;?column?
----------t
(1 row)
regression=# select 'c'::char(3) = 'c '::text;?column?
----------f
(1 row)
That is, the = operator is resolved as text = text, for which
trailing spaces in the strings are significant. But when we
promote the bpchar value to text, we strip its trailing spaces,
which are deemed not significant. So we have 'c' = 'c' and
'c' != 'c '.
regression=# select 'c'::char(3) = 'c'::varchar;?column?
----------t
(1 row)
regression=# select 'c'::char(3) = 'c '::varchar;?column?
----------t
(1 row)
Here, the = operator is resolved as bpchar = bpchar, in which
trailing spaces aren't significant period.
I forget at the moment exactly why these choices of how to resolve
the ambiguous comparison operator get made, but most likely it has
to do with text being a preferred type while varchar hasn't even
got any operators of its own. Reading the type conversions chapter
of the manual would help you identify why that happens.
These behaviors are of long standing and we're very unlikely to
change them. If you don't like them, don't use bpchar; it's a
legacy datatype of little real value anyway.
regards, tom lane