Reading the type conversions chapter of the manual would help you identify why that happens.
Sadly the manual is not clear enough, one can read something like this:
All type conversion rules are designed with several principles in mind: * Implicit conversions should never have surprising or unpredictable outcomes
I think this falls down in surprising and unpredictable outcome.
Also in:
Trailing spaces are removed when converting a character value to one of the other string types.
I have searched the implicit conversions table and found that a cast from char to text and varchar both have rtrim1 as prosrc, so is still unclear to me this behavior.
select p.prosrc, * from pg_cast c join pg_proc p on c.castfunc = p.oid where c.castsource = 'char'::regtype and c.casttarget in ('text'::regtype, 'varchar'::regtype)
I understand that the bpchar is a legacy data type and should not be used, but the reason I ask is because I'm working in refactoring a driver for postgres wich use VARCHAR for sending strings in the protocol and I would like to change it to TEXT since is the prefered type, so my options are keep using VARCHAR or implicitly rtrim all strings send from the driver (is this a sane choice?)
thank you for your time,
cheers,
Jorge Solórzano
On Tue, Nov 21, 2017 at 6:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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)
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)
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.
