RE: [HACKERS] Need help understanding unique indices (fwd)
От | Marc Howard Zuckman |
---|---|
Тема | RE: [HACKERS] Need help understanding unique indices (fwd) |
Дата | |
Msg-id | Pine.LNX.3.95.980619002214.22167A-100000@fallon.classyad.com обсуждение исходный текст |
Ответ на | RE: [HACKERS] Need help understanding unique indices (fwd) ("Jackson, DeJuan" <djackson@cpsgroup.com>) |
Список | pgsql-hackers |
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! _ _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
В списке pgsql-hackers по дате отправления: