RE: [HACKERS] 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

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