index returns different output

From: Viktor Przebinda <viktor(at)solon(dot)Colorado(dot)EDU>
To: pgsql-bugs(at)postgresql(dot)org
Subject: index returns different output
Date: 2000-11-04 21:25:10
Message-ID: 3A047EB6.F0C652BF@solon.colorado.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I have come across a query that produces different output depending on
weather or not an index is imposed on the relation. I am using
postgresql-7.0.2-2 installed as a binary from an rpm downloaded from the
postgres site. My machine is an Intel dual PII 300 with 256MB of ram.
The system runs Linux 2.2.16-3smp (redhat linux 6.2). postmaster is
running as user postgres.

1) create a database (call it cu_database)
2) open that database by doing psql cu_database
3) create the relation by entering the following sql:
create table cu_fall_00_test_2 (name varchar(40), phone varchar(20),
address varchar(60), major char(4), standing char(10), title char(20),
department varchar(40), email varchar(50), home_page varchar(50));
4) create a temporary file, download the attached names.txt file.
5) open a new window, switch to root (using su) and then switch from
root to the postgres account.
6) execute psql cu_database
7) enter the following sql (replace the path so that it finds your
names.txt file)
COPY cu_fall_00_test_2 from 'names.txt' using DELIMITERS '\t';
8) exit the root psql window, switch back to your origional psql
process.
9) enter the following sql:
select * from cu_fall_00_test_2 where name like 'PRZ%';
10) you notice that all fields are returned (as they should)
11) enter the following sql:
create index name on cu_fall_00_test_2 (name);
select * from cu_fall_00_test_2 where name like 'PRZ%';
12) if you see what I see, the same query will now return 0 rows!

--
Viktor Przebinda

Attachment Content-Type Size
names.txt text/plain 492 bytes

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2000-11-05 00:45:11 Re: index returns different output
Previous Message Lamar Owen 2000-11-04 21:21:36 Re: pg_dump doesn't work on Linux PPC