Postgres Pro
Downloads
Home   >   mailing lists

Thread: INTERSECT/EXCEPT and duplicates?

INTERSECT/EXCEPT and duplicates?

From
Tatsuo Ishii
Date:
Hi,

SQL92 seems to assume that results of the INTERSECT/EXCEPT contain no
duplications unless "ALL" is specified. However PostgreSQL 6.5.1 does
not follow the requirement, I think. Maybe we need to add this to the
TODO list?
---
Tatsuo Ishii

Re: [HACKERS] INTERSECT/EXCEPT and duplicates?

From
Bruce Momjian
Date:
> Hi,
> 
> SQL92 seems to assume that results of the INTERSECT/EXCEPT contain no
> duplications unless "ALL" is specified. However PostgreSQL 6.5.1 does
> not follow the requirement, I think. Maybe we need to add this to the
> TODO list?
> ---

Added to TODO:

* have INTERSECT/EXCEPT prevent duplicates unless ALL is specified

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026

Re: [HACKERS] INTERSECT/EXCEPT and duplicates?

From
Tom Lane
Date:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> SQL92 seems to assume that results of the INTERSECT/EXCEPT contain no
> duplications unless "ALL" is specified. However PostgreSQL 6.5.1 does
> not follow the requirement, I think.

I think you are right --- the ALL keyword is only implemented for UNION
(and it's not quite right there either :-().  For the other two, you
just get whichever behavior was easiest to implement, I suppose...
        regards, tom lane