Indeed, PostgreSQL (like SQLite and Vertica) do not support qualified column names in the UPDATE's SET clause. This should be handled correctly by jOOQ. Are you sure your dsl reference is really using the SQLDialect.POSTGRES? You can check like this:
System.out.println(dsl.configuration().dialect()); Hope this helps, Lukas 2017-02-27 2:04 GMT+01:00 <[email protected]>: > I want to update a row, > I've tried statements like: > > dsl.update(AUTH_LOGIN_USER) > .set(AUTH_LOGIN_USER.FAILED_LOGIN_COUNT, 0) > .where(AUTH_LOGIN_USER.LOGIN_NAME.eq(credentials. > getUsername())).execute(); > > and > > AuthLoginUserRecord authuser = dsl.fetchOne(AUTH_LOGIN_USER, > AUTH_LOGIN_USER.LOGIN_NAME.equal(credentials.getUsername())); > authuser.setFailedLoginCount(0); > authuser.update(); > > they failed with: > "Access database using jOOQ; bad SQL grammar [update > \"public\".\"auth_login_user\" set > \"public\".\"auth_login_user\".\"failed_login_count\" > = ? where \"public\".\"auth_login_user\".\"login_name\" = ?]; nested > exception is org.postgresql.util.PSQLException: ERROR: column \"public\" > of relation \"auth_login_user\" does not exist\n Position: 39", > > The sql looks like: > update "public"."auth_login_user" set > "public"."auth_login_user"."failed_login_count" > = ? where "public"."auth_login_user"."login_name" = ? > > and when I was trying to play with direct sql, I got working: > String sql = "update public.auth_login_user set failed_login_count = 0 > where public.auth_login_user.login_name = '" + credentials.getUsername() > + "'"; > dsl.execute(sql); > > which failes as soon as I add table as prefix to columname, i.e "... > auth_login_user.failed_login_count = 0 ..." fails. > I guess I'm supposed to alter the jooq's update command to have no > schema/table name here... or there is something else wrong here. > > > btw similar select (and delete) work just fine: > dsl.select(AUTH_LOGIN_USER.LOGIN_NAME, AUTH_LOGIN_USER.FAILED_LOGIN_ > COUNT).from(AUTH_LOGIN_USER) > .where(AUTH_LOGIN_USER.LOGIN_NAME.eq(credentials. > getUsername())).execute()); > > db is pstgresql 9.6.2 on OSX > > -- > 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.
