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?
2
Answers
From here Range Types:
So the
cast
transforms'[10,20]'
to'[10,21)'
.You can do:
to test the upper bound for inclusivity and modify:
accordingly.
The jOOQ 3.17
RANGE
type support (#2968) distinguishes betweenDateRange
,IntegerRange
,LongRange
,LocaldateRange
)BigDecimalRange
,LocalDateTimeRange
,OffsetDateTimeRange
,TimestampRange
)Much like in PostgreSQL, jOOQ treats these as the same:
The result being:
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 tonumrange
in PostgreSQL:Now, you’re getting: