Thread: BUG #3493: Cannot find data with = operator

The following bug has been logged online:

Bug reference:      3493
Logged by:          Michal Niklas
Email address:      mn@heuthes.pl
PostgreSQL version: 8.1.9
Operating system:   Linux, x86-64, debian
Description:        Cannot find data with = operator
Details:

I cannot find just inserted data with = operator.
It can be found if I use LIKE.

My SQL looks like:
begin;
select version();
insert into customers (name) values ('KIOSK ');
select id,name from customers where name='KIOSK ';
select id,name from customers where name like 'KIOSK ';
rollback;


Result on my psql console:

isof_customer=# select version();
                                                     version
----------------------------------------------------------------------------
-------------------------------------
 PostgreSQL 8.1.9 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

isof_customer=# insert into customers (name) values ('KIOSK ');
INSERT 437819002 1
isof_customer=# select id,name from customers where name='KIOSK ';
 id | name
---------------+------------------
(0 rows)

isof_customer=# select id,name from customers where name like 'KIOSK ';
 id | name
---------------+------------------
        218856 | KIOSK
(1 row)


This is problem in just one table in one database.  It works as expected on
just created tmp table and on other customers databases.  It works if I
change 'KIOSK ' to other text.  I tried 'vacuum analyze' on this table but
it didn't help.

Regards,
Michal Niklas
HEUTHES
Poland

Michal Niklas wrote:

> isof_customer=# insert into customers (name) values ('KIOSK ');
> INSERT 437819002 1
> isof_customer=# select id,name from customers where name='KIOSK ';
>  id | name
> ---------------+------------------
> (0 rows)
>
> isof_customer=# select id,name from customers where name like 'KIOSK ';
>  id | name
> ---------------+------------------
>         218856 | KIOSK
> (1 row)

What's the type of the name column?  Please be specific.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
> Michal Niklas wrote:
>
>
>> isof_customer=# insert into customers (name) values ('KIOSK ');
>> INSERT 437819002 1
>> isof_customer=# select id,name from customers where name='KIOSK ';
>>  id | name
>> ---------------+------------------
>> (0 rows)
>>
>> isof_customer=# select id,name from customers where name like 'KIOSK ';
>>  id | name
>> ---------------+------------------
>>         218856 | KIOSK
>> (1 row)
>>
>
> What's the type of the name column?  Please be specific.
>


It is varchar.

I'm sorry I didn't reported data structure.  This table has many columns
and indexes,
and there is fragment of sql to create this table:

create table "customers" ( "id" serial, "customer" varchar(254) default '', -- other columns Constraint
"customers_pkey"Primary Key ("id") 
);

create index customer_idx on customers (customer);
-- other indexes

--
Regards,
Michal Niklas
Poland

"Michal Niklas" <mn@heuthes.pl> writes:
> This is problem in just one table in one database.  It works as expected on
> just created tmp table and on other customers databases.  It works if I
> change 'KIOSK ' to other text.  I tried 'vacuum analyze' on this table but
> it didn't help.

REINDEX maybe?

            regards, tom lane

The schema specification you provided doesn't even include the column=20=20
"name", which you are selecting in your original message.  What is=20=20
the field type of "name" ?

On Jul 27, 2007, at 6:55 AM, Micha=C5=82 Niklas wrote:

> Alvaro Herrera wrote:
>> Michal Niklas wrote:
>>
>>
>>> isof_customer=3D# insert into customers (name) values ('KIOSK ');
>>> INSERT 437819002 1
>>> isof_customer=3D# select id,name from customers where name=3D'KIOSK ';
>>>  id | name
>>> ---------------+------------------
>>> (0 rows)
>>>
>>> isof_customer=3D# select id,name from customers where name like=20=20
>>> 'KIOSK ';
>>>  id | name
>>> ---------------+------------------
>>>         218856 | KIOSK
>>> (1 row)
>>>
>>
>> What's the type of the name column?  Please be specific.
>>
>
>
> It is varchar.
>
> I'm sorry I didn't reported data structure.  This table has many=20=20
> columns and indexes,
> and there is fragment of sql to create this table:
>
> create table "customers" (
>  "id" serial,
>  "customer" varchar(254) default '',
>  -- other columns
>  Constraint "customers_pkey" Primary Key ("id")
> );
>
> create index customer_idx on customers (customer);
> -- other indexes
>
> --=20
> Regards,
> Michal Niklas
> Poland
>
>
> ---------------------------(end of=20=20
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that=20=20
> your
>       message can get through to the mailing list cleanly

Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938

Tom Lane wrote:
> "Michal Niklas" <mn@heuthes.pl> writes:
>
>> This is problem in just one table in one database.  It works as expected on
>> just created tmp table and on other customers databases.  It works if I
>> change 'KIOSK ' to other text.  I tried 'vacuum analyze' on this table but
>> it didn't help.
>>
>
> REINDEX maybe?
>
>
Yes, REINDEX helped.

Thanks,
Michal Niklas