byte[] data in PostgreSQL

Question

I have a spring application that uses MySQL as database.

I want to migrate the application from MySQL to Postgres, and it seems that I can not declare byte[] array:

I have this column:

@Type(type="org.hibernate.type.BinaryType")
private byte[] data.

I get this error:

Caused by: org.postgresql.util.PSQLException: ERROR: type "tinyblob" does not exist

Is there a way to achive this in PostgreSQL?

You need to specify the PostgreSQL dialect in your JPA provider, you currently seem to be using the MySQL dialect when connecting to PostgreSQL. You may want to specify which JPA provider (Hibernate by the looks of it) you're using. — Mark Rotteveel, Mar 06 '18 at 15:15
You also need to make sure that your obfuscation layer (=ORM) uses `bytea` as the data type for that column. — , Mar 06 '18 at 15:17
In addition to what @MarkRotteveel said, there is no need to use the `@Type` annotation for this. — coladict, Mar 06 '18 at 15:18
In the entity class definition, if I declare only Type, I have an the error from the post. — georgiana_e, Mar 06 '18 at 15:19
check this link : http://wiki.ispirer.com/sqlways/mysql/data-types/tinyblob — Elarbi Mohamed Aymen, Mar 06 '18 at 15:37
I found also this, but doesn't work for me to use byte[] https://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte — georgiana_e, Mar 06 '18 at 16:50

score 5 · Answer 1 · answered Nov 01 '18 at 08:49

In your PostgreSQL DB Table, set the data column datatype to bytea NOT bytea[] and in your POJO class:

@Column(name="name_of_your_column") private byte[] data;

Leave out the @Type(type="org.hibernate.type.BinaryType") annotation.

That should work just fine.

1 Answers1