BUG #5877: problem with wild char used in where clause

"Savita" <savita.halli@gmail.com> wrote:

PostgreSQL version: 8.3.5

I have a table with id as primary key.

When I ran query to get the ids which ends with B1
select id from table where id like '%B1'
does not return me any rows.

You might want to check all the bug fixes listed in the 8.3 bug fix
releases since the 2008 version you're running:

http://www.postgresql.org/docs/8.3/static/release.html

Then consider an in-place upgrade:

http://www.postgresql.org/support/versioning

and see if the problem is still there. It doesn't exist on the more
current releases I'm running. For example:

test=# create table t (id text not null primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
test=# insert into t values ('AB1'),('AB2'),('AB3'),('AB4');
INSERT 0 4
test=# select id from t;
id
-----
AB1
AB2
AB3
AB4
(4 rows)

test=# select id from t where id like 'A%';
id
-----
AB1
AB2
AB3
AB4
(4 rows)

test=# select id from t where id like '%B1';
id
-----
AB1
(1 row)

If you apply the available 8.3 bug fixes and still see the problem,
creating a self-contained test case like the above will allow others
to better help you.

-Kevin

On Thu, Feb 10, 2011 at 11:02:40AM +0000, Savita wrote:

When I ran query to get the ids which ends with B1
select id from table where id like '%B1'
does not return me any rows. Should it be not returning row with id AB1? Is
there known issue?

is this by any chance char(x) datatype?

if yes - please read:
http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

"Savita" <savita.halli@gmail.com> wrote:

When I ran query to get the ids which ends with B1
select id from table where id like '%B1'
does not return me any rows. Should it be not returning row with
id AB1? Is there known issue?

Oh, I did have one more thought after I posted -- what is the data
type of the id column. If it is character(n), also specified as
char(n), you would need to include trailing spaces or a wildcard at
the end of your string. For all purposes, including pattern
matching, char(n) values are considered to be padded with space
characters to their declared length. So if the id was char(4) the
'%B1' would be considered a match to AAB1 but not AB1.

Use of char(n) columns is generally discouraged because the
semantics required by the standard can be rather surprising.
Consider varchar(n) or text instead.

-Kevin