PG Doc comments form wrote:
> create table t(x int);
> create table u(x int);
>
> insert into t values (1), (2), (2), (3), (3);
> insert into u values (1), (2);
>
> select * from t except all select * from u;
> x
> ---
> 3
> 3
> 2
> (3 rows)
I find this pretty odd behavior. Is this not an outright bug? I
haven't read the SQL standard on this matter[*], but if they define
EXCEPT ALL to work like this, then it seems pretty useless. (Maybe they
just didn't intend EXCEPT ALL to be useful?) If this is indeed the
case, maybe we should amend the docs not only to explain this behavior
but also to warn against the construct.
[*] I didn't try terribly hard, but couldn't actually find where the
behavior is defined. What I have on hand is a draft of SQL:2011 where
this appears to be defined in 7.13 <query expression> but I was unable
to find the rules for set operations. It refers to 9.12 Grouping
operations but that defines conformance rules only.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services