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.