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
2017-04-06 18:04 GMT+02:00 Anto Aravinth <[email protected]>:
> 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.
>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
