Inclusive intervals don't work as expected in jooq and postgres

Question

When using the jooq-postgres-extension and inserting a row with a field value IntegerRange.integerRange(10, true, 20, true) in the query it is translated by cast('[10,20]' as int4range).

It's interesting that if I run the query select cast('[10,20]' as int4range) I get [10,21) which is not an inclusive interval anymore.

My problem is: when I read the row back in Jooq the integerRange.end is now 21 and not 20.

Is this a known issue and is there a workaround rather than the obvious subtracting 1 to upper boundary?

Answer 1

The jOOQ 3.17 RANGE type support (#2968) distinguishes between

• discrete ranges (e.g. DateRange, IntegerRange, LongRange, LocaldateRange)
• non-discrete ranges (e.g. BigDecimalRange, LocalDateTimeRange, OffsetDateTimeRange, TimestampRange)

Much like in PostgreSQL, jOOQ treats these as the same:

WITH r (a, b) AS (
  SELECT '[10,20]'::int4range, '[10,21)'::int4range
)  
SELECT a, b, a = b
FROM r;

The result being:

|a      |b      |?column?|
|-------|-------|--------|
|[10,21)|[10,21)|true    |

As you can see, PostgreSQL itself doesn't distinguish between the two identical ranges. While jOOQ maintains the information you give it, they're the same value in PostgreSQL. PostgreSQL itself won't echo back [10,20]::int4range to jOOQ, so you wouldn't be able to maintain this value in jOOQ.

If you need the distinction, then why not use BigDecimalRange instead, which corresponds to numrange in PostgreSQL:

WITH r (a, b) AS (
  SELECT '[10,20]'::numrange, '[10,21)'::numrange
)  
SELECT a, b, a = b
FROM r;

Now, you're getting:

|a      |b      |?column?|
|-------|-------|--------|
|[10,20]|[10,21)|false   |