Jooq: Add support for PostgreSQL RANGE types

[lukaseder]

PostgreSQL supports vendor-specific RANGE types. It would be interesting to natively support them as well in jOOQ:

---

For details, see:

• http://www.postgresql.org/docs/9.2/static/rangetypes.html
• http://stackoverflow.com/q/21238441/521799

[Brzhk]

Would love to help implementing this. I honestly believe it could be some killer feature. where can i start looking ? :)

[lukaseder]

Thanks for the offer!

I honestly believe it could be some killer feature.

I thought so, myself. So far, I was only aware of the SQL standard OVERLAPS predicate, which is already supported by jOOQ:
http://www.jooq.org/doc/3.2/manual/sql-building/conditional-expressions/overlaps-predicate/

True RANGE type support is something entirely else, of course.

The best way to help in the first place is by providing links to comprehensive documentation. E.g. what we'd need to know is how to interact with such types on a JDBC level, i.e. how much help can PostgreSQL JDBC offer us here. I can then think about how this could be integrated in jOOQ. I suspect that the existing Row2 type won't work, as ranges can be inclusive / exclusive at both bounds. Probably, we should think about introducing a new <T> Range<T> DSL.range(T, T) constructor and think about how to emulate ranges in other SQL dialects. While emulation is not a priority, I would still like to construct an API that can be emulated later on.

So, as you can see, there are lots of areas where you could help even before writing the first line of code :-)

[lukaseder]

Here's an interesting listing of functions returning %range% types or taking %range% type arguments:

select 
  r.routine_name || '(' ||
  string_agg(p.data_type::varchar, ', ') || ') : ' ||
  r.data_type as "function"
from information_schema.routines r
join information_schema.parameters p
using (specific_catalog, specific_schema, specific_name)
where (specific_catalog, specific_schema, specific_name) in (
  select specific_catalog, specific_schema, specific_name 
  from information_schema.parameters
  where data_type like '%range%'
)
or r.data_type like '%range%'
group by 
  r.data_type,
  r.routine_name,
  specific_catalog, 
  specific_schema, 
  specific_name
order by r.routine_name

The above yields (on PostgreSQL 9.3):

anyrange_in(oid, integer, cstring) : anyrange
anyrange_out(anyrange) : cstring
daterange(date, date) : daterange
daterange(date, text, date) : daterange
daterange_canonical(daterange) : daterange
elem_contained_by_range(anyrange, anyelement) : boolean
hash_range(anyrange) : integer
int4range(integer, integer) : int4range
int4range(integer, integer, text) : int4range
int4range_canonical(int4range) : int4range
int8range(bigint, bigint) : int8range
int8range(text, bigint, bigint) : int8range
int8range_canonical(int8range) : int8range
isempty(anyrange) : boolean
lower(anyrange) : anyelement
lower_inc(anyrange) : boolean
lower_inf(anyrange) : boolean
numrange(numeric, numeric) : numrange
numrange(numeric, text, numeric) : numrange
range_adjacent(anyrange, anyrange) : boolean
range_after(anyrange, anyrange) : boolean
range_before(anyrange, anyrange) : boolean
range_cmp(anyrange, anyrange) : integer
range_contained_by(anyrange, anyrange) : boolean
range_contains(anyrange, anyrange) : boolean
range_contains_elem(anyelement, anyrange) : boolean
range_eq(anyrange, anyrange) : boolean
range_ge(anyrange, anyrange) : boolean
range_gist_consistent(integer, anyrange, internal, internal, oid) : boolean
range_gist_same(anyrange, anyrange, internal) : internal
range_gt(anyrange, anyrange) : boolean
range_in(integer, oid, cstring) : anyrange
range_intersect(anyrange, anyrange) : anyrange
range_le(anyrange, anyrange) : boolean
range_lt(anyrange, anyrange) : boolean
range_minus(anyrange, anyrange) : anyrange
range_ne(anyrange, anyrange) : boolean
range_out(anyrange) : cstring
range_overlaps(anyrange, anyrange) : boolean
range_overleft(anyrange, anyrange) : boolean
range_overright(anyrange, anyrange) : boolean
range_recv(integer, oid, internal) : anyrange
range_send(anyrange) : bytea
range_union(anyrange, anyrange) : anyrange
tsrange(timestamp without time zone, timestamp without time zone) : tsrange
tsrange(text, timestamp without time zone, timestamp without time zone) : tsrange
tstzrange(timestamp with time zone, timestamp with time zone) : tstzrange
tstzrange(timestamp with time zone, timestamp with time zone, text) : tstzrange
upper(anyrange) : anyelement
upper_inc(anyrange) : boolean
upper_inf(anyrange) : boolean

