ERROR: column "image" is of type bytea but expression is of type oid in Postgres using Jhipster

Question

I'm getting this error with a Jhipster App deployed on GAE (Postgres 9.6) which I'm not getting in local DB H2:

common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: column "image" is of type bytea but expression is of type oid

from an entity Photo:

package es.mibar.web.domain;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;

import javax.persistence.*;
import javax.validation.constraints.*;

import java.io.Serializable;
import java.time.Instant;

/**
 * A Photo.
 */
@Entity
@Table(name = "photo")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Photo implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    @NotNull
    @Column(name = "creation_date", nullable = false)
    private Instant creationDate;

    @Lob
    @Column(name = "image")
    private byte[] image;

    @Column(name = "image_content_type")
    private String imageContentType;

    @Size(min = 2, max = 25000)
    @Column(name = "description", length = 25000)
    private String description;

    @ManyToOne(optional = false)
    @NotNull
    @JsonIgnoreProperties("photos")
    private Local local;

    @ManyToOne
    @JsonIgnoreProperties("photos")
    private Course course;

JDL:

entity Photo {
    creationDate Instant required,
    image ImageBlob,
    description String minlength(2) maxlength(25000)
}

I have no idea though may be this is a hint: Hibernate, Postgresql: Column "x" is of type oid but expression is of type byte

But I'm not sure if this is a bug (since I haven't mofidied that entity) and it should be fixed in Jhipster or that solution could be applied.

Thanks for you help.

Does this answer your question? [proper hibernate annotation for byte\[\]](https://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte) — vicpermir, Feb 24 '20 at 21:28
Thanks. It looks promising. Let me see and i'll get back to you to let you know. — Mike, Feb 25 '20 at 09:29

Mike · Answer 1 · 2020-02-26T17:47:52.050

11

This is an old bug https://github.com/jhipster/generator-jhipster/issues/1940 and the solution is to change the @Lob annotation with:

@Type(type="org.hibernate.type.BinaryType")
@Column(name = "image")
private byte[] image;

It happens in Postgres 9.6 which is the version used at GCP. May be you wont have it in a newer version, but if it happens to you too, please write a comment with the Postgres version that is giving you the problem. Thanks for you help.

PD: i leave the question and the answer as a different one because it is a jhipster potencial bug that we can not reproduce. So if anyone falls in the same problem and can reproduce it, we would like to report it as a Jhipster issue to make the workaround official. If it does not happen again, we will forget about it. Thanks for your consideration.

edited Feb 26 '20 at 17:47

answered Feb 26 '20 at 10:57

Mike

1,059
5
28
51

I was searching for this issue since 2 days, messed up with chat GPT, but finally OLD is GOLD, thanx @Mike – Dnyaneshwar Jadhav Jun 11 '23 at 18:06
As a reference, the answer for Hibernate 6 is here: https://stackoverflow.com/questions/75354646/using-type-for-binary-data-in-hibernate-6 – user462982 Jul 20 '23 at 13:50

Peter F · Answer 2 · 2021-01-26T10:25:36.760

Check your hibernate migrations. The table column type may be incorrect (this was the problem for me).

I used ./mvnw liquibase:diff while on an H2 database, so the column type was set to blob, but it needs to be longblob for postgresql.

auto-generated with blob:

    <changeSet author="generated" id="1610443997705-19">
        <addColumn tableName="photo">
            <column name="image" type="blob"/>
        </addColumn>
    </changeSet>

update the type="blob" to type="longblob"

fixing migration with longblob (could not migrate oid to bytes, so i just drop and re-add the column):

    <changeSet author="generated" id="1611655471347-1">
        <dropColumn tableName="photo" columnName="image"/>
        <addColumn tableName="photo">
            <column name="image" type="longblob"/>
        </addColumn>
    </changeSet>

ERROR: column "image" is of type bytea but expression is of type oid in Postgres using Jhipster

2 Answers2