Horton

Thread

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

    Jackson, DeJuan <djackson@cpsgroup.com> — 1998-06-15T21:21:40Z

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

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

    Marc Howard Zuckman <marc@fallon.classyad.com> — 1998-06-19T04:24:03Z

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

  3. Re: [HACKERS] Need help understanding unique indices (fwd)

    Vadim Mikheev <vadim@krs.ru> — 1998-06-19T14:47:47Z

    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