Re: LIKE erratic? or unseen DB corruption?

Frank Miles <fpm@u.washington.edu> writes:
> Seq scan for '=' and for 'LIKE'; no locale support enabling.  As Len
> Morgan suggested, it appears to be a matter of LIKE being sensitive to
> trailing spaces, and '=' NOT being sensitive to them.  The field data type
> is char(16) {not stated in my original message}.

Bingo.  '=' for char(N) fields is not sensitive to trailing spaces, per
SQL specs.  But LIKE does not have a variant for char(N), it's purely
a "text" operation; so it thinks spaces are significant.

As near as I can tell, the SQL spec does not have any provision that
requires ignoring trailing blanks in a LIKE comparison on char(N),
which seems rather an oversight on their part.

I tend to think that char(N) is evil and should be avoided in favor of
varchar or text.  Those trailing spaces are just too likely to cause
confusion; and when do they buy you anything?

            regards, tom lane