Status of Postgres range type support?

[Adam Zell]

Hello,

I am using Postgres 11 and have a number of tables that need range boundaries.  The ranges could be implemented using two separate columns, or one column using https://www.postgresql.org/docs/11/rangetypes.html .

Reading through the following links, I am not sure what the current best practice is for using int4range, etc:

https://blog.jooq.org/2015/05/07/postgresqls-best-kept-secret-and-how-to-use-it-with-jooq/

https://github.com/jOOQ/jOOL/issues/350

https://github.com/jOOQ/jOOQ/issues/2968

https://stackoverflow.com/questions/44959037/update-a-postgres-daterange-with-jooq/44964836#44964836

What approach (assuming jOOQ 3.13.1) is recommended?

Thanks!

[Lukas Eder]

Hi Adam,

The best approach is to write your own data type binding for the various range types and attach that to your schema in your code generator configuration:

https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings/ 

Whether you use a jOOλ type in your client code, or a Guava type, or something else is up to your own preferences.

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/462ba013-0359-4e89-94ae-d5dddf0c8038%40googlegroups.com.

[Adam Zell]

Turns out I had some free time this weekend, so I implemented bindings for the six Postgres range types (int4, int8, num, date, ts and tstz).  Please see https://github.com/azell/jooq-utils/tree/postgres-ranges for the code and sample schema.

https://github.com/azell/jooq-utils/tree/postgres-ranges/types contains the various bindings and converters

https://github.com/azell/jooq-utils/tree/postgres-ranges/records contains the jOOQ codegen settings

https://github.com/azell/jooq-utils/tree/postgres-ranges/app contains the persistence logic

Code reviews welcome!  I ended up using Guava's range type as I recently implemented https://github.com/vladmihalcea/hibernate-types/pull/245 .  Also, while the jOOL range type supports unbounded endpoints, I did not see a release after the code merge: https://github.com/jOOQ/jOOL/pull/351 .

On Thursday, March 5, 2020 at 2:28:05 AM UTC-8, Lukas Eder wrote:

Hi Adam,

The best approach is to write your own data type binding for the various range types and attach that to your schema in your code generator configuration:

https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings/ 

Whether you use a jOOλ type in your client code, or a Guava type, or something else is up to your own preferences.

I hope this helps,

Lukas

On Thu, Mar 5, 2020 at 5:48 AM Adam Zell <zell...@gmail.com> wrote:

Hello,

I am using Postgres 11 and have a number of tables that need range boundaries.  The ranges could be implemented using two separate columns, or one column using https://www.postgresql.org/docs/11/rangetypes.html .

Reading through the following links, I am not sure what the current best practice is for using int4range, etc:

https://blog.jooq.org/2015/05/07/postgresqls-best-kept-secret-and-how-to-use-it-with-jooq/

https://github.com/jOOQ/jOOL/issues/350

https://github.com/jOOQ/jOOQ/issues/2968

https://stackoverflow.com/questions/44959037/update-a-postgres-daterange-with-jooq/44964836#44964836

What approach (assuming jOOQ 3.13.1) is recommended?

Thanks!

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.