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
PRZEBINDA, VIKTOR none 1600 HILL SIDE RD. BOULDER, CO 80303 CSEN Sophomore none none
Viktor.Przebinda@Colorado.EDU none
PRZESTRZELSKI, LAURA (303) 460-7180 1395 W 12 AVENUE BROOMFIELD, CO 80020 none none none none none
none
PRZONEK, JEFFREY KEITH (303) 445-9552 1860 WALNUT ST NO 6 BOULDER, CO 80302 ENVS Senior none none
Jeffrey.Przonek@Colorado.EDU none
PRZYSINDA, CARLY HANA (716) 271-5117 571 CLAYBOURNE ROAD ROCHESTER, NY 14618 OPNO Sophomore none none
Carly.Przysinda@Colorado.EDU none