PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: Add Limit Parameter

In Oracle I can have an SQL string like this

select * from table where rownum <= ?

Then in an PreparedStatement I can set the rownum as a parameter.

How does it work with Postgres JDBC (Java) with the LIMIT clause? Doing the following won’e let me set a parameter:

select * from table limit ?
Tags:

4

Answers


  1. 0

    It is not possible use a ? placeholder in a LIMIT clause. If you absolutley needed to do this from SQL, one workaround might be to use ROW_NUMBER() with a subquery. In other words, change this:

    SELECT * FROM yourTable ORDER BY some_col LIMIT ?

    to this:

    SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY some_col) rn
    FROM yourTable
) t
WHERE rn < ?;

    However, in practice, if you are using JPA, you could use the various JPA methods to programmatically limit the size of the result set.

    Login or Signup to reply.
  2. 0

    Apparently you are using such an old version of the JDBC driver (version 7.x.x or lower), in which the LIMIT parameterization is not supported. This version was released about twenty years ago and is completely outdated. I recommend that you switch to using a new version of the driver as soon as possible.

    In PostgreSQL, you can use LIMIT to limit the number of records retrieved.

    In the following code

    PreparedStatement statement = connection.prepareStatement("SELECT * FROM users LIMIT ?"));
statement.setInt(1, 10);

    You create a prepared statement and set the LIMIT parameter to get the first 10 records.

    For more information, see the PostgreSQL documentation.

    Login or Signup to reply.
  3. 0

    In PostgreSQL, unlike Oracle where ROWNUM can be parameterized in a PreparedStatement, the LIMIT clause in PostgreSQL does not accept a placeholder (?) directly. The LIMIT clause expects an integer literal and doesn’t support parameterized values in the same way as typical WHERE conditions.

    However, you can still achieve the desired result by using a PreparedStatement and dynamically inserting the LIMIT value into the SQL query. Dynamically build the query string with the LIMIT value included.

    Here is code example of Java

    int limit = 10;
String sql = "SELECT * FROM tablename LIMIT " + limit;

PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();

// Other code to process the result set
    Login or Signup to reply.
  4. 0

    How does it work with Postgres JDBC (Java) with the LIMIT clause?
    Doing the following won’e let me set a parameter:

    select * from table limit ?

    This expands on answer by @Andrey Smelik.

    Using Postgres JDBC driver (access database using Java programming), you can use the SQL LINIT clause to limit the number of rows returned by the query.

    This also allows set the prepared statement’s (of JDBC API) parameter to specify the "limit". For example:

    Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "mydb", "***");
final String SQL = "SELECT name FROM table LIMIT ?";

try (PreparedStatement pst = conn.prepareStatement(SQL)) {

    pst.setInt(1, 5); // limit the number of rows by 5
    ResultSet rs = pst.executeQuery();
    while (rs.next())
        System.out.println("Column data: " + rs.getString("name"));
}

// ... close connection, etc.

    The above code prints five rows of name column value from the database table table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search