If I try to UNION (or INTERSECT or EXCEPT) a common table expression I get a syntax error near the UNION. If instead of using the CTE I put the query into the union directly, everything works as expected.
I can work around this but for some more complicated queries using CTEs makes things much more readable. I also just don't like not knowing why something is failing.
As an example, the following query works:
SELECT *
FROM
(
SELECT oid, route_group
FROM runs, gpspoints
WHERE gpspoints.oid = runs.start_point_oid
UNION
SELECT oid, route_group
FROM runs, gpspoints
WHERE gpspoints.oid = runs.end_point_oid
) AS allpoints
;
But this one fails with: ERROR: syntax error at or near "UNION" LINE 20: UNION
WITH
startpoints AS
(
SELECT oid, route_group
FROM runs, gpspoints
WHERE gpspoints.oid = runs.start_point_oid
),
endpoints AS
(
SELECT oid, route_group
FROM runs, gpspoints
WHERE gpspoints.oid = runs.end_point_oid
)
SELECT *
FROM
(
startpoints
UNION
endpoints
) AS allpoints
;
The data being UNIONed together is identical but one query fails and the other does not.
I'm running PostgreSQL 9.3 on Windows 7.