Thread: except all & WITH - syntax error?

Hi!
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

ERROR: syntax error at or near "WITH" LINE 3: WITH abc AS (SELECT 1) SELECT
1 ^ SQL state: 42601 Character: 21

"PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit"

Is this expected behaviour? 
It's much harder to optimize queries without this option.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

pinker <pinker@onet.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

thank you for the answer, had no idea about "syntactic precedence" thing.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 07/03/2018 02:05 AM, pinker wrote:
> thank you for the answer, had no idea about "syntactic precedence" thing.

The order in which commands are executed in the absence of specific 
instructions e.g. the use of parenthesis.


-- 
Adrian Klaver
adrian.klaver@aklaver.com