postgresql: Converting bytea to bigint

Question

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.

Answer 1

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!

Answer 2

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.

Answer 3

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