jOOQ Postgres PERCENTILE_CONT & MEDIAN Issue with Type Casting

Question

Coercion of data types does not seem to work within median() or percentileCont(). Data type coercion works just fine with other aggregate functions like max() and min(). The Postgres queries that are produced as a result show that type casting is not applied in the final result. Below are the snippets from jOOQ and Postgres for reference. As of now, I have no work-around or knowledge of an open ticket for this issue.

Any direction would be much appreciated!

---

MEDIAN

jOOQ Snippet

selectFields.add(
    median(
            field(String.format("%s.%s", a.getDataSourceName(), a.getField()))
                .coerce(Double.class)) // Seems to not successfully coerce data types
        .as(
            String.format(
                "%s.%s.%s", a.getDataSourceName(), a.getField(), "median")));

SQL Output

select 
  tableA.columnA, 
  percentile_cont(0.5) within group (order by tableA.columnA) as "tableA.columnA.median" 
from tableA
group by tableA.columnA 
limit 100;

ERROR: function percentile_cont(numeric, text) does not exist

---

PERCENTILE_CONT

jOOQ Snippet

selectFields.add(
    percentileCont(a.getPercentileValue())
        .withinGroupOrderBy(
            field(String.format("%s.%s", a.getDataSourceName(), a.getField()))
                .coerce(Double.class)) // Seems to not successfully coerce data types
        .as(
            String.format(
                "%s.%s.%s", a.getDataSourceName(), a.getField(), "percentile_" + Math.round(a.getPercentileValue() * 100))));

SQL Output

select 
  tableA.columnA, 
  percentile_cont(0.0) within group (order by tableA.columnA) as "tableA.columnA.percentile_0" 
from tableA.columnA
group by tableA.columnA
limit 100;

ERROR: function percentile_cont(numeric, text) does not exist

---

POSTGRES -- This works due to type casting

select 
    percentile_cont(0.5)
    within group (
        order by tableA.columnA::INTEGER
    )
    as "tableA.columnA.median" 
from tableA.columnA 
group by (select 1)

https://www.jooq.org/javadoc/latest/org.jooq/module-summary.html

Answer 1

You're not looking for coercion, which in jOOQ-speak means changing a data type only in the client without letting the server know. This is mostly useful when fetching data of some type (e.g. Integer) despite jOOQ producing some other data type (e.g. BigInteger), otherwise. See the Javadoc on Field.coerce()

Unlike with casting, coercing doesn't affect the way the database sees a Field's type.

// This binds an int value to a JDBC PreparedStatement
DSL.val(1).coerce(String.class);

// This binds an int value to a JDBC PreparedStatement
// and casts it to VARCHAR in SQL
DSL.val(1).cast(String.class);

Cleary, you want to Field.cast(), instead, just like in your example where you actually used a cast tableA.columnA::INTEGER.