Hibernate postgresql/hsqldb TEXT column incompatibility problem

Question

I have a problem using Hibernate and PostgreSQL for production and HSQLDB for testing.
I am using top-down approach letting Hibernate create database schema.
I am also using annotations; mapping part of hibernate.cfg.xml only contains lines like
<mapping class="package.subpackage.ClassName" />
Hibernate defaults String variables to character varying(255) on PostgreSQL which is not sufficient for me in some cases, so I have to redefine some columns manually using
@Column(columnDefinition = "TEXT").
But, TEXT type is invalid for HSQLDB, so those tables can not be created.

Can anyone help to solve this?

Have you tried to use Postgres for testing too? If yes what kind of problems you've met? — Milen A. Radev, Nov 18 '10 at 11:49
Yes, I use Postgres for testing, too. No problems with it, only hsqldb incompatibility problem — Nemanja, Nov 19 '10 at 10:49

score 12 · Accepted Answer · answered Nov 18 '10 at 12:50

12

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

@Column(length=10000)

It might also be that you could instead map it with @Lob(type = LobType.CLOB)

but I'm not sure that is supported properly in HSQLDB. In Postgres it should give you your TEXT type.

answered Nov 18 '10 at 12:50

Don Roby

40,677
6
91
113

1

Yes, this is the most simple solution, but I would prefer the use of HSQLDB with postgres compatibility mode. – G. Demecki Oct 25 '12 at 11:28

score 10 · Answer 2 · answered Oct 25 '12 at 11:29

10

Agree with @fredt. TEXT data type isn't standard SQL type, but extension that some engine supports.

To enable PostgreSQL compatibility mode use sql.syntax_pgs=true in your connection parameters.

answered Oct 25 '12 at 11:29

G. Demecki

10,145
3
58
58

score 6 · Answer 3 · answered Apr 14 '11 at 21:49

6

HSQLDB 2.1 and later has a PostgreSQL compatibility mode and supports the TEXT data type in this mode.

answered Apr 14 '11 at 21:49

fredt

24,044
3
40
61

terrance.a.snyder · Answer 4 · 2013-07-04T19:49:32.127

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

# 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

The create table PG_CLASS is required to allow Hibernate/JPA to correctly function. But other than that - pretty seamless.

score 0 · Answer 5 · answered Oct 31 '18 at 06:27

0

Yes, just try on blow to make HSQLDB run in PostgreSQL compatibility mode.

jdbc.url=jdbc:h2:mem:mydb;sql.syntax_pgs=true

answered Oct 31 '18 at 06:27

Sam

536
1
4
9

This didn't work for me, but an independent property `sql.syntax_pgs=true` did work (spring boot jpa) – Bohemian Mar 02 '20 at 06:19

score -9 · Answer 6 · answered Nov 18 '10 at 17:10

-9

Yes, you have a really big problem.

DON'T USE ONE DATABASE ENGINE FOR TESTING, AND ANOTHER FOR PRODUCTION.

You can hit upon problems you've never dreamed about.

answered Nov 18 '10 at 17:10

Szymon Lipiński

27,098
17
75
77

1

Yeah, thanx, I had that in mind and I've organized tests so they can be easily run on both databases. But recently I had this little change and hsqldb began making problems. – Nemanja Nov 19 '10 at 10:46
24

Yes and No. Agree that test environment **should be** exactly like production environment. But unit tests are something different: the use of in-memory database (like Apache Derby or HSQLDB) is perfectly fine. And what is more: has many advantages. – G. Demecki Oct 25 '12 at 11:09
1

This comment is probably the best argument for using more than one database engine for testing and production, considering the kind of project where you have to deploy the artifact in many environments. – s.froehlich Jun 25 '14 at 15:39
Ideally, use the same db for internal integration tests (component tests) and for production, but the final decision depends on many factors. The purpose of integration testing is to check the integration between component and layers. In addition, the test must be quick so as not to penalize the integration cycle, it must be able to raise the context quickly. Therefore, the use of H2, although not fully compatible with postgre, compensates and allows us to verify the robustness of our development and the integration of the components. – leon cio Jul 23 '20 at 08:07
On the other hand, if we are using hibernate, it is that we seek to be agnostic as much as possible, about the type of db used. In this context, not only is it acceptable to use different types of db in test and production, but it is also recommended. – leon cio Jul 23 '20 at 08:14

Hibernate postgresql/hsqldb TEXT column incompatibility problem

6 Answers6

Linked