Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id WAA66765; Mon, 24 Jan 2000 22:12:50 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id WAA22942; Mon, 24 Jan 2000 22:09:42 -0500 (EST) From: Bruce Momjian Message-Id: <200001250309.WAA22942@candle.pha.pa.us> Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace In-Reply-To: <388D0F84.FF844DF@mascari.com> from Mike Mascari at "Jan 24, 2000 09:50:45 pm" To: Mike Mascari Date: Mon, 24 Jan 2000 22:09:42 -0500 (EST) CC: Tom Lane , pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL66 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > INSERT INTO temp_sales > SELECT DISTINCT on key supplysources.supplysource, > incharges.supply, targets.target, incharges.saledate, > incharges.supplyunit, '', incharges.quantity, incharges.company, > incharges.costcntr, 'Replenish', incharges.price, '','','', 0, > text(supplysources.supplysource)|| > text(incharges.supply)|| > text(targets.target)|| > text(incharges.saledate) as key > FROM supplysources, incharges, targets WHERE > supplysources.warehouse = incharges.warehouse AND > (targets.site,targets.area) = (incharges.site,incharges.area); > > What happens is that a large charges file which is transferred to > a mainframe ERP application is first brought into PostgreSQL. > Depending upon certain race conditions, duplicate "sales" records > can appear in the data file. We use DISTINCT ON to pick (as you > point out) an arbitrary record when duplicates appear. I suppose > we could do a DELETE ... WHERE NOT EXISTS after the import. Using > DISTINCT ON just saves a step. I don't have any arguments beyond > the grounds that we're using it in existing code as a duplicate > record filter - :-( Why not just throw a NOTICE and keep the feature. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026