Re: LIKE erratic? or unseen DB corruption?

Frank Miles <fpm(at)u(dot)washington(dot)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