[Brzhk]

I've looked into the JDBC sources and found nothing mentionning ranges
anywhere, but i'm gonna look around their doc as well.

On Mon, Jan 20, 2014 at 8:55 PM, Lukas Eder [email protected]:

Here's an interesting listing of functions returning %range% types or
taking %range% type arguments:

select
r.routine_name || '(' ||
string_agg(p.data_type::varchar, ', ') || ') : ' ||
r.data_type as "function"from information_schema.routines rjoin information_schema.parameters pusing (specific_catalog, specific_schema, specific_name)where (specific_catalog, specific_schema, specific_name) in (
select specific_catalog, specific_schema, specific_name
from information_schema.parameters
where data_type like '%range%')or r.data_type like '%range%'group by
r.data_type,
r.routine_name,
specific_catalog,
specific_schema,
specific_nameorder by r.routine_name

The above yields (on PostgreSQL 9.3):

anyrange_in(oid, integer, cstring) : anyrange
anyrange_out(anyrange) : cstring
daterange(date, date) : daterange
daterange(date, text, date) : daterange
daterange_canonical(daterange) : daterange
elem_contained_by_range(anyrange, anyelement) : boolean
hash_range(anyrange) : integer
int4range(integer, integer) : int4range
int4range(integer, integer, text) : int4range
int4range_canonical(int4range) : int4range
int8range(bigint, bigint) : int8range
int8range(text, bigint, bigint) : int8range
int8range_canonical(int8range) : int8range
isempty(anyrange) : boolean
lower(anyrange) : anyelement
lower_inc(anyrange) : boolean
lower_inf(anyrange) : boolean
numrange(numeric, numeric) : numrange
numrange(numeric, text, numeric) : numrange
range_adjacent(anyrange, anyrange) : boolean
range_after(anyrange, anyrange) : boolean
range_before(anyrange, anyrange) : boolean
range_cmp(anyrange, anyrange) : integer
range_contained_by(anyrange, anyrange) : boolean
range_contains(anyrange, anyrange) : boolean
range_contains_elem(anyelement, anyrange) : boolean
range_eq(anyrange, anyrange) : boolean
range_ge(anyrange, anyrange) : boolean
range_gist_consistent(integer, anyrange, internal, internal, oid) : boolean
range_gist_same(anyrange, anyrange, internal) : internal
range_gt(anyrange, anyrange) : boolean
range_in(integer, oid, cstring) : anyrange
range_intersect(anyrange, anyrange) : anyrange
range_le(anyrange, anyrange) : boolean
range_lt(anyrange, anyrange) : boolean
range_minus(anyrange, anyrange) : anyrange
range_ne(anyrange, anyrange) : boolean
range_out(anyrange) : cstring
range_overlaps(anyrange, anyrange) : boolean
range_overleft(anyrange, anyrange) : boolean
range_overright(anyrange, anyrange) : boolean
range_recv(integer, oid, internal) : anyrange
range_send(anyrange) : bytea
range_union(anyrange, anyrange) : anyrange
tsrange(timestamp without time zone, timestamp without time zone) : tsrange
tsrange(text, timestamp without time zone, timestamp without time zone) : tsrange
tstzrange(timestamp with time zone, timestamp with time zone) : tstzrange
tstzrange(timestamp with time zone, timestamp with time zone, text) : tstzrange
upper(anyrange) : anyelement
upper_inc(anyrange) : boolean
upper_inf(anyrange) : boolean

