[FIXED] PostgreSQL Union query not working in JPA

Issue

The below PostgreSQL query works correct in pgAdmin.

(select * from posts where id = 1) union (select * from posts);

but when written in Spring Data JPA it doesn't work.

   @Query(value = "(select * from posts where id = 1) union (select * from posts)",
            nativeQuery = true)
    Page<Post> getPosts(Pageable pageable);

It gives an exception

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "where"
  Position: 15

i want to have a union of the two select statements but the union query is not working in JPA.

Solution

How about

select
  *
from (
  (select * from posts where id = 1)
  union
  (select * from posts)
) as subquery

as your query? This is a query where the framework can append its WHERE clause without a syntax error.

Answered By - SebDieBln
Answer Checked By - Senaida (JavaFixing Volunteer)