postgresql - Automatically casting RHS values for specific JOOQ columns -

i have following code, "ci_col" of type citext (https://www.postgresql.org/docs/current/static/citext.html)

string str = "testing";  int countbad = context.fetchcount(select(tables.my_table.ci_col)   .from(tables.my_table)   .where(tables.my_table.ci_col.eq(str)));  int countgood = context.fetchcount(select(tables.my_table.ci_col)   .from(tables.my_table)   .where(tables.my_table.ci_col.eq(cast(str, new defaultdatatype<>(sqldialect.postgres, string.class, "citext"))))); 

the first query returns 0, , second query correctly returns > 0.

it took me long time track down root cause, because when first query printed (or found in debug logging), seemed execute in terminal fine.

once got down statement level , started binding values, that's root cause seemed be. seems issue (or on purpose) in postgres driver. post illustrates binding issue citext: https://www.postgresql.org/message-id/cajfs0qb90bo0vww5pzcw0c%3dljocox04qpem4nsd6uy7-t2r5ha%40mail.gmail.com

is possible fix @ jooq level, having jooq automatically perform cast on right hand side values specific column?

side note

new defaultdatatype<>(...) 

ghasp! you're using internal api :)

correct solution

the correct way introduce new data types in jooq use converter, or in case data type binding:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

while binding method implementations delegate jooq's defaultbinding, can override sql() method (which generates bind variable's sql string) this:

@override public void sql(bindingsqlcontext<jsonelement> ctx) throws sqlexception {     ctx.render().sql("?::citext"); }