PostgreSQL JDBC Null String taken as a bytea

Question

If entity.getHistory() is null following code snippet:

(getEntityManager() returns spring injected EntityManager, database field history type is: text or varchar2(2000)

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

query.executeUpdate();

Gives strange exception:

17/11/11 06:26:09:009 [pool-2-thread-1]  WARN util.JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804
17/11/11 06:26:09:009 [pool-2-thread-1] ERROR util.JDBCExceptionReporter:101 - ERROR: **column "history" is of type text but expression is of type bytea**

Hint: You will need to rewrite or cast the expression.

Problem occurred only in this configuration:
OS: CentOS release 5.6 (Final)
Java: 1.6.0_26
DB: PostgreSQL 8.1
JDBC Driver: postgresql-9.1-901.jdbc4
Application Server: apache-tomcat-6.0.28

Everything is working fine on few other configurations or when history is empty string. Same statement executed from pgAdmin works fine.

I guess problem is in PostgreSQL JDBC driver, is there some sensible reason to treat null string as a bytea value? Maybe some strange changes between Postgres 8 and 9?

Paucity

I would suspect the EntityManager calling the wrong PreparedStatement.setXXX() method for NULL values. You could also try the 8.1 JDBC driver. Btw: are you aware that 8.1 is no longer supported? – Hindrance

Can you clarify your first two paragraphs? They don't seem to make sense. – Angellaangelle

Same code working fine with PostgreSQL 9+ (@a_horse_with_no_name Client must have 8.1... ), Windows 7 etc.If entity.getHistory() is "" instead of null it's working. Same insert executed from pgAdmin works fine. – Paucity

@laidlook: did you try the 8.1 driver against the 8.1 database? (but I still think it's a problem with that EntityManager thing) – Hindrance

Kinda. I don't have access to this server, all I can do is ask client to do this kind of things. They did with no result, so they say... Sad developer life :) I'll try to create this configuration on VM. – Paucity

Answer 1

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.

Underlie

Answer 2

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)

Cap

Answer 3

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.

Paramatta

note I had this problem with a Short and there you can't use setShort() because it takes a non-nullable short. With hibernate 4.x the solution is to do: .setParameter("short", ShortValue, ShortType.INSTANCE); (with 3.x I think it's Hibernate.SHORT instead of using the ShortType). – Hardbitten

Was trying to escape JPA into hibernate for hours. Thanks! – Black

New issue for update statements: hibernate.atlassian.net/browse/HHH-14778 – Sower

Answer 4

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);

Talbert

For me .setParameter("yearLow",yearLow, IntegerType.INSTANCE) did the trick. – Drawl

This doesn't work for me - when the date is null I get expected type [null] passed with TemporalType; expecting Date or Calendar – Orjonikidze

Answer 5

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)

Exhibitive

Answer 6

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.)

Tulatulip

Answer 7

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.

Names

Answer 8

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();
}

Elusive

Answer 9

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.

Cernuous

Answer 10

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.

Michel