JOOQ insert with type

Question

I'm using jOOQ 3.8.4 and PostgreSQL 9.5 in a Spring 4 application. I have the following table and type definition

 CREATE DOMAIN shop.money_amount AS numeric(6,2) DEFAULT 0 NOT NULL CHECK (value > 0::numeric);

 CREATE TYPE shop.money AS (
   m_amount shop.money_amount,
   m_currency shop.currency,
   m_country shop.site_country
 );

 CREATE TYPE shop.money_mapping AS (
   mm_moneys shop.money []
 );

 CREATE TABLE shop.article
 (
   a_id bigserial NOT NULL,
   a_price shop.money_mapping NOT NULL,
   CONSTRAINT a_pk_id PRIMARY KEY (a_id)
 );

Then I tried an insert using jOOQ, i.e.:

MoneyMappingRecord priceMoneyMapping = new MoneyMappingRecord();
priceMoneyMapping.setMoneys(new MoneyRecord[]{
        new MoneyRecord().setAmount(new BigDecimal("11")).setCountry(SiteCountry.US).setCurrency(Currency.USD),
        new MoneyRecord().setAmount(new BigDecimal("14")).setCountry(SiteCountry.DE).setCurrency(Currency.EUR)
});

dsl.insertInto(ARTICLE)
        .set(ARTICLE.A_PRICE, priceMoneyMapping)
        .returning(ARTICLE.A_ID).fetchOne().getId();

Then I get:

Caused by: org.springframework.jdbc.BadSqlGrammarException: jOOQ; 
  bad SQL grammar [insert into "shop"."article" ("a_price") values
  (row(?::money[])) returning "shop"."article"."a_id"]; nested 
  exception is org.postgresql.util.PSQLException: 
  ERROR: cannot cast type record to shop.money_mapping
  Detail: Cannot cast type money[] to shop.money[] in column 1.

What am I doing wrong?

UPDATE

I tried to rename the shop.money type as suggested by Lukas (from shop.money to shop.localized_money), but I believe that the problem is related to the schema. See the updated error.

Caused by: org.springframework.jdbc.BadSqlGrammarException: 
   jOOQ; bad SQL grammar [insert into "shop"."article" ("a_price") 
   values (row(?::localized_money[])) returning "shop"."article"."a_id"]; nested exception is    
   org.postgresql.util.PSQLException: ERROR: type "localized_money[]" does not exist

Maybe the type in type is an issue!

Hmm, that looks like a bug. I'm not sure why the cast is not fully qualified. The problem is that PostgreSQL has a built-in `money` type, which is probably preferred here over your own custom type. Does it work if you rename your type to `my_money` or something similar? — Lukas Eder, Sep 23 '16 at 12:11
@LukasEder I tried, but nothing changed. It is not because of the money! — mat_boy, Sep 23 '16 at 17:23
@LukasEder please, have a look at my updated answer. I believe that it is because the translated query is missing the schema. — mat_boy, Sep 23 '16 at 20:14
Oh, I see, thanks for the update. I suspect you're not using the `shop` schema as your default schema or as a schema on your search path then, right? I'm afraid that might be the only workaround right now. I hope I will be able to further investigate this by the end of the week — Lukas Eder, Sep 26 '16 at 15:19
I do not quite understand. Should I set `shop` as a default schema in jOOQ? Can you show me an example or address me to the docs? — mat_boy, Sep 26 '16 at 17:31
You could do it in jOOQ or in your data source configuration, etc. Here's what I mean: http://stackoverflow.com/q/2875610/521799 — Lukas Eder, Sep 26 '16 at 20:52
Thanks for the update. I'll provide an answer and I've created an issue to fix this: https://github.com/jOOQ/jOOQ/issues/5571 — Lukas Eder, Sep 27 '16 at 11:09

score 1 · Accepted Answer · edited May 23 '17 at 11:48

There seems to be a problem related to casting of nested array of types in jOOQ 3.8. I've created an issue for this: https://github.com/jOOQ/jOOQ/issues/5571

The problem is that your custom type array type needs to be fully qualified when bound as a bind variable. If it isn't fully qualified, then the type isn't found by PostgreSQL. One possible workaround is described in this question: Permanently Set Postgresql Schema Path

You can add the shop schema on your user's search path:

ALTER ROLE <your_login_role> SET search_path TO shop;

... which means that elements inside of the shop schema no longer need to be fully qualified. Which can also be a bad thing, so be careful! :)

JOOQ insert with type

1 Answers1