Need help understanding unique indices (fwd) - Mailing list pgsql-hackers

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@fallon.classyad.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@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!                      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_