CODEWITHSUNDEEP
hibernatepostgresqljpapostgresql-9.1
Manish Shukla
Manish Shukla

Reputation: 173

postgresql: Converting bytea to bigint

I have to convert a bytea entry for a query to bigint. How could this be done?

More Info:

I have a hibernate repository as below -

 @Query(value = "update Sample_Table set other_id = ?1 where id = ?2", nativeQuery = true)
 void saveOrUpdateOtherId(Long other_id, Long id);

Hibernate somehow taking id (in where clause) as bytea and since 'Sample_Table' has this id field as bigint and thus it throws type mismatch problem.

I have tried using CAST to convert bytea to bigint but it didn't succeed and error msg says bytea can not be casted to bigint.

How can i change bytea to bigint?

Edit:

Sample_Table DAO:

@Table(name = "Sample_Table")
public class Sample{
    @Id
    @Column(name = "id", unique = true)
    @GeneratedValue
    private Long id;

    @Column(name = "other_id")
    private Long other_id;
}

id field is defined in here as Long.

Edit-2 If someone get such issue, most likely he is passing null value in the query.

Upvotes: 15

Views: 18374

Answers (5)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

There doesn't seem to be a straightforward function to cast from bytea (a chunk of memory) to a base datatype, other than passing through the bit datatype from a properly padded hexadecimal string:

SELECT ('x'||lpad(encode('\001'::bytea, 'hex'), 16, '0'))::bit(64)::bigint

Or, if your bytea is already 8 bytes and your Postgres installation runs with the default setting bytea_output = 'hex', cast to text and remove the leading backslash instead:

SELECT right(bytea_val::text, -1)::bit(64)::bigint
FROM (SELECT '\x0000000000000001'::bytea as bytea_val) x

Upvotes: 3

mowwwalker
mowwwalker

Reputation: 17392

I was running into this issue for a repository query which inserts a record which has a nullable column. When the value for that column is null, hibernate uses the wrong type and I would see exceptions like this from Postgres:

cannot cast type bytea to bigint

Eventually found this blog post with a solution: http://www.carbonrider.com/2020/08/15/org-postgresql-util-psqlexception-error-cannot-cast-type-bytea-to-uuid/ which is to use Hibernate's TypedParameterValue. Copy and pasting their snippets:

@Query("select * from user where firstname=:name and id=:id", nativeQuery=true)
public List<user> findByNameAndId(@Param("name") String firstName, @Param("id")TypedParameterValue id);

UUID userId = ... //Retrived from request parameter.
TypedParameterValue userIdParam = new TypedParameterValue(new PostgresUUIDType(), userId);
userRepository.findByNameAndId(userName, userIdParam);

Not ideal to have a Hibernate-specific solution rather than a purely JPA one, but 🤷‍♂️. Big thanks to "Carbon Rider" or whoever added that post!

Upvotes: 7

Juan Carlos Alafita
Juan Carlos Alafita

Reputation: 280

As Jan Nielsen said

One of your parameters is null which cannot be turned into a bigint.

You are trying to pass null in the query.

Upvotes: 1

Ben Whitmore
Ben Whitmore

Reputation: 967

The following expression worked for me, to convert from bytes::bytea to a bigint:

get_byte(bytes, 0)::bigint << 8
    | get_byte(bytes, 1) << 8
    | get_byte(bytes, 2) << 8
    | get_byte(bytes, 3) << 8
    | get_byte(bytes, 4) << 8
    | get_byte(bytes, 5) << 8
    | get_byte(bytes, 6) << 8
    | get_byte(bytes, 7)

This correctly handles the sign bit as well.

Upvotes: 4

Jan Nielsen
Jan Nielsen

Reputation: 11849

One of your parameters is null which cannot be turned into a bigint.

Upvotes: 2

Related Questions