PostgreSQL JDBC Null String taken as a bytea

There's a simple fix: when you're building the query string, if a parameter is going to be null -- use "null" instead of "?".

As @araqnid says, Hibernate is incorrectly casting null values into type bytea because it doesn't know any better.

Since you're calling setParameter(int,Object) with a null value, at a guess the entity manager has no idea which persistence type to use to bind the parameter. Istr Hibernate having a predilection for using SerializableType, which would equate to a bytea.

Can you use the setParameter method that takes a Parameter object instead, so you can specify the type? I haven't really used JPA, so can't give a detailed pointer.

(IMHO this is your just desserts for abusing EntityManager's native-sql query interface to do inserts, rather than actually mapping the entity, or just dropping through to JDBC)

Using Hibernate specific Session API works as a workaround:

String sql = "INSERT INTO person (id, name) VALUES (:id, :name)";
Session session = em.unwrap(Session.class);
SQLQuery insert = session.createSQLQuery(sql);
sql.setInteger("id", 123);
sql.setString("name", null);
insert.executeUpdate();

I've also filed HHH-9165 to report this issue, if it is indeed a bug.

Most of above answers are meaningful and helped me to narrow down issue.

I fixed issue for nulls like this:

setParameter(8, getDate(), TemporalType.DATE);

You can cast the parameter to a proper type in the query. I think this solution should work for other databases, too.

Before:

@Query("SELECT person FROM people person"
     + " WHERE (?1 IS NULL OR person.name = ?1)")
List<Person> getPeopleWithName(final String name);

After:

@Query("SELECT person FROM people person"
     + " WHERE (?1 IS NULL OR person.name = cast(?1 as string))")
List<Person> getPeopleWithName(final String name);

Also works for LIKE statements:

person.name LIKE concat('%', cast(?1 as string), '%')

and with numbers (here of type Double):

person.height >= cast(cast(?1 as string) as double)

If you are willing to use the PreparedStatement class instead of Query:

if (entity.getHistory() == null)
    stmt.setNull(6, Types.VARCHAR);
else
    stmt.setString(6, entity.getHistory());

(It is possible that using ?::text in your query string would also work, but I've never done it that way myself.)

The JDBC setNull with sql type parameter is fallback for legacy JDBC drivers that do not pass the JDBC Driver Test Suite.

One can guard against null errors within the query as follows:

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName

This should work in Postgresql after the fix of this issue.

Had this issue in my springboot application, with postgres and hibernate. I needed to supply date from/to parameters, with possible nulls in every combination.

Excerpt of the query :

   ...
        WHERE  f.idf = :idCat
        AND f.supplier = TRUE
        AND COALESCE (pc.datefrom, CAST('2000-1-1' AS DATE) ) <= COALESCE (:datefrom, now())
        AND COALESCE (pc.dateto, CAST('2200-12-31' AS DATE) ) >= COALESCE (:dateto, now())

Dao call :

@Transactional(readOnly = true)
public List<PriceListDto> ReportCatPriceList(Long idk, Date dateFrom, Date dateTo) {
    Query query = EM.createNativeQuery(queryReport, PersonProductsByDay.class)
        .setParameter("idCat", idk)
        .setParameter("datefrom", dateFrom, TemporalType.DATE)
        .setParameter("dateto", dateTo, TemporalType.DATE);

    return query.getResultList();
}

I had the same problem executing an UPDATE with Oracle Database. Sometimes a few fields that are updated are null and this issue occurs. Apparently JPA is casting my null value into bytea as already spoke in this topic.

I give up of using the Query object to update my table and used the EntityManager.merge(Entity) method.

I simply create the Entity object with all informations and when I execute the merge(Entity), the JPA executes a SELECT, compares the information already persisted on the Data Base with my Entity object and executes the UPDATE correctly. That is, JPA create a "field = NULL" sentence for the Entity variables that are null.

I have the same issue like you. After I researched this problem i found the answer on https://heowc.dev/2021/11/27/hibernate-nativequery-bug-with-postgres.

Query query = getEntityManager().createNativeQuery("insert into table_name(..., history, ....) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
[...]
.setParameter(6, new TypedParameterValue(type of history.INSTANCE, entity.getHistory()))
[...]

query.executeUpdate();

May be it's helpful.