Inconsistency when creating a new Record from Jooq if already a row is present in Database.

[Anto Aravinth]

Hi All, 

I have a simple table with the following schema:

CREATE TABLE USERDATA(

  ID BIGSERIAL PRIMARY KEY NOT NULL,

  USERNAME VARCHAR(20) NOT NULL,

  PASSWORD VARCHAR(30) NOT NULL

);

Nothing fancy here; After I created the table, I do run a simple insert statement like:

INSERT into USERDATA VALUES (1,'anto','password');

Again nothing fancy here. Now I have written a simple Java call to create a new record from Jooq:

DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);

UserdataRecord record = create.newRecord(USERDATA);

record.setUsername("anto");

record.setPassword("aravinth");

record.store();

But this code fails with the exception:

Caused by: org.jooq.exception.DataAccessException: SQL [insert into "public"."userdata" ("username", "password") values (?, ?) returning "public"."userdata"."id"]; ERROR: duplicate key value violates unique constraint "userdata_pkey"

  Detail: Key (id)=(1) already exists.

The exception is clear, it says the id 1 is already exist, but I would see for the above Java call and the given database state, jooq should have created the row with ID as 2. But it didn't. But the do call the Java code again, everything works! Bit strange to me.

Is it a bug? Or I'm doing something wrong?

I'm running Postgres 9.6.2 and Jooq latest version 3.9.1.

[Lukas Eder]

Hi Anto,

Thanks for your message. This is the expected behaviour in PostgreSQL. The BIGSERIAL data type is just syntax sugar for a BIGINT data type with a system-generated sequence and a DEFAULT clause that selects the nextval from the sequence. DEFAULT here means that the sequence value is used only if there's no *explicit* value from the user's INSERT statement.

The nextval after table creation is 1, but you've already inserted a value 1 explicitly (which you should never do when a sequence is present).

Try again like this:

DROP TABLE USERDATA;

CREATE TABLE USERDATA(

  ID BIGSERIAL PRIMARY KEY NOT NULL,

  USERNAME VARCHAR(20) NOT NULL,

  PASSWORD VARCHAR(30) NOT NULL

);

INSERT into USERDATA VALUES (DEFAULT,'anto','password');

Or, alternatively, specify the USERDATA columns on INSERT:

INSERT into USERDATA(USERNAME, PASSWORD) VALUES ('anto','password');

Now, your jOOQ store() call will insert a new row with ID = 2.

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.