Hibernate postgresql / hsqldb TEXT column incompatibility issue

Question

I have a problem using Hibernate and PostgreSQL for production and HSQLDB for testing.
I use a top-down approach, allowing Hibernate to create a database schema.
I also use annotations; The display part of hibernate.cfg.xml contains only lines like
<mapping class="package.subpackage.ClassName" />
Hibernate by default builds variables for a variable changing (255) on PostgreSQL, which is not enough for me, so I need to redefine some columns manually using
@Column(columnDefinition = "TEXT") .

But the TEXT type is not valid for HSQLDB, so these tables cannot be created.

Can anyone help solve this problem?

Answer 1

The easiest way to deal with this particular problem is to probably not use the columnDefinition parameter at all and instead explicitly specify the length of the column (for example)

 @Column(length=10000) 

It is also possible that you can match it with @Lob (type = LobType.CLOB)

but I'm not sure if this is properly supported in HSQLDB. In Postgres, he should provide you with his TEXT.

Answer 2

Agree with @fredt. The TEXT data type is not a standard SQL type, but an extension that supports any engine.

To enable PostgreSQL compatibility mode , use sql.syntax_pgs=true in the connection options.

Answer 3

HSQLDB 2.1 and later have PostgreSQL compatibility mode and support this TEXT data type in this mode.

Answer 4

To get H2 to work in PostgreSQL compatibility mode (useful for testing junit).

 # JDBC Driver jdbc.driverClassName=org.h2.Driver jdbc.url=jdbc:h2:mem:play;MODE=PostgreSQL;TRACE_LEVEL_SYSTEM_OUT=2;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE;INIT=CREATE TABLE IF NOT EXISTS PG_CLASS (RELNAME text, RELKIND text); jdbc.username=sa jdbc.password= # general hibernate options hibernate.database=h2 hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect 

For Hibernate / JPA to work correctly, you need to create the PG_CLASS table. But other than that - pretty seamlessly.

Answer 5

Yes, you have a really big problem.

DO NOT USE ONE DATA SENSOR FOR TESTING AND OTHER FOR PRODUCTION.

You may encounter problems that you have never dreamed of.