There is an easy way to set hibernate to use different primary key ids for each table with postgres? I tried to use postgres dialect in DataSource:
dialect = org.hibernate.dialect.PostgreSQLDialect or dialect = net.sf.hibernate.dialect.PostgreSQLDialect But it doesn't work. Thanks
The short answer is no, there isn't a easy way to do this. However, I have found a solution that does work. Basically you need to implement a custom dialect. Here is an implementation (please note the original source of the implementation within the comments).
package com.my.custom; import java.util.Properties; import org.hibernate.dialect.Dialect; import org.hibernate.dialect.PostgreSQLDialect; import org.hibernate.id.PersistentIdentifierGenerator; import org.hibernate.id.SequenceGenerator; import org.hibernate.type.Type; /** * Creates a sequence per table instead of the default behavior of one sequence. * * From <a href='http://www.hibernate.org/296.html'>http://www.hibernate.org/296.html</a> * @author Burt */ public class TableNameSequencePostgresDialect extends PostgreSQLDialect { /** * Get the native identifier generator class. * @return TableNameSequenceGenerator. */ @Override public Class<?> getNativeIdentifierGeneratorClass() { return TableNameSequenceGenerator.class; } /** * Creates a sequence per table instead of the default behavior of one sequence. */ public static class TableNameSequenceGenerator extends SequenceGenerator { /** * {@inheritDoc} * If the parameters do not contain a {@link SequenceGenerator#SEQUENCE} name, we * assign one based on the table name. */ @Override public void configure( final Type type, final Properties params, final Dialect dialect) { if (params.getProperty(SEQUENCE) == null || params.getProperty(SEQUENCE).length() == 0) { String tableName = params.getProperty(PersistentIdentifierGenerator.TABLE); if (tableName != null) { params.setProperty(SEQUENCE, "seq_" + tableName); } } super.configure(type, params, dialect); } } } The above implementation is should be stored as TableNameSequencePostgresDialect.java under src/java/com/my/custom within your Grails project.
Next, update your DataSource.groovy to use this new custom dialect.
dialect = com.my.custom.TableNameSequencePostgresDialect That's pretty much about it. Not easy but it can be done.
