PostgreSQL extensions
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The jooq-postgres-extensions module contains data types, converters, and bindings for the following data types:
- The cidr type is for IPv4 and IPv6 networks.
- The citext type is "case insensitive text".
- The daterange type is a date range type. Depending on the javaTimeTypes configuration, this translates to
java.sql.Dateorjava.time.LocalDateranges. - The hstore type is a key-value store (i.e. a
Map<String, String>). - The inet type is for IPv4 and IPv6 hosts and networks.
- The int4range type is a 32 bit integer range type.
- The int8range type is a 64 bit integer range type.
- The ltree type is a label tree data structure.
- The numrange type is a numeric range type.
- The tsrange type is a timestamp range type. Depending on the javaTimeTypes configuration, this translates to
java.sql.Timestamporjava.time.LocalDateTimeranges. - The tstzrange type is a timestamptz range type.
In order to access these data types, just add the following dependency to your project:
Maven
Gradle (Kotlin)
Gradle (Groovy)
<dependency>
<!-- Use org.jooq for the Open Source Edition
org.jooq.pro for commercial editions with Java 17 support,
org.jooq.pro-java-11 for commercial editions with Java 11 support,
org.jooq.pro-java-8 for commercial editions with Java 8 support,
org.jooq.trial for the free trial edition with Java 17 support,
org.jooq.trial-java-11 for the free trial edition with Java 11 support,
org.jooq.trial-java-8 for the free trial edition with Java 8 support
Note: Only the Open Source Edition is hosted on Maven Central.
Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk -->
<groupId>org.jooq</groupId>
<artifactId>jooq-postgres-extensions</artifactId>
<version>3.18.34</version>
</dependency>
dependencies {
// Use org.jooq for the Open Source Edition
// org.jooq.pro for commercial editions with Java 17 support,
// org.jooq.pro-java-11 for commercial editions with Java 11 support,
// org.jooq.pro-java-8 for commercial editions with Java 8 support,
// org.jooq.trial for the free trial edition with Java 17 support,
// org.jooq.trial-java-11 for the free trial edition with Java 11 support,
// org.jooq.trial-java-8 for the free trial edition with Java 8 support
//
// Note: Only the Open Source Edition is hosted on Maven Central.
// Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
// See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk
implementation("org.jooq:jooq-postgres-extensions:3.18.34")
}
dependencies {
// Use org.jooq for the Open Source Edition
// org.jooq.pro for commercial editions with Java 17 support,
// org.jooq.pro-java-11 for commercial editions with Java 11 support,
// org.jooq.pro-java-8 for commercial editions with Java 8 support,
// org.jooq.trial for the free trial edition with Java 17 support,
// org.jooq.trial-java-11 for the free trial edition with Java 11 support,
// org.jooq.trial-java-8 for the free trial edition with Java 8 support
//
// Note: Only the Open Source Edition is hosted on Maven Central.
// Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
// See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk
implementation "org.jooq:jooq-postgres-extensions:3.18.34"
}
Starting with jOOQ 3.17, the code generator will auto-register the following forced types, if it finds the jooq-postgres-extensions module on the classpath. These types are registered with lowest priority, such that custom forced types will take precedence:
XML (standalone and maven)
Programmatic
Gradle (Kotlin)
Gradle (Groovy)
Gradle (third party)
<configuration>
<generator>
<database>
<forcedTypes>
<forcedType>
<userType>org.jooq.postgres.extensions.types.BigDecimalRange</userType>
<binding>org.jooq.postgres.extensions.bindings.BigDecimalRangeBinding</binding>
<includeTypes>numrange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.BigDecimalRange[]</userType>
<binding>org.jooq.postgres.extensions.bindings.BigDecimalRangeArrayBinding</binding>
<includeTypes>_numrange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Cidr</userType>
<binding>org.jooq.postgres.extensions.bindings.CidrBinding</binding>
<includeTypes>cidr</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Cidr[]</userType>
<binding>org.jooq.postgres.extensions.bindings.CidrArrayBinding</binding>
<includeTypes>_cidr</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>java.lang.String</userType>
<binding>org.jooq.postgres.extensions.bindings.CitextBinding</binding>
<includeTypes>citext</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>java.lang.String[]</userType>
<binding>org.jooq.postgres.extensions.bindings.CitextArrayBinding</binding>
<includeTypes>_citext</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Hstore</userType>
<binding>org.jooq.postgres.extensions.bindings.HstoreBinding</binding>
<includeTypes>hstore</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Hstore[]</userType>
<binding>org.jooq.postgres.extensions.bindings.HstoreArrayBinding</binding>
<includeTypes>_hstore</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Inet</userType>
<binding>org.jooq.postgres.extensions.bindings.InetBinding</binding>
<includeTypes>inet</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Inet[]</userType>
<binding>org.jooq.postgres.extensions.bindings.InetArrayBinding</binding>
<includeTypes>_inet</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.IntegerRange</userType>
<binding>org.jooq.postgres.extensions.bindings.IntegerRangeBinding</binding>
<includeTypes>int4range</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.IntegerRange[]</userType>
<binding>org.jooq.postgres.extensions.bindings.IntegerRangeArrayBinding</binding>
<includeTypes>_int4range</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<!-- Depending on <javaTimeTypes/>, this may map to DateRange instead -->
<forcedType>
<userType>org.jooq.postgres.extensions.types.LocalDateRange</userType>
<binding>org.jooq.postgres.extensions.bindings.LocalDateRangeBinding</binding>
<includeTypes>daterange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<!-- Depending on <javaTimeTypes/>, this may map to DateRange[] instead -->
<forcedType>
<userType>org.jooq.postgres.extensions.types.LocalDateRange[]</userType>
<binding>org.jooq.postgres.extensions.bindings.LocalDateRangeArrayBinding</binding>
<includeTypes>_daterange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<!-- Depending on <javaTimeTypes/>, this may map to TimestampRange instead -->
<forcedType>
<userType>org.jooq.postgres.extensions.types.LocalDateTimeRange</userType>
<binding>org.jooq.postgres.extensions.bindings.LocalDateTimeRangeBinding</binding>
<includeTypes>tsrange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<!-- Depending on <javaTimeTypes/>, this may map to TimestampRange[] instead -->
<forcedType>
<userType>org.jooq.postgres.extensions.types.LocalDateTimeRange[]</userType>
<binding>org.jooq.postgres.extensions.bindings.LocalDateTimeRangeArrayBinding</binding>
<includeTypes>_tsrange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.LongRange</userType>
<binding>org.jooq.postgres.extensions.bindings.LongRangeBinding</binding>
<includeTypes>int8range</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.LongRange[]</userType>
<binding>org.jooq.postgres.extensions.bindings.LongRangeArrayBinding</binding>
<includeTypes>_int8range</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Ltree</userType>
<binding>org.jooq.postgres.extensions.bindings.LtreeBinding</binding>
<includeTypes>ltree</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.Ltree[]</userType>
<binding>org.jooq.postgres.extensions.bindings.LtreeArrayBinding</binding>
<includeTypes>_ltree</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.OffsetDateTimeRange</userType>
<binding>org.jooq.postgres.extensions.bindings.OffsetDateTimeRangeBinding</binding>
<includeTypes>tstzrange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
<forcedType>
<userType>org.jooq.postgres.extensions.types.OffsetDateTimeRange[]</userType>
<binding>org.jooq.postgres.extensions.bindings.OffsetDateTimeRangeArrayBinding</binding>
<includeTypes>_tstzrange</includeTypes>
<priority>-2147483648</priority>
</forcedType>
</forcedTypes>
</database>
</generator>
</configuration>
See the configuration XSD, standalone code generation, and maven code generation for more details.
new org.jooq.meta.jaxb.Configuration()
.withGenerator(new Generator()
.withDatabase(new Database()
.withForcedTypes(
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.BigDecimalRange")
.withBinding("org.jooq.postgres.extensions.bindings.BigDecimalRangeBinding")
.withIncludeTypes("numrange")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.BigDecimalRange[]")
.withBinding("org.jooq.postgres.extensions.bindings.BigDecimalRangeArrayBinding")
.withIncludeTypes("_numrange")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Cidr")
.withBinding("org.jooq.postgres.extensions.bindings.CidrBinding")
.withIncludeTypes("cidr")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Cidr[]")
.withBinding("org.jooq.postgres.extensions.bindings.CidrArrayBinding")
.withIncludeTypes("_cidr")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("java.lang.String")
.withBinding("org.jooq.postgres.extensions.bindings.CitextBinding")
.withIncludeTypes("citext")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("java.lang.String[]")
.withBinding("org.jooq.postgres.extensions.bindings.CitextArrayBinding")
.withIncludeTypes("_citext")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Hstore")
.withBinding("org.jooq.postgres.extensions.bindings.HstoreBinding")
.withIncludeTypes("hstore")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Hstore[]")
.withBinding("org.jooq.postgres.extensions.bindings.HstoreArrayBinding")
.withIncludeTypes("_hstore")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Inet")
.withBinding("org.jooq.postgres.extensions.bindings.InetBinding")
.withIncludeTypes("inet")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Inet[]")
.withBinding("org.jooq.postgres.extensions.bindings.InetArrayBinding")
.withIncludeTypes("_inet")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.IntegerRange")
.withBinding("org.jooq.postgres.extensions.bindings.IntegerRangeBinding")
.withIncludeTypes("int4range")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.IntegerRange[]")
.withBinding("org.jooq.postgres.extensions.bindings.IntegerRangeArrayBinding")
.withIncludeTypes("_int4range")
.withPriority("-2147483648"),
// Depending on <javaTimeTypes/>, this may map to DateRange instead
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.LocalDateRange")
.withBinding("org.jooq.postgres.extensions.bindings.LocalDateRangeBinding")
.withIncludeTypes("daterange")
.withPriority("-2147483648"),
// Depending on <javaTimeTypes/>, this may map to DateRange[] instead
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.LocalDateRange[]")
.withBinding("org.jooq.postgres.extensions.bindings.LocalDateRangeArrayBinding")
.withIncludeTypes("_daterange")
.withPriority("-2147483648"),
// Depending on <javaTimeTypes/>, this may map to TimestampRange instead
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.LocalDateTimeRange")
.withBinding("org.jooq.postgres.extensions.bindings.LocalDateTimeRangeBinding")
.withIncludeTypes("tsrange")
.withPriority("-2147483648"),
// Depending on <javaTimeTypes/>, this may map to TimestampRange[] instead
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.LocalDateTimeRange[]")
.withBinding("org.jooq.postgres.extensions.bindings.LocalDateTimeRangeArrayBinding")
.withIncludeTypes("_tsrange")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.LongRange")
.withBinding("org.jooq.postgres.extensions.bindings.LongRangeBinding")
.withIncludeTypes("int8range")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.LongRange[]")
.withBinding("org.jooq.postgres.extensions.bindings.LongRangeArrayBinding")
.withIncludeTypes("_int8range")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Ltree")
.withBinding("org.jooq.postgres.extensions.bindings.LtreeBinding")
.withIncludeTypes("ltree")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.Ltree[]")
.withBinding("org.jooq.postgres.extensions.bindings.LtreeArrayBinding")
.withIncludeTypes("_ltree")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.OffsetDateTimeRange")
.withBinding("org.jooq.postgres.extensions.bindings.OffsetDateTimeRangeBinding")
.withIncludeTypes("tstzrange")
.withPriority("-2147483648"),
new ForcedType()
.withUserType("org.jooq.postgres.extensions.types.OffsetDateTimeRange[]")
.withBinding("org.jooq.postgres.extensions.bindings.OffsetDateTimeRangeArrayBinding")
.withIncludeTypes("_tstzrange")
.withPriority("-2147483648")
)
)
)
See the configuration XSD and programmatic code generation for more details.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
generationTool {
generator {
database {
forcedTypes {
forcedType {
userType = "org.jooq.postgres.extensions.types.BigDecimalRange"
binding = "org.jooq.postgres.extensions.bindings.BigDecimalRangeBinding"
includeTypes = "numrange"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.BigDecimalRange[]"
binding = "org.jooq.postgres.extensions.bindings.BigDecimalRangeArrayBinding"
includeTypes = "_numrange"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Cidr"
binding = "org.jooq.postgres.extensions.bindings.CidrBinding"
includeTypes = "cidr"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Cidr[]"
binding = "org.jooq.postgres.extensions.bindings.CidrArrayBinding"
includeTypes = "_cidr"
priority = "-2147483648"
}
forcedType {
userType = "java.lang.String"
binding = "org.jooq.postgres.extensions.bindings.CitextBinding"
includeTypes = "citext"
priority = "-2147483648"
}
forcedType {
userType = "java.lang.String[]"
binding = "org.jooq.postgres.extensions.bindings.CitextArrayBinding"
includeTypes = "_citext"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Hstore"
binding = "org.jooq.postgres.extensions.bindings.HstoreBinding"
includeTypes = "hstore"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Hstore[]"
binding = "org.jooq.postgres.extensions.bindings.HstoreArrayBinding"
includeTypes = "_hstore"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Inet"
binding = "org.jooq.postgres.extensions.bindings.InetBinding"
includeTypes = "inet"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Inet[]"
binding = "org.jooq.postgres.extensions.bindings.InetArrayBinding"
includeTypes = "_inet"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.IntegerRange"
binding = "org.jooq.postgres.extensions.bindings.IntegerRangeBinding"
includeTypes = "int4range"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.IntegerRange[]"
binding = "org.jooq.postgres.extensions.bindings.IntegerRangeArrayBinding"
includeTypes = "_int4range"
priority = "-2147483648"
}
// Depending on <javaTimeTypes/>, this may map to DateRange instead
forcedType {
userType = "org.jooq.postgres.extensions.types.LocalDateRange"
binding = "org.jooq.postgres.extensions.bindings.LocalDateRangeBinding"
includeTypes = "daterange"
priority = "-2147483648"
}
// Depending on <javaTimeTypes/>, this may map to DateRange[] instead
forcedType {
userType = "org.jooq.postgres.extensions.types.LocalDateRange[]"
binding = "org.jooq.postgres.extensions.bindings.LocalDateRangeArrayBinding"
includeTypes = "_daterange"
priority = "-2147483648"
}
// Depending on <javaTimeTypes/>, this may map to TimestampRange instead
forcedType {
userType = "org.jooq.postgres.extensions.types.LocalDateTimeRange"
binding = "org.jooq.postgres.extensions.bindings.LocalDateTimeRangeBinding"
includeTypes = "tsrange"
priority = "-2147483648"
}
// Depending on <javaTimeTypes/>, this may map to TimestampRange[] instead
forcedType {
userType = "org.jooq.postgres.extensions.types.LocalDateTimeRange[]"
binding = "org.jooq.postgres.extensions.bindings.LocalDateTimeRangeArrayBinding"
includeTypes = "_tsrange"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.LongRange"
binding = "org.jooq.postgres.extensions.bindings.LongRangeBinding"
includeTypes = "int8range"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.LongRange[]"
binding = "org.jooq.postgres.extensions.bindings.LongRangeArrayBinding"
includeTypes = "_int8range"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Ltree"
binding = "org.jooq.postgres.extensions.bindings.LtreeBinding"
includeTypes = "ltree"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.Ltree[]"
binding = "org.jooq.postgres.extensions.bindings.LtreeArrayBinding"
includeTypes = "_ltree"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.OffsetDateTimeRange"
binding = "org.jooq.postgres.extensions.bindings.OffsetDateTimeRangeBinding"
includeTypes = "tstzrange"
priority = "-2147483648"
}
forcedType {
userType = "org.jooq.postgres.extensions.types.OffsetDateTimeRange[]"
binding = "org.jooq.postgres.extensions.bindings.OffsetDateTimeRangeArrayBinding"
includeTypes = "_tstzrange"
priority = "-2147483648"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
Feedback
Do you have any feedback about this page? We'd love to hear it!