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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] none PRZYSINDA, CARLY HANA (716) 271-5117 571 CLAYBOURNE ROAD ROCHESTER, NY 14618 OPNO Sophomore none none [EMAIL PROTECTED] none
