PostgreSQL cannot work out the type of a null query parameter from Hibernate
ERROR: could not determine data type of parameter $1Short 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.
Fix it fast
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=*.java2Match what you see, then jump to its fix
- 1
The 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
The 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
Plain 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
- 1
What does the error look like?
ERROR: could not determine data type of parameter $1org.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/main2Fix
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=UserRepositoryTestConfirmed by postgresql.org
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/main2Fix
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=UserRepositoryTestConfirmed by docs.hibernate.org
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/main2Fix
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 testConfirmed by jdbc.postgresql.org
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
Databases
Flyway cannot handle the PostgreSQL URL after upgrading to Flyway 10 or newer
No database found to handle jdbc:postgresql://localhost:5432/app
Since Flyway 10, each database has its own module.
4 causes, each with a fixDatabases
H2 in PostgreSQL mode still behaves differently from PostgreSQL
Names or null ordering differ from PostgreSQL in test results
H2 compatibility modes are switched on in the JDBC URL and cover only a small part of the differences between databases.
4 causes, each with a fixDatabases
Hibernate cannot find the sequence hibernate_sequence on PostgreSQL
ERROR: relation "hibernate_sequence" does not exist
Hibernate 6 no longer uses one shared hibernate_sequence.
3 causes, each with a fixDatabases
PSQLException: column user0_.id does not exist in a Hibernate query
org.postgresql.util.PSQLException: ERROR: column user0_.id does not exist
Hibernate generated SQL that names a column PostgreSQL does not have under that exact name.
3 causes, each with a fixDatabases
Spring Boot fails on startup: HikariPool-1 exception during pool initialization
Caused by: ... (the underlying driver exception)
HikariCP opens one connection when the pool starts, and the startup fails when that connection fails.
3 causes, each with a fixDatabases
JDBC cannot log in to PostgreSQL: authentication type 10
org.postgresql.util.PSQLException: The authentication type 10 is not supported.
Authentication type 10 is SCRAM-SHA-256, the password method PostgreSQL 14 and later use by default.
3 causes, each with a fix