We’ve had an app running on SQL Server and MySQL under Hibernate for over a year now — but I failed (rather miserably) getting Postgres running well with the same mapping files.

The main problem was that our mapping files use a native generator:

    <generator class="native" />

In SQL Server and MySQL this maps to an auto incrementing column, but in Postgres (I think) you end up with an int or bigint (depending upon your domain object data type), and a separate sequence (named hibernate_sequence), used to populate it.

Thus if you’re applying raw SQL statements outside of Hibernate’s control, you can’t use the same SQL across all 3 databases. In other words, assuming a table:

<hibernate-mapping>
    <class name="MyTableImpl" proxy="MyTable" table="mytable">
        <id name="dbId" column="id">
            <generator class="native" />
        </id>
        <property name="col1" column="col1" not-null="true" />
        <property name="col2" column="col2" not-null="true" />
    </class>
</hibernate-mapping>

The following sql statement…

    insert into mytable (col1, col2) values ('a', 'b')

…will work on SQL Server and MySQL (in these cases, the column “id” is created as an identity or auto_increment), but not on Postgres (the column is created as a basic int).

However, I’ve just found a workaround — not sure if it’s the recommended approach (I’ve seen a couple of other references describing other ways to accomplish the same result, but these involve changing the mapping files), but this way works for me.

The answer is to define your own Hibernate dialect, subclassing the default Postgres dialect:

import java.sql.Types;
import org.hibernate.Hibernate;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.id.IdentityGenerator;

public class CustomPostgresqlDialect extends PostgreSQLDialect {

    public CustomPostgresqlDialect() {
        super();

        // register some functions and column types in here
        ...
    }

    public Class getNativeIdentifierGeneratorClass() {
        return IdentityGenerator.class;
    }
}

This dialect returns an identity generator instead of a sequence generator, which means we don’t need to change our mappings across all 3 databases.

Result!

I keep forgetting this… and it comes up every now and then. The new versions of postgres have pager set to less (at least in my gentoo install they do). Meaning when you quit less, the results vanish from the terminal — which is a pain if you wanted something to refer to.

The answer is to put:

export PAGER=more