SQL query returns `ERROR: syntax error at or near "union"`

Question

I am a beginner in SQL. What is the syntax issue with the query shown here?

I just needed the random rows to be present n number of times.

with recursive cte1 as 
(
    select 1 as idx, company_id, field_values 
    from transaction_detail 
    where company_id = 12345 
    limit 1

    union all 

    select idx + 1 as idx, company_id, field_values 
    from transaction_detail 
    where company_id = 12345 
    limit 1
    where idx < 6
)
select * 
from cte1;

Error is

ERROR: syntax error at or near "union"
LINE 3: union all

SQL state: 42601
Character: 145

Try ancor part to frame with brackets: with recursive cte1 as ((select ... ) ... — ValNik
– ValNik, Commented May 8 at 23:40

Thorsten Kettner · Accepted Answer · 2025-05-09 04:54:11Z

Your CTE is not recursive. Mistakenly, you select from the table again instead of the CTE, which is what would create the recursion.

Then, a LIMIT clause refers to the whole query, but the query has two parts glued with UNION ALL. In order to make the first part a separate query with an own LIMIT clause, you need parantheses around it.

The corrected query:

with recursive cte1 as 
(
    (
    select 1 as idx, company_id, field_values 
    from transaction_detail 
    where company_id = 12345 
    limit 1
    )
    union all 

    select idx + 1 as idx, company_id, field_values 
    from cte1 
    where idx < 6
)
select * 
from cte1;

Demo: https://dbfiddle.uk/gfDNQ21m

An alternative is to select the one row in a separate (non-recursive) CTE first:

with recursive 
  one_row as
  (
    select *
    from transaction_detail 
    where company_id = 12345 
    fetch first row only
  ),
  cte1 as 
  (
    select 1 as idx, company_id, field_values 
    from one_row 

    union all 

    select idx + 1 as idx, company_id, field_values 
    from cte1 
    where idx < 6
  )
select * 
from cte1;

Demo: https://dbfiddle.uk/52PKsIky

(I've used the standard SQL fetch first row only here instead of LIMIT, but that's just personal preference, it makes no difference to the query execution.)

Laurenz Albe · Accepted Answer · 2025-05-09 03:59:31Z

2

To show the missing parentheses with a simple example:

SELECT 1 LIMIT 1 UNION ALL SELECT 2 LIMIT 1;
ERROR:  syntax error at or near "UNION"
LINE 1: SELECT 1 LIMIT 1 UNION ALL SELECT 2 LIMIT 1;
                         ^
(SELECT 1 LIMIT 1) UNION ALL (SELECT 2 LIMIT 1);

 ?column? 
══════════
        1
        2
(2 rows)

answered May 9 at 3:59

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

SQL query returns `ERROR: syntax error at or near "union"`

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related