0

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.

  • 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. – SebDieBln Mar 26 '22 at 00:06
  • Do you have access to the PostgreSQL server? Can you provide the relevant entries in the error-log? It would help understand what query the framework actually produces that contains the syntax error. – SebDieBln Mar 26 '22 at 00:07
  • @SebDieBln Thanks The approach you mentioned works out for me. –  Mar 26 '22 at 14:47
  • I'm glad I could help. I posted the approach as an answer. If you mark it as "accepted" it will help other people to know it solved your issue. – SebDieBln Mar 26 '22 at 17:32
  • It's occurring due to the `Pageable` you are passing. Check this question https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination – Ratul Sharker Mar 27 '22 at 08:21

1 Answers1

0

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.

SebDieBln
  • 3,303
  • 1
  • 7
  • 21