From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Frank Miles <fpm(at)u(dot)washington(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: LIKE erratic? or unseen DB corruption? |
Date: | 2001-05-21 17:09:53 |
Message-ID: | 29234.990464993@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2001-05-21 17:17:23 | Re: psql shell problem |
Previous Message | Denis A. Doroshenko | 2001-05-21 16:53:22 | Re: psql shell problem |