"id" column being inserted with null on postgresql serial type when it should not be include in the sql statement

[Nathan Modrzewski]

I just recently updated my Java environment from 1.7 to 1.8. When I did this, all of my current systems started failing due to the same SQL issue. The insert statement is now including the column "id" which is of type serial and is the only column in the primary key. Before, JOOQ did not include the "id" column on inserts, which caused Postgresql 9.4 to automatically get the next sequence value. Is there any way to fix this so it stop sending over the id column in the inserts with a null value? Below is an example of the code used to do the insert, along with the SQL and error that is generated.

        SessionsRecord r = db.newRecord(Tables.SESSIONS);

        r.setToken(token);

        r.setRemoteAddress(remoteAddress);

        r.setCookieId(cookieId);

        r.setMemberId(memberId);

        r.setApplicationId(applicationId);

        r.setGenerateCookie(generateCookie);

        r.setSecondAuthenticationVerifiedOn(toTS(secondAuthenticationVerifiedOn));

        r.setSystemApprovedOn(toTS(systemApprovedOn));

        r.setCreatedOn(now);

        r.setUpdatedOn(now);

        r.store();

 SQL [insert into "public"."sessions" ("cookie_id", "created_on", "generate_cookie", "id", "member_id", "remote_address", "second_authentication_verified_on", "system_approved_on", "token", "updated_on") values (?, cast(? as timestamp), ?, ?, ?, ?, cast(? as timestamp), cast(? as timestamp), ?, cast(? as timestamp)) returning "public"."sessions"."id"]; ERROR: null value in column "id" violates not-null constraint

  Detail: Failing row contains (null, 2016-05-28 21:27:48.379, false, null, null, null, null, null, null, null, 2016-05-28 21:27:48.379).

        at org.jooq.impl.Tools.translate(Tools.java:1908)

        at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:659)

        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:362)

        at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:184)

        at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:155)

        at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)

        at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:151)

        at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:195)

        at org.jooq.impl.UpdatableRecordImpl.access$000(UpdatableRecordImpl.java:85)

        at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:135)

        at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)

        at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:131)

        at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:123)

        at com.bitdivvy.signer.server.dao.SessionDAO.insert(SessionDAO.java:226)

        at com.bitdivvy.signer.server.rest.SessionsEP.createSession(SessionsEP.java:73)

        at com.bitdivvy.signer.server.servlets.BasePage.createSession(BasePage.java:34)

        at com.bitdivvy.signer.server.servlets.MainWebPage.getJAXBObject(MainWebPage.java:25)

        at com.bitdivvy.signer.server.servlets.ObjectPage.doGet(ObjectPage.java:27)

        at javax.servlet.http.HttpServlet.doHead(HttpServlet.java:244)

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:645)

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)

        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)

        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)

        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)

        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)

        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)

        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)

        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)

        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

        at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint

  Detail: Failing row contains (null, 2016-05-28 21:27:48.379, false, null, null, null, null, null, null, null, 2016-05-28 21:27:48.379).

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)

        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)

        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)

        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

        at org.jooq.tools.jdbc.DefaultPreparedStatement.executeQuery(DefaultPreparedStatement.java:83)

        at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:425)

        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)

        ... 38 more

[Nathan Modrzewski]

Never Mind!!! I solved my issue. I updated to the latest version of JOOQ and regenerated the classes etc. using the latest JOOQ generator and it fixed everything. 

[Lukas Eder]

Thank you for the update, Nathan.

Hmm, I'm not sure how this issue could have been related to upgrading from Java 7 to 8, or from a recent jOOQ update. This should have always worked exactly as such. The primary key (identity) value is not stored by jOOQ if you don't explicitly set it to some value...

Anyway, glad it works now. Please, let me know if you re-encounter any issues in this area, and I'm very happy to help.

Best Regards,

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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.