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
"name", which you are selecting in your original message. What is
the field type of "name" ?
On Jul 27, 2007, at 6:55 AM, Michał Niklas wrote:
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---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
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