Re: Issue with CHAR column and "column LIKE column" condition

Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com> writes:
> I was surprised by the behavior of LIKE and the CHAR type. Consider
> the following statements:

> CREATE TABLE t0(c0 CHAR(2)) ;
> INSERT INTO t0(c0) VALUES('a');
> SELECT * FROM t0 WHERE c0 LIKE c0; -- expected: fetches the row,
> actual: does not fetch the row

Yeah. That's because LIKE is not, in fact, symmetric.
The available LIKE operators are

regression=# \do ~~
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
------------+------+---------------+----------------+-------------+-------------------------
pg_catalog | ~~ | bytea | bytea | boolean | matches LIKE expression
pg_catalog | ~~ | character | text | boolean | matches LIKE expression
pg_catalog | ~~ | name | text | boolean | matches LIKE expression
pg_catalog | ~~ | text | text | boolean | matches LIKE expression
(4 rows)

of which the first and third aren't relevant here, and we end up
choosing "character ~~ text" ... so the pattern side receives a
coercion to text, which strips its trailing blanks, and then you
have

'a '::char(2) ~~ 'a'::text

which doesn't match.

The semantics of char(N) are a mess generally. It's possible that
this particular case would act less surprisingly if we got rid of
the char ~~ text operator (forcing blank-stripping on both sides),
or adding char ~~ char (preventing any blank-stripping), but
probably somebody out there would complain if we did either,
because it'd break some other case.

Generally speaking, PG developers aren't excited about messing
around with the semantics of char(N) --- we think it's a legacy
datatype that you're best off not using. Trying to make it act
less surprisingly would be a lot of work with, most likely,
negative return. Anyone who *is* using it has probably tweaked
their app until they got acceptable results, and would complain
that we broke it.

regards, tom lane