OFFSET N FETCH FIRST M ROWS with JDBC and PostgreSQL not working

Question

I'm trying to make a query using JDBC and PostgreSQL, but I'm facing a weird situation that I can't find in any documentation.

The following query works if I execute it through pgAdmin and H2 (which I'm using for unit tests for my application), but I'm getting a syntax error if I execute it through JDBC:

Queries.SELECT_SQL

SELECT columns 
  FROM Table 
  LEFT JOIN TableToJoin1 
  LEFT JOIN TableToJoin2 
  LEFT JOIN TableToJoin3 
  JOIN TableToJoin4

---

Queries.ENDING_PAGING_STATEMENT_SQL

OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

---

The SELECT query is correct and works fine using any means, the problem is when I get both queries together, i.e., the SELECT statement along with OFFSET and FETCH FIRST.

This is how I'm executing the query through JDBC:

// Receive offset and limit as argument.
try (final PreparedStatement selectStatement = connection
        .prepareStatement(Queries.SELECT_SQL + Queries.ENDING_PAGING_STATEMENT_SQL)) {

  selectStatement.setInt(Queries.PAGING_ENDING_STATEMENT_OFFSET_ARGUMENT_POSITION, offset);
  selectStatement.setInt(Queries.PAGING_ENDING_STATEMENT_LIMIT_ARGUMENT_POSITION, limit);

  final ResultSet resultSet = bookSelectStatement.executeQuery();
  ...
}

Here is the message of the exception that's being thrown:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"

---

If I call selectStatement.toString() for debugging, I receive the query exactly as I expect.

If I change OFFSET N ROWS FETCH FIRST M ROWS ONLY by OFFSET N LIMIT M it will work through JDBC without any problem. The simplest solution would make that simple change above, but part of my task is to make a query using this FETCH FIRST M ROWS ONLY statement.

So, can you guys help me to see if I'm doing it wrong, or if there's no way of using FETCH FIRST on JDBC for PostgreSQL at all? With some examples or some references to any documentation that would help.

I noticed that FETCH FIRST doesn't exist on PostgreSQL Documentation: Queries - Offset and Limit, but since it works through pgAdmin I don't know why I should have any problem using JDBC.

Thank you so much!

Answer 1

I can reproduce this problem. As of version 10, the documentation says:

In this syntax, to write anything except a simple integer constant for start or count, you must write parentheses around it

Which is ridiculous, of course. Bind variables should be allowed without parentheses - most databases allow them and the OFFSET ? LIMIT ? syntax is also accepted by PostgreSQL, but it looks as though they aren't in PostgreSQL's interpretation of the standard SQL syntax. So you'll have to write:

OFFSET (?) ROWS FETCH FIRST (?) ROWS ONLY

I would consider this a bug, which I've reported here