Interesting, looks like you've run into a bug / missing feature. I've created an issue for this: https://github.com/jOOQ/jOOQ/issues/4364
In other databases, the money "type" is more or less just an alias for decimal. It looks as though in PostgreSQL, it is more. It would make sense for future versions of jOOQ to automatically bind SQL money types to JSR 354 javax.money.MonetaryAmount. All that's missing to convert BigDecimal to PostgreSQL money is a cast. You can follow the examples given in the manual showing how JSON data types can be bound in PostgreSQL: http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings It should work the same way with money. Let me know if this works for you, Lukas 2015-06-04 13:40 GMT+02:00 <[email protected]>: > Hi all! > > I'm trying to learn JOOQ and I get some strange behavior with PostgesSQL > money type. > JOOQ codegen generates BigDecimal field in class for money field in DB. > But when I try to select and use that field in WHERE clause, there are > exception. > > Here are table definition: > > CREATE TABLE "user" > ( > id serial NOT NULL, > username character varying NOT NULL, > balance money, > CONSTRAINT user_table_pkey PRIMARY KEY (id), > CONSTRAINT user_table_age_fkey FOREIGN KEY (age) > REFERENCES age_categories (name) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE "user" > OWNER TO postgres; > > > And here are my code throwing exception: > > Connection connection = DriverManager.getConnection(url, dbUser, dbPass); > Settings settings = new Settings(); > settings.setParamType(ParamType.INLINED); > DSLContext dslContext = PostgresDSL.using(connection, POSTGRES_9_4, > settings); > Result<UserRecord> fetch = dslContext.selectFrom(USER) > .where(USER.BALANCE.gt(new BigDecimal(1000))) > .limit(100) > .fetch(); > System.out.println(fetch); > > > Here are exception print: > > Exception in thread "main" org.jooq.exception.DataAccessException: SQL > [select "public"."user"."id", "public"."user"."username", > "public"."user"."age", "public"."user"."current_mood", > "public"."user"."height", "public"."user"."friends_ids", > "public"."user"."balance" from "public"."user" where > "public"."user"."balance" > ? limit ?]; ERROR: operator does not exist: > money > numeric > Hint: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > Position: 249 > at org.jooq.impl.Utils.translate(Utils.java:1644) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:661) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356) > at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290) > at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2316) > at com.maxifier.noorm.Test.main(Test.java:50) > Caused by: org.postgresql.util.PSQLException: ERROR: operator does not > exist: money > numeric > Hint: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > Position: 249 > 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.execute(AbstractJdbc2Statement.java:413) > at > org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194) > at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:247) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342) > ... 3 more > > > Please tell me, I don't unserstand something and write bad code or that > are a bug? > > -- > 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.
