previous : next

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15

PostgreSQL extensions

Applies to ✅ 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:

  • cidr is for IPv4 and IPv6 networks.
  • citext is "case insensitive text".
  • daterange is a date range type. Depending on the javaTimeTypes configuration, this translates to java.sql.Date or java.time.LocalDate ranges.
  • hstore is a key-value store (i.e. a Map<String, String>).
  • inet is for IPv4 and IPv6 hosts and networks.
  • int4range is a 32 bit integer range type.
  • int8range is a 64 bit integer range type.
  • ltree is a label tree data structure.
  • numrange is a numeric range type.
  • tsrange is a timestamp range type. Depending on the javaTimeTypes configuration, this translates to java.sql.Timestamp or java.time.LocalDateTime ranges.
  • tstzrange 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.22</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.22")
}
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.22"
}

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.


previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo