How to correctly associate an id generator sequence with a table

Question

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.

Answer 1

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'
}

Answer 2

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

• Auto increment table column

---

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:

• Get table and column "owning" a sequence
• Safely rename tables using serial primary key columns