How to write a native query to get random items for PostgreSQL.
When I use SQL Server, this query runs correctly
@Query(value = "SELECT TOP (:range) * FROM books ORDER BY NEWID()", nativeQuery = true)
List<Book> findRandomBooks(@Param("range") int range);
But when I change to PostgreSQL, it always returns an error:
JDBC exception executing SQL [SELECT TOP (?) * FROM books ORDER BY NEWID()] [ERROR: syntax error at or near "FROM"
Position: 19] [n/a]; SQL [n/a]
3
As mentioned above by @Frank, the TOP & NEWID() are not supported in PostgreSQL and are SQL Server-specific.
Updated code to make it compatible with PostgreSQL,
@Query(value = "SELECT * FROM books ORDER BY RANDOM() LIMIT :range", nativeQuery = true)
List<Book> findRandomBooks(@Param("range") int range);
- ORDER BY RANDOM() – random ordering of results, alternative for NEWID().
- LIMIT – alternative for TOP.
In case, if you want to make it compatible for both, then you can either create a conditional query for each database or define a custom Hibernate dialect.