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
– Mark Rotteveel, Commented Mar 6, 2018 at 15:15
You also need to make sure that your obfuscation layer (=ORM) uses bytea as the data type for that column. — user330315
– user330315, Commented Mar 6, 2018 at 15:17
In addition to what @MarkRotteveel said, there is no need to use the @Type annotation for this. — coladict
– coladict, Commented Mar 6, 2018 at 15:18
In the entity class definition, if I declare only Type, I have an the error from the post. — georgiana_e
– georgiana_e, Commented Mar 6, 2018 at 15:19
check this link : wiki.ispirer.com/sqlways/mysql/data-types/tinyblob — Elarbi Mohamed Aymen
– Elarbi Mohamed Aymen, Commented Mar 6, 2018 at 15:37

Zeeng · Accepted Answer · 2018-11-01 08:49:25Z

8

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.

answered Nov 1, 2018 at 8:49

Zeeng

1,24510 silver badges13 bronze badges

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

1 Answer 1