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.

You should provide your Sample_Table class as well. Usually it is because you defined the `id` not in a proper way. — SWiggels, Oct 05 '15 at 08:38
'id' field is defined here as Long (bigint). Not sure, why 8 bit big int is interpreted as byte array here. — Manish Shukla, Oct 05 '15 at 08:46
When you've verified your table *is* using `bigint`, I'd highly suspect Spring's mapping your Long in a crazy way. Are you sure you're passing a `Long` into that `saveOrUpdateOtherId` function? Have you tried a non-native query and does it fail in the same way? — mabi, Oct 05 '15 at 09:03
System up gradation request and its actual implementation will take atleast a week time. Still, it is not clear that it is postgres issue that got fixed in latest version. — Manish Shukla, Oct 05 '15 at 09:05
Have looked into similar SO posts. http://stackoverflow.com/questions/16044754/heroku-postgresql-django-comments-tastypie-no-operator-matches-the-given-na and http://stackoverflow.com/questions/3739808/no-operator-matches-the-given-name-and-argument-types-you-might-need-to-add-e They possibly have corrected this problem via tweaking their query. But my query is very simple and dont know if repo query require some alteration. — Manish Shukla, Oct 05 '15 at 09:08
@mabi - I have verified that i am passing a long value. I have not tried non-native query yet. Will give it a try. — Manish Shukla, Oct 05 '15 at 09:09

score 7 · Answer 1 · answered Oct 16 '20 at 05:46

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!

See also > CAST(CAST(?1 AS TEXT) AS BIGINT) > https://stackoverflow.com/a/62321877/1503636 — user1503636, Oct 08 '21 at 16:15
for other types one can use StandardBasicTypes class to specify types. — Shekhavat, Jun 18 '22 at 13:10

Ezequiel Tolnay · Answer 2 · 2021-09-23T02:49:41.770

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

score 3 · Answer 3 · answered May 15 '18 at 22:02

3

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.

answered May 15 '18 at 22:02

Ben Whitmore

857
1
6
15

Could you add an example of using? – Joseph Katzman Dec 21 '20 at 16:06
The code fails when I pass `E'\\x100000'`. – Joseph Katzman Dec 21 '20 at 16:23

score 2 · Answer 4 · answered Mar 24 '18 at 22:29

2

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

answered Mar 24 '18 at 22:29

Jan Nielsen

10,892
14
65
119

score 1 · Answer 5 · answered Oct 22 '18 at 16:54

1

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.

answered Oct 22 '18 at 16:54

Juan Carlos Alafita

260
1
7

postgresql: Converting bytea to bigint

5 Answers5

Linked