Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
Date: 2000-01-25 02:50:45
Message-ID: 388D0F84.FF844DF@mascari.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Tom Lane wrote:
> 
> If I don't hear loud hollers very soon, I'm going to eliminate the
> DISTINCT ON "feature" for 7.0.  As previously discussed, this feature
> is not standard SQL and has no clear semantic interpretation.
> 

I grepped our source code and found this query:

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 - :-(

Just FYI, 

Mike Mascari

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-01-25 02:51:51
Subject: Re: [HACKERS] Happy column dropping
Previous:From: Chris BitmeadDate: 2000-01-25 02:50:30
Subject: Re: [HACKERS] Happy column dropping

pgsql-sql by date

Next:From: Philip WarnerDate: 2000-01-25 03:03:49
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
Previous:From: Chris BitmeadDate: 2000-01-25 02:41:25
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group