Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

pgjdbc says the column index is out of range, number of columns: 0

Errororg.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

Short answer

The PostgreSQL driver counted fewer ? placeholders in your SQL than the index you bound. The usual cause is a ? that sits inside a quoted string, such as LIKE '%?%', which the driver treats as plain text. Put the placeholder outside the quotes, build the pattern in Java, and count from 1.

  • Applies to PostgreSQL JDBC driver 42.7, PostgreSQL 17, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare the number of ? in the SQL text with the highest index you pass to a setXxx call. The message gives both numbers: the index you used, and how many parameters the driver found.

    String sql = "SELECT * FROM users WHERE name LIKE '%?%'";
    System.out.println(sql.chars().filter(c -> c == '?').count());
  2. 2Match what you see, then jump to its fix

    1. 1number of columns: 0 although the SQL text clearly contains a ?The ? sits inside a quoted stringFix
    2. 2jsonb operators such as ?, ?| or ?& in the query, and the count is lower or higher than expectedA PostgreSQL operator with a question mark was written as ??, or as ?Fix
    3. 3The index in the message is 0, or is higher than the number you countedThe parameter index is 0, or larger than the number of placeholdersFix

What does the error look like?

  • org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

Cause 1 of 3

The ? sits inside a quoted string

1Check

The driver's SQL parser skips everything inside single quotes, double quotes, dollar quotes and comments, so a ? there is never a bind parameter. LIKE '%?%' therefore has no parameters at all.

grep -rn "'[^']*?[^']*'" src/main/java | grep -i "like\|="

2Fix

Leave the ? bare and add the percent signs to the value, or join them in SQL.

PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM users WHERE name LIKE ?");
ps.setString(1, "%" + term + "%");

// or in SQL
// WHERE name LIKE '%' || ? || '%'

3Verify

Run the query: the exception no longer appears and rows come back.

ResultSet rs = ps.executeQuery();

Confirmed by raw.githubusercontent.com

Did this fix it?

Cause 2 of 3

A PostgreSQL operator with a question mark was written as ??, or as ?

1Check

In JDBC the single ? is the placeholder for a parameter. For PostgreSQL operators that contain a question mark, the driver docs say to write two question marks, and the driver turns ?? into one literal ? in the SQL it sends.

grep -rn "?|\|?&\|jsonb" src/main/java

2Fix

Write the operator with doubled question marks inside a PreparedStatement, then count only the single ? as parameters.

PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM docs WHERE tags ?? ?");
ps.setString(1, "urgent");

3Verify

The statement runs and binds one parameter.

ps.executeQuery();

Confirmed by jdbc.postgresql.org

Did this fix it?

Cause 3 of 3

The parameter index is 0, or larger than the number of placeholders

1Check

JDBC parameter indexes start at 1. An index that does not match a parameter marker in the statement raises an SQLException, and the driver's check is: index below 1 or above the parameter count.

grep -rn "setString(0\|setInt(0\|setLong(0" src/main/java

2Fix

Number the setXxx calls 1, 2, 3 in the order of the ? in the SQL, and remove calls for placeholders that no longer exist after you edited the query.

ps.setString(1, name);
ps.setInt(2, age);

3Verify

Every setXxx index now has a matching placeholder.

ps.executeUpdate();

Confirmed by docs.oracle.com

Did this fix it?

How do I stop it coming back?

  • Never put a ? inside quotes; keep literals in the SQL and values in setXxx calls.
  • Use named parameters from your framework (Spring's NamedParameterJdbcTemplate) so you do not count placeholders by hand.
  • Add a unit test per query that runs it with sample values against a real PostgreSQL.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Databases

Java cannot load org.postgresql.Driver

Class not found loading JDBC org.postgresql.Driver

The PostgreSQL JDBC jar is not on the classpath of the process that runs your code.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.