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
