Hibernate 6, Postgres, and bytea

I had a similar problem, that I need to support H2,MariaDB and PostgreSQL at the same time. In addition to the original problem I also had to map CLOB to text. Based on the Kotlin solution, I have created this java class.

public class CustomPostgreSQLDialect extends PostgreSQLDialect {

    public CustomPostgreSQLDialect() {
        super();
    }

    @Override
    protected String columnType(int sqlTypeCode) {
        if (SqlTypes.BLOB == sqlTypeCode) {
            return "bytea";
        }
        if (SqlTypes.CLOB == sqlTypeCode) {
            return "text";
        }
        return super.columnType(sqlTypeCode);
    }

    @Override
    protected String castType(int sqlTypeCode) {
        if (SqlTypes.BLOB == sqlTypeCode) {
            return "bytea";
        }
        if (SqlTypes.CLOB == sqlTypeCode) {
            return "text";
        }
        return super.castType(sqlTypeCode);
    }

    @Override
    public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        JdbcTypeRegistry jdbcTypeRegistry = typeContributions.getTypeConfiguration().getJdbcTypeRegistry();
        jdbcTypeRegistry.addDescriptor(Types.BLOB, BinaryJdbcType.INSTANCE);
        jdbcTypeRegistry.addDescriptor(Types.CLOB, LongVarcharJdbcType.INSTANCE);
    }

}

It's hard to be sure, since you have not told us what the Java type of the field you're trying to map with the @Lob annotation is, but hell, just this once I'll have a go at reading your mind, and speculate that what you've written is this:

@Lob 
byte[] binaryData;

Now, the Postgres JDBC driver doesn't support using the LOB APIs to read/write bytea columns, and Hibernate knows about this, so it does the next-most-sensible thing, and assumes that this field maps to an oid column. This turns out to not be what you want.

Of course, you could try bypassing Hibernate's knowledge of Postgres by writing:

@Lob @Column(columnDefinition="bytea") 
byte[] binaryData;

And then you would get an error from Postgres or its driver (I forget which), because, as I said, the postgres driver doesn't support this.

So what to do?

Well, sure, you could try hacking the Hibernate Dialect as you have done above, to tell Hibernate to:

1. use bytea for the JDBC type BLOB, and
2. bind JDBC BLOBs using the usual handling for the JDBC type BINARY or VARBINARY or whatever.

And yeah, you could still make that work easily enough in H6.

Or, you know, you could just remove the stupid @Lob annotation which isn't doing any useful work here, and let Hibernate treat your field as a varbinary, which maps to bytea by default.

byte[] binaryData;

Now, look, there might be something more to what you're doing here, but then you'll need to help me out here by actually posting the details of the field and how you've mapped it.

We had exactly the same problem and manage to fix it with this dialect class (kotlin):

class CustomSQLDialect : PostgreSQLDialect(DatabaseVersion.make(9, 5)) {
    override fun columnType(sqlTypeCode: Int): String {
        return when (sqlTypeCode) {
            SqlTypes.BLOB -> "bytea"
            else -> super.columnType(sqlTypeCode)
        }
    }

    override fun castType(sqlTypeCode: Int): String {
        return when (sqlTypeCode) {
            SqlTypes.BLOB -> "bytea"
            else -> super.castType(sqlTypeCode)
        }
    }

    override fun contributeTypes(typeContributions: TypeContributions, serviceRegistry: ServiceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry)
        val jdbcTypeRegistry = typeContributions.typeConfiguration
            .jdbcTypeRegistry
        jdbcTypeRegistry.addDescriptor(Types.BLOB, BinaryJdbcType.INSTANCE)
    }
}

I just remove @Lob and its works

@Column(name = "media", columnDefinition = "bytea", nullable = true)
 private byte [] media;