3

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.

Gregory Arenius
  • 2,904
  • 5
  • 26
  • 47

2 Answers2

4

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).

user2864740
  • 60,010
  • 15
  • 145
  • 220
  • I didn't know about the SELECT being a mandatory part of the syntax. I didn't think that the CTEs were direct text-substitutions because they're run once and then can be reused. I was under the, apparently incorrect, impression that "union" just took the results from the query and didn't worry about the query itself. Thank you for the explanation and the title fix. – Gregory Arenius Aug 20 '14 at 19:12
  • You're welcome - there be lots of fun things with SQL. I apologize for being so harsh initially. – user2864740 Aug 20 '14 at 19:13
1

At least in SQL Server, I can easily do this - create two CTE's, and do a SELECT from each, combined with a UNION:

WITH FirstNames AS 
(
    SELECT DISTINCT FirstName FROM Person
), LastNames AS 
(
    SELECT DISTINCT LastName FROM Person
)
SELECT * FROM FirstNames
UNION 
SELECT * FROM LastNames

Not sure if this works in Postgres, too - give it a try!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459