7

I'm using Grails 3.0.7 and Postgres 9.2. I'm very new to Postgres, so this may be a dumb question. How do I correctly associate an id generator sequence with a table? I read somewhere that if you create a table with an id column that has a serial datatype, then it will automatically create a sequence for that table.

However, the column seems to be created with a type of bigint. How do I get Grails to create the column with a bigserial datatype, and will this even solve my problem? What if I want one sequence per table? I'm just not sure how to go about setting this up because I've never really used Postgres in the past.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
cloudwalker
  • 2,346
  • 1
  • 31
  • 69
  • This might be helpful: http://stackoverflow.com/questions/7580807/hibernate-postgresql-with-grails/7581050#7581050 – Joshua Moore Oct 07 '15 at 01:33
  • I've done that. I have no problem getting the sequences to generate. The issue seems to be that grails is associating a bigint datatype with the column rather than a bigserial when creating the table, so the sequence does not get used properly. – cloudwalker Oct 14 '15 at 18:26

2 Answers2

3

You can define a generator in a domain class like this:

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

If the sequence is already present in the database then you'll need to name it in the params.

There are other properties also available as outlined in the documentation, for example:

static mapping = {
 id column: 'book_id', type: 'integer'
}
Alex
  • 8,093
  • 6
  • 49
  • 79
Anshul
  • 687
  • 9
  • 18
1

In Postgres 10 or later consider an IDENTITY column instead. See:

However, the column seems to be created with a type of bigint. How do I get Grails to create the column with a bigserial datatype, and will this even solve my problem?

That's expected behavior. Define the column as bigserial, that's all you have to do. The Postgres pseudo data types smallserial, serial and bigserial create a smallint, int or bigint column respectively, and attach a dedicated sequence. The manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Big quote, I couldn't describe it any better than the manual.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hmm, ok, so perhaps something is wrong with my Grails project setup then, because the id column does not end up owning the corresponding sequence. I thought maybe it was just something Postgres related because I have next to 0 experience with Postgres. – cloudwalker Oct 20 '15 at 02:47