Need help understanding unique indices (fwd)

I didn't sit down and analyze what you did wrong, but this test worked
for me:

DROP TABLE propsales;
CREATE TABLE propsales (
closingdate date,
county varchar(50),
city varchar(50),
streetno varchar(10),
street varchar(70),
price float8
);
CREATE UNIQUE INDEX propsales_key on propsales using btree ( city
varchar_ops,
street varchar_ops, streetno varchar_ops,
county varchar_ops, closingdate date_ops );
DROP TABLE newpropsales;
CREATE TABLE newpropsales (
closingdate date,
county varchar(50),
city varchar(50),
streetno varchar(10),
street varchar(70),
price float8
);
INSERT INTO propsales VALUES('6/15/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales VALUES('6/16/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales VALUES('6/17/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales VALUES('6/18/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/15/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/16/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/29/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/30/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales
SELECT n.*
FROM newpropsales AS n
WHERE NOT EXISTS (SELECT p.*
FROM propsales AS p
WHERE n.city = p.city AND
n.street = p.street AND
n.streetno = p.streetno AND
n.county = p.county AND
n.closingdate = p.closingdate);
SELECT * FROM propsales;

Enjoy,
-DEJ

On Mon, 15 Jun 1998, Jackson, DeJuan wrote:

I didn't sit down and analyze what you did wrong, but this test worked
for me:

DROP TABLE propsales;
CREATE TABLE propsales (
closingdate date,
county varchar(50),
city varchar(50),
streetno varchar(10),
street varchar(70),
price float8
);
CREATE UNIQUE INDEX propsales_key on propsales using btree ( city
varchar_ops,
street varchar_ops, streetno varchar_ops,
county varchar_ops, closingdate date_ops );
DROP TABLE newpropsales;
CREATE TABLE newpropsales (
closingdate date,
county varchar(50),
city varchar(50),
streetno varchar(10),
street varchar(70),
price float8
);
INSERT INTO propsales VALUES('6/15/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales VALUES('6/16/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales VALUES('6/17/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales VALUES('6/18/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/15/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/16/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/29/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO newpropsales VALUES('6/30/98', 'Dallas', 'Dallas', '9859',
'Valley Ranch Pkwy.', 10830.73);
INSERT INTO propsales
SELECT n.*
FROM newpropsales AS n
WHERE NOT EXISTS (SELECT p.*
FROM propsales AS p
WHERE n.city = p.city AND
n.street = p.street AND
n.streetno = p.streetno AND
n.county = p.county AND
n.closingdate = p.closingdate);
SELECT * FROM propsales;

Enjoy,
-DEJ

While this query makes just as much sense as the ones that I tried,
it also fails on my database. Once again, I do not understand why.
Bug???

realestate=> begin;
BEGIN
realestate=> INSERT INTO propsales
realestate-> SELECT n.*
realestate-> FROM newpropsales AS n
realestate-> WHERE NOT EXISTS (SELECT p.*
realestate-> FROM propsales AS p
realestate-> WHERE n.city = p.city AND
realestate-> n.street = p.street AND
realestate-> n.streetno = p.streetno AND
realestate-> n.county = p.county AND
realestate-> n.closingdate = p.closingdate);
ERROR: Cannot insert a duplicate key into a unique index
realestate=> abort;
ABORT

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

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

Marc Howard Zuckman wrote:

While this query makes just as much sense as the ones that I tried,
it also fails on my database. Once again, I do not understand why.
Bug???

realestate=> begin;
BEGIN
realestate=> INSERT INTO propsales
realestate-> SELECT n.*
realestate-> FROM newpropsales AS n
realestate-> WHERE NOT EXISTS (SELECT p.*
realestate-> FROM propsales AS p
realestate-> WHERE n.city = p.city AND
realestate-> n.street = p.street AND
realestate-> n.streetno = p.streetno AND
realestate-> n.county = p.county AND
realestate-> n.closingdate = p.closingdate);
ERROR: Cannot insert a duplicate key into a unique index

I can't reproduce this! (6.3.2 on Solaris 2.5 (sparc),
6.4-current on FreeBSD 2.2.6)

Vadim