—
Reply to this email directly or view it on GitHubhttps://github.com/jOOQ/jOOQ/issues/2968#issuecomment-32791589
.

[Brzhk]

I haven't found anything in their doc, nor in their wiki. I've asked in their IRC channel, and sent an email to the postgresql-jdbc mailing list. So far, unanswered. It sure feels like everything is still to be done !

[lukaseder]

It sure feels like everything is still to be done !

Yes, that is - unfortunately - often the case with PostgreSQL JDBC. I think the database is just way too advanced for the JDBC API

[lukaseder]

... anyway, if you're still interested in helping, some experiments with JDBC (or links to relevant blog posts) would still be very helpful. I'm sure something can be worked out here, even with poor JDBC driver support. We've worked around quite a few issues related to UDT serialisation / deserialisation in the past.

[Brzhk]

I will. i've encountered too many issues with time ranges to avoid this
one. And i'm too lazy to bother with theses issues next time.

On Wed, Jan 22, 2014 at 2:11 PM, Lukas Eder [email protected]:

... anyway, if you're still interested in helping, some experiments with
JDBC (or links to relevant blog posts) would still be very helpful. I'm
sure something can be worked out here, even with poor JDBC driver support.
We've worked around quite a few issues related to UDT serialisation /
deserialisation in the past.

—
Reply to this email directly or view it on GitHubhttps://github.com/jOOQ/jOOQ/issues/2968#issuecomment-33020033
.

[lukaseder]

But for time ranges, does the OVERLAPS predicate already cover some of your use-cases, maybe?

[lukaseder]

@Brzhk : A bit late for you, perhaps, but I've updated the Stack Overflow question with a solution using jOOQ 3.5 org.jooq.Binding:
http://stackoverflow.com/a/21241417/521799

Range type support will almost work out of the box, then

[alexpdp7]

Couldn't one do something hackish to do this without JDBC support using a subquery with bound parameters?

Suppose I had:

Date from = ...;
Date to = ...;

and I wanted to do the following pseudo-SQL:

insert into foo(bar) values tstzrange(from, to);

couldn't I create a select statement:

var subquery = dslContext.select('tstzrange(:from, :to)').bind('from', from).bind('to', to)

and then:

dslContext.insertInto('foo', 'bar').values(subquery);

Wouldn't that work even if the JDBC driver support? Is there any way to make it work with some fiddling? I have been unable to :( [actually I would like to use this with insertInto and update's set(Map).

[lukaseder]

@alexpdp7 : Thank you very much for your suggestion. I don't think you'll need a subquery for this. A plain SQL Field will be much simpler:

DSL.field("tstzrange(?, ?)", Object.class, from, to);

Or, as suggested earlier, using a data type binding that takes care of binding a Java range abstraction to a single value.

[alexpdp7]

Cool, that works! Gotta learn more jOOQ

[deinspanjer]

I am diving into JOOQ on a project, and I had the need for the json and range bindings, so I wrote implementations of them. I would be willing to contribute the code if someone experienced with committing to JOOQ might be interested in collaborating to see about adding it to the Postgres dialect.

The only thing of note is they are sorta Google-centric. Using the Range class from guava and the Gson JsonElement type for JSON and JSONB.

[lightoze]

@deinspanjer, because of extra dependencies, it probably will be more suitable to create a small separate project (or two) and deploy it to Maven Central. Email me if you need help with that. I have some json/range bindings implemented too.

[deinspanjer]

I figured that might be the case, but my Maven-foo isn't high enough to be sure what to do to make sure it could be imported and used in conjunction with the jooq packages.

[lukaseder]

I agree with @lightoze. While we are planning on adding an extensions project for these things (https://github.com/jOOQ/jOOQ/issues/5507), we're going to be a bit reluctant of adding "random" dependencies to it. Any Range or Json implementation would do the job and it wouldn't be "jOOQ-style" to pick one over the others.

When we started publishing to Maven Central, I've found the official guide from Sonatype quite useful: http://central.sonatype.org/pages/ossrh-guide.html

[lukaseder]

We'll look into this again via https://github.com/jOOQ/jOOQ/issues/5507, in jOOQ 3.15.