CODEWITHSUNDEEP
postgresqljooq
Pedro Alipio
Pedro Alipio

Reputation: 117

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

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?

Upvotes: 2

Views: 281

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220762

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   |

Upvotes: 0

Adrian Klaver
Adrian Klaver

Reputation: 19570

From here Range Types:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [). User-defined range types can use other conventions, however.

So the cast transforms '[10,20]' to '[10,21)'.

You can do:

select upper_inc(cast('[10,20]' as int4range));
 upper_inc 
-----------
 f

to test the upper bound for inclusivity and modify:

select upper(cast('[10,20]' as int4range));
 upper 
-------
    21

accordingly.

Upvotes: 2

Related Questions