Обсуждение: BUG #1392: could not select filter an entry from copied data table

The following bug has been logged online:

Bug reference:      1392
Logged by:          Kanu Patel
Email address:      kpry99@yahoo.com
PostgreSQL version: 7.4.6
Operating system:   Linux
Description:        could not select filter an entry from copied data table
Details:

I have postgresql 7.4.6 running on linux7.2. I have created couple tables
and loaded about 15k entries using copy command. If I try to get one
perticular entry using "select * from table where col = 'value';" command,
it does not return anything (return 0 row). But if I use the command: select
* from table;, it returns all rows. It filters select commands if the
entries are inserted into the tables manually using inser commands instead
of copy. The problem is - it does not filter select command if the data is
loaded using copy command. Any solution for that? Please email me at
kpry99@yahoo.com.
Thanks. -- Kanu

On Wed, Jan 12, 2005 at 08:12:38PM +0000, Kanu Patel wrote:
>
> I have postgresql 7.4.6 running on linux7.2. I have created couple tables
> and loaded about 15k entries using copy command. If I try to get one
> perticular entry using "select * from table where col = 'value';" command,
> it does not return anything (return 0 row). But if I use the command: select
> * from table;, it returns all rows. It filters select commands if the
> entries are inserted into the tables manually using inser commands instead
> of copy.

Could the COPY data have extraneous whitespace, or could it be a
case sensitivity issue?  What happens when you issue a query like
the following?

SELECT '<' || col || '>' FROM foo WHERE col ILIKE '%value%';

If you still can't figure it out then please provide a real test
case -- show the table definition, the exact query you're making,
the output you expect (culled from an unrestricted SELECT, for
example), and the output you actually got.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, Jan 17, 2005 at 12:36:33PM -0800, Kanu Patel wrote:

> I had an employee table with the following columns in it: id, name, address, phone, payinfo.
> I had loaded the data using: "copy employee from 'employee.txt';" command.
> I want to get namde of one employee, so I have entered the following command:
> select name from employee where id='1';   This returns zero rows.

Is there an employee with id='1'?  What data type is the id column?
If it's a string type (char, varchar, text) then I wonder if the
data has extraneous whitespace.  I mentioned this in my original
reply, and I asked you to run a query like the following:

SELECT '<' || id || '>', name FROM employee WHERE id LIKE '%1%';

Please run that query and look closely at whether there are spaces
around the id value.  If the query doesn't return what you expect,
then please post the query you ran and show the record you expect
it to match.  It might be useful to see that record as the output
of the following command:

pg_dump -t employee -aD | grep 'something'

where 'something' is a pattern that will match the desired record.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Yes, the employee id=1 and it is text. So I agree with you that could be a space problem. How can I resolve that? May
bechange id as integer or real?  

Thanks.



Michael Fuhr <mike@fuhr.org> wrote:
On Mon, Jan 17, 2005 at 12:36:33PM -0800, Kanu Patel wrote:

> I had an employee table with the following columns in it: id, name, address, phone, payinfo.
> I had loaded the data using: "copy employee from 'employee.txt';" command.
> I want to get namde of one employee, so I have entered the following command:
> select name from employee where id='1'; This returns zero rows.

Is there an employee with id='1'? What data type is the id column?
If it's a string type (char, varchar, text) then I wonder if the
data has extraneous whitespace. I mentioned this in my original
reply, and I asked you to run a query like the following:

SELECT '<' || id || '>', name FROM employee WHERE id LIKE '%1%';

Please run that query and look closely at whether there are spaces
around the id value. If the query doesn't return what you expect,
then please post the query you ran and show the record you expect
it to match. It might be useful to see that record as the output
of the following command:

pg_dump -t employee -aD | grep 'something'

where 'something' is a pattern that will match the desired record.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/


---------------------------------
Do you Yahoo!?
 The all-new My Yahoo! – What will yours do?