I have a java application that supports mysql and OracleDB to this point. It also uses hibernate so when I was asked to make it support postgresql (considering that I’ve enabled in the past the support for OracleDB) I said sure, that’s an easy thing.
Well… no, not really. It took me a full day to make it work and it involved a lot of refactoring (good thing that I only have around 20 tables to handle).
Some obstacles that I had to jump (not going into specific details):
- postgres, by default, lower cases the names of the identifiers. (e.g. if you have table “users” and column “Name” in it, doing select users.Name will fail telling you the column does not exist. Fix: select users.”Name”. even better fix: don’t use capitals, ever).
- postgres has the boolean type. mysql does not. making the booleans to integers fixes the issue.
- postgres queries are case sensitive and there’s no way of making it case insensitive via hibernate. mysql is not case sentive on windows (on linux, it is) so you can run into problems. I don’t even want to think of a migration from one to the other.
- GenerationType.AUTO (the default in JPA) works fine with mysql (defaults to auto_increment), but not so fine with postgres (defaults to using a sequence, hibernate_sequence; anyway the sequence stragety is not recommended). Switching to GenerationType.IDENTITY fixed the problem.
Lesson learned here: If you decide to use a database in your application, decide upfront what databases you want to use and stick to the conventions. Otherwise you might go through the painful process of learning that even if SQL is standardized, the current databases are not.