Re: OT: seeking query help, where?

From: "Ross J(dot) Reedstrom" <reedstrm(at)rice(dot)edu>
To: Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>, Tim Lynch <admin+pgsqladmin(at)thirdage(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: OT: seeking query help, where?
Date: 2003-01-17 15:54:50
Message-ID: 20030117155450.GB15778@wallace.ece.rice.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Fri, 17 Jan 2003, Bruno Wolff III wrote:
> On Fri, Jan 17, 2003 at 08:57:14 -0500,
> Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu> wrote:
> >
> > that way you avoid duplicates across tables.
>
> The union operator already removes duplicates.

Right, which means everyone's UNION queries have too many DISTINCTs
in them:

SELECT email from table1
UNION
SELECT email from table2 ;

should do it.

Here's proof: note the duplicate 'foo' and 'bar':

test=# select * from table1;
email
-----------------
foo(at)example(dot)com
bar(at)example(dot)com
bar(at)example(dot)com
(3 rows)

test=# select * from table2;
email
------------------
quux(at)example(dot)com
foo(at)example(dot)com
(2 rows)

test=# select email

test=# select email from table1 union select email from table2;
email
------------------
bar(at)example(dot)com
foo(at)example(dot)com
quux(at)example(dot)com
(3 rows)

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Research Scientist phone: 713-348-6166
The Connexions Project http://cnx./rice.edu fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2003-01-17 16:24:19 Re: OT: seeking query help, where?
Previous Message Ross J. Reedstrom 2003-01-17 15:42:29 Re: Do Something before Abort on Trigger ???