Re: [GENERAL] LIKE erratic? or unseen DB corruption?
Frank Miles <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] LIKE erratic? or unseen DB corruption?
Is it possible that there are spaces after the 'IDC16W' in the field? Try: LIKE 'IDC16W%' and see if that makes a difference. len >A direct query gets appropriate rows of data: > >dbname=# select * from partdef where shpname = 'IDC16W'; > pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname | value| descrip >---+---+--++-++-+--+--- ---++- >17 | 328 | 08X2 | 0 | 0 || |1 | IDC16W | Header-8x2 | >11 | 323 | 08X2 | 0 | 0 || |1 | IDC16W | Header-8x2 | > >...while the very same query (substituting LIKE for the '=' sign) gets nothing!? > >dbname=# select * from partdef where shpname LIKE 'IDC16W'; > pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname | value | descrip >---+---+-++-++-+--+-+-- -+- >(0 rows) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] LIKE erratic? or unseen DB corruption?
Running 7.1(.0) on a PC running Debian Linux (potato), I have a strange situation in psql: A direct query gets appropriate rows of data: dbname=# select * from partdef where shpname = 'IDC16W'; pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname | value| descrip ---+---+--++-++-+--+--++- 17 | 328 | 08X2 | 0 | 0 || |1 | IDC16W | Header-8x2 | 11 | 323 | 08X2 | 0 | 0 || |1 | IDC16W | Header-8x2 | ...while the very same query (substituting LIKE for the '=' sign) gets nothing!? dbname=# select * from partdef where shpname LIKE 'IDC16W'; pn_id | class | num | mt | ver | loc_id | unit_id | subptcnt | shpname | value | descrip ---+---+-++-++-+--+-+---+- (0 rows) Creating a new table, and populating it with trial values shows no problem -- it all works as expected (both '=' and 'LIKE' returning the same results). If selects are done on other columns (same table) they seem to work correctly, whether one or more rows are returned. Can someone please tell me the really stupid thing that I'm doing wrong? Thanks -frank ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
