MySQL to PostgresSQL migration

Today I migrated a 10 MB database from MySQL to PostgresSQL. I used the “ESF Database Migration Toolkit – Standard 6.5.06”. It works pretty good for me.

I access the database with a spring/hibernate based java application. After some litle changes on the applicationContext.xml I could successfully start my app and access the postgresSQL database.

When I tried to enter new data through the java application I got some errors:

Caused by: java.sql.BatchUpdateException: Batch-Eintrag 0 insert into

The reason for this error is that postgresSQL uses sequences to generate IDs. The migration toolkit from ESF created for every table a separate sequence. By default hibernate creates one sequence for all tables.

After I customized the hibernate mappings to use the sequences created by the migration toolkit from ESF, it works fine for me. Here is the right mapping for a custom sequence.

<id name="id" type="long" column="`ID`">
 <generator >
 <param name="sequence">work_unit_id_seq</param>
 </generator>
 </id>

It is absolute important that the sequence name is spelled in small letters, in the postgresSQL database.