Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL cannot work out the type of a null query parameter from Hibernate

ErrorERROR: could not determine data type of parameter $1

Short answer

PostgreSQL works out an unspecified parameter's type from the place where it is first used. When the parameter is null and appears only in a check like :p IS NULL, or inside a function, there is nothing to infer from. Cast the parameter to an explicit type in the query, and bind null with a matching type.

  • Applies to PostgreSQL 16, Hibernate 6, Spring Boot 3, PostgreSQL JDBC driver 42
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find the query that has an optional parameter, usually written as (:name IS NULL OR column = :name), or a function such as lower(:name) around a parameter.

    grep -rn "IS NULL OR\|is null or\|lower(:" src/main --include=*.java
  2. 2Match what you see, then jump to its fix

    1. 1The failing statement has the pattern (:p IS NULL OR col = :p) and the error shows up when p is nullA null parameter appears only in an IS NULL testFix
    2. 2The error mentions a function such as lower(bytea) when a null string reaches lower(:name)An HQL or JPQL query leaves the parameter type openFix
    3. 3Plain JDBC code binds null with a type that differs from the type of the other bindingssetNull is called with a different type than the column needsFix

What does the error look like?

  • ERROR: could not determine data type of parameter $1
  • org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist

Cause 1 of 3

A null parameter appears only in an IS NULL test

1Check

A parameter without a declared type gets its type from the context where it is first referenced, if that is possible. In an IS NULL test there is no other value to compare with, so no type can be inferred.

grep -rn ":[a-zA-Z]* IS NULL\|:[a-zA-Z]* is null" src/main

2Fix

Add a cast so the type is written in the query itself. CAST(expr AS type) and expr::type are the two documented forms.

-- native SQL
WHERE (CAST(:name AS text) IS NULL OR name = CAST(:name AS text))

3Verify

Call the repository method once with null and once with a value; both must run.

mvn -q test -Dtest=UserRepositoryTest

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

An HQL or JPQL query leaves the parameter type open

1Check

In HQL the cast function takes the target as a Java type name such as String, Long or Integer. Look for a bare parameter passed to a function.

grep -rn "lower(:\|upper(:\|concat(:" src/main

2Fix

Cast the parameter to its Java type inside the query.

@Query("select u from User u where :name is null or lower(u.name) like lower(cast(:name as String))")
List<User> search(@Param("name") String name);

3Verify

Run the query with name set to null.

mvn -q test -Dtest=UserRepositoryTest

Confirmed by docs.hibernate.org

Did this fix it?

Cause 3 of 3

setNull is called with a different type than the column needs

1Check

The driver sends the NULL's type to the server, and mixing types for one placeholder forces the statement to be prepared again. Look at every setNull call.

grep -rn "setNull" src/main

2Fix

Use the same type for setNull as for the non-null binding of that placeholder.

if (name == null) {
    ps.setNull(1, java.sql.Types.VARCHAR);
} else {
    ps.setString(1, name);
}

3Verify

Run the statement with both a null and a real value.

mvn -q test

Confirmed by jdbc.postgresql.org

Did this fix it?

How do I stop it coming back?

  • Write optional filters with an explicit cast on the parameter the first time.
  • Cover each optional filter with a test that passes null.
  • Use one bind type per placeholder across your JDBC code.

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?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

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