RE: [HACKERS] Need help understanding unique indices (fwd)

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

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