PostgreSQL money type

[oleg.ol...@gmail.com]

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?

[Lukas Eder]

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

--
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.

[Oleg Oleynik]

Yes, it works for me. 

Here are my code for PostgreSQL money type binding:

<database>
    <name>org.jooq.util.postgres.PostgresDatabase</name>
    <includes>.*</includes>
    <inputSchema>public</inputSchema>
    <customTypes>
        <customType>
            <name>PgMoney</name>
            <type>java.math.BigDecimal</type>
            <binding>com.maxifier.noorm.PgMoneyBinding</binding>
        </customType>
    </customTypes>
    <forcedTypes>
        <forcedType>
            <name>PgMoney</name>
            <types>money</types>
        </forcedType>
    </forcedTypes>
</database>

public class PGMoneyConverter implements Converter<BigDecimal, BigDecimal> {
    @Override
    public BigDecimal from(BigDecimal databaseObject) {
        return new BigDecimal(databaseObject.toString());
    }

    @Override
    public BigDecimal to(BigDecimal userObject) {
        return userObject;
    }

    @Override
    public Class<BigDecimal> fromType() {
        return BigDecimal.class;
    }

    @Override
    public Class<BigDecimal> toType() {
        return BigDecimal.class;
    }
}

public class PgMoneyBinding implements Binding<BigDecimal, BigDecimal> {
    @Override
    public Converter<BigDecimal, BigDecimal> converter() {
        return new PGMoneyConverter();
    }

    @Override
    public void sql(BindingSQLContext<BigDecimal> ctx) throws SQLException {
        ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::money");
    }
    @Override
    public void register(BindingRegisterContext<BigDecimal> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }


    @Override
    public void set(BindingSetStatementContext<BigDecimal> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
    }


    @Override
    public void get(BindingGetResultSetContext<BigDecimal> ctx) throws SQLException {
        //value may be like '200 500.00 usd' so we must extract value
        String[] split = ctx.resultSet().getString(ctx.index()).split(" ");
        //whitespace is &nbsp; really, so we must replace \u00a0 character
        BigDecimal bigDecimal = new BigDecimal(split[0].replaceAll("\u00a0",""));
        ctx.convert(converter()).value(bigDecimal);
    }


    @Override
    public void get(BindingGetStatementContext<BigDecimal> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getBigDecimal(ctx.index()));
    }


    @Override
    public void set(BindingSetSQLOutputContext<BigDecimal> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void get(BindingGetSQLInputContext<BigDecimal> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

четверг, 4 июня 2015 г., 17:37:58 UTC+4 пользователь Lukas Eder написал:

[Lukas Eder]

Hi Oleg,

Thanks for the heads-up. Interesting bit about parsing the money when fetched from a JDBC ResultSet. Note, there's also org.postgresql.util.PGmoney, which ships with the JDBC driver and contains the following logic:

    public void setValue(String s) throws SQLException

    {

        try

        {

            String s1;

            boolean negative;

            negative = (s.charAt(0) == '(') ;

            // Remove any () (for negative) & currency symbol

            s1 = PGtokenizer.removePara(s).substring(1);

            // Strip out any , in currency

            int pos = s1.indexOf(',');

            while (pos != -1)

            {

                s1 = s1.substring(0, pos) + s1.substring(pos + 1);

                pos = s1.indexOf(',');

            }

            val = Double.parseDouble(s1);

            val = negative ? -val : val;

        }

        catch (NumberFormatException e)

        {

            throw new PSQLException(GT.tr("Conversion of money failed."), PSQLState.NUMERIC_CONSTANT_OUT_OF_RANGE, e);

        }

    }

It looks like it doesn't take into account as many special cases (e.g. &nbsp;) as your logic. On the other hand, there seems to be some interesting logic for handling negative amounts...

Cheers,
Lukas