How to perform "a UNION b" when a and b are CTEs?

Question

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.

Answer 1

The problem is because CTEs are not direct text-substitutions and a UNION b is invalid SELECT syntax. The SELECT keyword is a mandatory part of the parsing and the syntax error is raised before the CTEs are even taken into account.

This is why

SELECT * FROM a
UNION
SELECT * FROM b

works; the syntax is valid, and then the CTEs (represented by a and b) are then used at the table-position (via with_query_name).