Thread: = or LIKE ?
Hi. Can anyone explain me this behavior? testdb=# select E'\\' = E'\\'; ?column? ---------- t (1 row) testdb=# select E'\\' like E'\\'; ?column? ---------- f (1 row) Shouldnt the like operator do the same as the = if there occours no wildcards and stuff in the string? -- Jesper
Jesper Krogh <jesper@krogh.cc> writes: > Shouldnt the like operator do the same as the = if there occours no > wildcards and stuff in the string? If there are also no escape characters, then yeah. FWIW, 8.4 will complain about this case: regression=# select E'\\' like E'\\'; ERROR: LIKE pattern must not end with escape character regards, tom lane
Tom Lane wrote: > Jesper Krogh <jesper@krogh.cc> writes: >> Shouldnt the like operator do the same as the = if there occours no >> wildcards and stuff in the string? > > If there are also no escape characters, then yeah. > > FWIW, 8.4 will complain about this case: > > regression=# select E'\\' like E'\\'; > ERROR: LIKE pattern must not end with escape character So I cannot rely on the like operator to behave correct if I'd like to compare strings with backslashes (e.g. filepaths from MS Windows filesystems)? I actually get the same if it doesnt end with the slashes: testdb=# select E'\\t' like E'\\t'; ?column? ---------- f (1 row) testdb=# select E'\\t' = E'\\t'; ?column? ---------- t (1 row) -- Jesper
Jesper Krogh wrote: > Tom Lane wrote: >> Jesper Krogh <jesper@krogh.cc> writes: >>> Shouldnt the like operator do the same as the = if there occours no >>> wildcards and stuff in the string? >> >> If there are also no escape characters, then yeah. >> >> FWIW, 8.4 will complain about this case: >> >> regression=# select E'\\' like E'\\'; >> ERROR: LIKE pattern must not end with escape character > > So I cannot rely on the like operator to behave correct if I'd like to > compare strings with backslashes (e.g. filepaths from MS Windows > filesystems)? > > I actually get the same if it doesnt end with the slashes: > testdb=# select E'\\t' like E'\\t'; > ?column? > ---------- > f > (1 row) > > testdb=# select E'\\t' = E'\\t'; > ?column? > ---------- > t > (1 row) Ok. The pattern has to be "double escaped".. testdb=# select E'\\t' like E'\\\\t'; ?column? ---------- t (1 row) (for the archives a ref to the documentations i didnt get by the read through) http://www.postgresql.org/docs/8.2/static/functions-matching.html#FUNCTIONS-LIKE -- Jesper
Jesper Krogh <jesper@krogh.cc> writes: > Ok. The pattern has to be "double escaped".. Or specify a different escape character. regards, tom lane
Jesper Krogh wrote: > So I cannot rely on the like operator to behave correct if I'd like to > compare strings with backslashes (e.g. filepaths from MS Windows > filesystems)? test=# SELECT E'\\' LIKE E'\\' ESCAPE ''; ?column? ---------- t (1 row) -- Craig Ringer