Squeryl and PostgreSQL's autoincrement

Question

When I try to insert a new record in a PostgreSQL table using Squeryl's Table.insert, it fires this query:

insert into "users" ("id", "email", "fullname") values (nextval('"s_users_id"'),?,?)

This doesn't work, as I did not define a sequence and instead defined the id column to be "autoincrement":

CREATE TABLE Users (
    id BIGSERIAL,
    email varchar(255) NOT NULL,
    fullname varchar(255),
    PRIMARY KEY (id)
);

I read some old post in another forum about this issue, and I was wondering what the status is now. Are we discouraged to use autoincrement and define a sequence instead? Or is there a simple workaround to get this working?

Edit: In fact just now I see that the autoincrement itself creates a serial, but under another name: users_id_seq Is there a way to tell Squeryl to look under this name instead, or follow PostgreSQL's convention?

score 8 · Accepted Answer · answered Oct 13 '12 at 19:49

You can declare name of the sequence:

 case class Author(id: Long, firstName: String)             

 val author = table[Author]

 on(author)(s => declare(
    s.id is(autoIncremented("your_seq_name"))
 ))

more information: http://squeryl.org/schema-definition.html

score 5 · Answer 2 · answered Oct 09 '12 at 17:56

5

There isn't a terribly clean way to do it right now, but it can be done. Just subclass PostgreSqlAdapter and override:

def createSequenceName(fmd: FieldMetaData)

answered Oct 09 '12 at 17:56

Dave Whittaker

3,102
13
14

score 3 · Answer 3 · 2012-10-09T07:12:56.427

3

serial or bigserial will create a sequence in the background from which the values for the column are taken. So the insert is basically correct.

bigserial is just a shorthand for:

create sequence users_id_seq;
create table users (id bigint default nextval('users_id_seq'));

The only thing that seems wrong is that default name of the sequence for your id column would be users_id_seq, not s_users_id as used by your tool.

More details in the manual: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

Edit:
One possible workaround if that tool assumes the wrong naming convention is to rename the generated sequence. As Postgres "knows" it belongs to the column things will still work.

ALTER SEQUENCE users_id_seq RENAME TO s_users_id;

edited Oct 09 '12 at 07:12

answered Oct 09 '12 at 06:59

Thanks, you are really fast. I just realized the same thing when looking again. So then it would be a question if Squeryl can be modified to look up the sequence under the default name when in PostgreSQL mode. – Jeroen Kransen Oct 09 '12 at 07:04
@JeroenKransen: sorry I don't know Squeryl. As a workaround you could just rename the sequence PostgreSQL created. – Oct 09 '12 at 07:05

Squeryl and PostgreSQL's autoincrement

3 Answers3

Linked