CODEWITHSUNDEEP
sqlpostgresqlgrailsormgrails-orm
Bosh
Bosh

Reputation: 8748

How can I cause Grails/GORM to use default sequence values in postgres?

When I define a domain object like:

class MusicPlayed {

  String user
  Date date = new Date()
  String mood

  static mapping = {
    id name: 'played_id'
    version false
  }

}

I get a postgres sequence automatically defined like:

CREATE SEQUENCE seq_music_played
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

That's great -- but I'd love to have this become the default value for my id field. In other words, I'd like to have the table defined with:

    played_id bigint DEFAULT nextval('seq_music_played'::regclass) NOT NULL,

... but this doesn't happen. So when my client code requires manual SQL invocation, I'm stuck pulling new values form the sequence instead of just relying on auto-population.

Is there any way to cause this table to be created "the way I want," or do I need to forgo gorm's table-creation magic and just create the tables myself with a db-creation script that runs at install-time?

Note My question is similar to How to set up an insert to a grails created file with next sequence number?, but I'm specifically looking for a solution that doesn't pollute my client code.

Upvotes: 3

Views: 4833

Answers (4)

pmc
pmc

Reputation: 1005

This works for me :-)

 static mapping = {
    id generator: 'native', params: [sequence: 'my_seq'], defaultValue: "nextval('my_seq')"
 }

Generating something like:

create table author (
    id int8 default nextval('nsl_global_seq') not null,...

for postgresql.

Upvotes: 2

boecko
boecko

Reputation: 2185

I would use:

    static mapping = {
     id generator: 'native', params:[sequence:'your_seq']
    }

Additionally, i would update the DEFAULT-Value of the id-column via

ALTER TABLE your_table ALTER COLUMN id SET DEFAULT nextval('your_seq');

This is extremely useful for manual INSERTs

UPDATE - use liquibase for the default-column-problem:

changeSet(author:'Bosh', id:'your_table_seq_defaults', failOnError: true) {
sql ("ALTER TABLE your_table ALTER COLUMN id SET DEFAULT nextval('your_seq')")
}

Upvotes: 1

rawi
rawi

Reputation: 531

I tend to create my tables directly in PostgreSQL and then map them in grails.

I took the best idea to the sequences-generated-IDs from here:

http://blog.wolfman.com/articles/2009/11/11/using-postgresql-with-grails

Give it a try and then smile at your former problems :)

rawi

Upvotes: 0

Kurt Battisti
Kurt Battisti

Reputation: 1

You can define it in Config.groovy

grails.gorm.default.mapping = {
    id generator: 'sequence'
}

Upvotes: -2

Related Questions