Need help understanding unique indices (fwd)

This message received no replies from the SQL list and I forward
it to hackers looking for additional thoughts.

EXECUTIVE SUMMARY:

I have two tables with identical structure.
One table has a unique index on 5 of the
6 table attributes.

When attempting to insert from the non-indexed
table into the uniquely indexed table, the
insert fails due to "duplicate key" error. (index definition below)

However, this query, which tries to identify tuples with identical keys,
returns 0 rows. Each attribute included in the multifield index
is qualified in the where clause. Why doesn't the
select show the duplicate tuples?

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)

---------- Forwarded message ----------
Date: Fri, 5 Jun 1998 19:42:21 -0400 (EDT)
From: Marc Howard Zuckman <marc(at)fallon(dot)classyad(dot)com>
Subject: Need help understanding unique indices

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(at)fallon(dot)classyad(dot)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! _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_