Need help understanding unique indices - Mailing list pgsql-sql
From | Marc Howard Zuckman |
---|---|
Subject | Need help understanding unique indices |
Date | |
Msg-id | Pine.LNX.3.95.980605192027.15343A-100000@fallon.classyad.com Whole thread Raw |
List | pgsql-sql |
I have a need to incrementally add new data to a table with this structure: Table = propsales +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | closingdate | date | 4 | | county | varchar() | 50 | | city | varchar() | 50 | | streetno | varchar() | 10 | | street | varchar() | 70 | | price | float8 | 8 | +----------------------------------+----------------------------------+-------+ A second table, newpropsales, exists with identical structure. The original table, propsales has a unique index that includes all of the record fields except the price field. The index is defined as follows: CREATE UNIQUE INDEX propsales_key on propsales using btree ( city varchar_ops, street varchar_ops, streetno varchar_ops, county varchar_ops, closingdate date_ops ); When loading new data into the database, it is loaded into table newpropsales. An effort to remvove duplicate tuples is then made using this series of queries: delete from recentpropsales; --temporary table with identical structure to those above. -- get rid of any duplicates contained solely within newpropsales insert into recentpropsales select distinct * from newpropsales; delete from newpropsales; insert into newpropsales select * from recentpropsales; delete from recentpropsales; delete from newminclosingdate; insert into newminclosingdate select min(closingdate) from newpropsales; -- get tuples from accumulated data that are in same time frame as new data. insert into recentpropsales select propsales.* from propsales,newminclosingdate where closingdate >= newminclosingdate.min; -- attempt to eliminate duplicates tuples that are present in -- both tables considered together -- This will NOT eliminate all index duplicates because -- price is not indexed. Therefore, tuples that are identical -- in every way but have different price values will not be -- deleted from the new data set. delete from newpropsales where exists ( select city from recentpropsales r where r.county=newpropsales.county and r.price=newpropsales.price and r.city=newpropsales.city and r.closingdate=newpropsales.closingdate and r.street=newpropsales.street and r.streetno=newpropsales.streetno); All of this seems to work ok. But, this fails insert into propsales select * from newpropsales; because a duplicate key is encountered. However, this query, which tries to identify tuples with identical keys, returns 0 rows. Why? select newpropsales.* from newpropsales n, propsales p where n.city=p.city and n.county=p.county and n.street=p.street and n.streetno=p.streetno and n.closingdate=p.closingdate ; closingdate|county|city|streetno|street|price -----------+------+----+--------+------+----- (0 rows) Marc Zuckman marc@fallon.classyad.com _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ _ Visit The Home and Condo MarketPlace _ _ http://www.ClassyAd.com _ _ _ _ FREE basic property listings/advertisements and searches. _ _ _ _ Try our premium, yet inexpensive services for a real _ _ selling or buying edge! _ _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_