Re: except all & WITH - syntax error?

pinker <pinker(at)onet(dot)eu> writes:
> Something strange happened to me right now. I'm trying to compare results
> from one query with rewritten version and everything is ok with this order:

> WITH abc AS (SELECT 1) SELECT 1
> except all
> SELECT 1

> but when I'm trying other way around it throws an error:
> SELECT 1
> except all
> WITH abc AS (SELECT 1) SELECT 1

You need some parens:

# SELECT 1
except all
(WITH abc AS (SELECT 1) SELECT 1);
?column?
----------
(0 rows)

In your first example, the WITH actually attaches to the whole EXCEPT
construct, not the first sub-select as I suspect you're thinking.

In short: WITH has lower syntactic precedence than UNION/INTERSECT/EXCEPT.
You need parens if you want it to work the other way 'round.

regards, tom lane