Spring data repository sends null as bytea to PostgreSQL database

Question

After switching from MySQL to PostgreSQL I found out that my SQL query (@Query in spring data repository interface) does not work anymore. The issue is caused by null value being sent as bytea and I'm getting following exception:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = bytea
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Repository with @Query:

public interface WineRepository extends PagingAndSortingRepository<Wine, Long> {
    @Query(value = "SELECT * FROM WINE w WHERE (?1 IS NULL OR w.id = ?1)", nativeQuery = true)
    Wine simpleTest(Long id);
}

Simple test:

LOGGER.warn("test1: {}", wineRepository.simpleTest(1L));    //ok
LOGGER.warn("test2: {}", wineRepository.simpleTest(null));  //PSQLException

In the real case I have multiple parameters which can be null and I would prefer not checking them in java code but sending them to sql query. I have checked questions here on stackoverflow but found none with a good answer especially for spring data repository @query annotation.

What is a correct way of handling null values with PostgreSQL? Or do you have any hints how to fix my approach? Thanks!

Update: Issue seems to be related to nativeQuery = true, when value is false, null values work as expected. So the question is whether it is possible to make it function even with nativeQuery enabled.

Jodiug · Accepted Answer · 2020-06-11 10:06:11Z

11

Try this.

SELECT *
FROM WINE w
WHERE ?1 IS NULL OR w.id = CAST(CAST(?1 AS TEXT) AS BIGINT)

It satisfies the type checker and should have the same properties as the original query. CAST is not a big performance hit if it happens on a constant value rather than a value from a database row.

answered Jun 11, 2020 at 10:06

Jodiug

6,1487 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shazin · Accepted Answer · 2016-04-25 10:39:33Z

0

You are trying to check whether a Java null is equal to Postgres NULL which I think is not necessary. You can rewrite as following and avoid sending null to the simpleTest function at all.

@Query(value = "SELECT * FROM WINE w WHERE (w.id IS NULL OR w.id = ?1)", nativeQuery = true)
Wine simpleTest(Long id);

answered Apr 25, 2016 at 10:39

shazin

22k3 gold badges58 silver badges72 bronze badges

1 Comment

rhorvath Over a year ago

But my intention is to check whether input from Java is null. I am not trying to select values from database which are null.

HerdDad · Accepted Answer · 2017-09-13 15:32:45Z

0

I had a similar issue in an @Query where a Long was being interpreted by Postgres as a bytea. The solution for me was to unbox the @Param...by passing a long value, Postgres correctly interpreted the value as a bigint

answered Sep 13, 2017 at 15:32

HerdDad

1

1 Comment

SimonH Over a year ago

In addition to this reply we converted the Long into a primitive. If the Long object was null, we initialized the primitive with -1 and checked the -1 in the sql query (:modelId = -1 OR table.model_id = :modelId)

fjsv · Accepted Answer · 2020-06-03 09:46:19Z

0

I know it is an old issue, but you should be able to fix that bay casting the java value. Something like:

public interface WineRepository extends PagingAndSortingRepository<Wine, Long> {
@Query(value = "SELECT * FROM WINE w WHERE (?1\\:\\:bigint IS NULL OR w.id = ?1\\:\\:bigint)", nativeQuery = true)
Wine simpleTest(Long id);

answered Jun 3, 2020 at 9:46

fjsv

71413 silver badges26 bronze badges

Comments

Vu Tran · Accepted Answer · 2023-05-09 06:30:23Z

0

I am experiencing the same issue as you are. After I researched this problem i found the answer on https://github.com/spring-projects/spring-data-jpa/issues/2370

The problem will be solved by using Spring Data JPA 2.7. You can find the source code to demonstrate this at https://github.com/heowc-scratch/hibernate_postgres_HHH-14778/tree/main.

maybe it's helpful

answered May 9, 2023 at 6:30

Vu Tran

3644 silver badges9 bronze badges

Collectives™ on Stack Overflow

Spring data repository sends null as bytea to PostgreSQL database

5 Answers 5

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related