Thread

Need help understanding unique indices

Marc Howard Zuckman <marc@fallon.classyad.com> — 1998-06-05T23:42:21Z

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!				      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_