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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

PostgreSQL

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:

<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. 
               Import the others manually from your distribution -->
    <groupId>org.jooq</groupId>
    <artifactId>jooq-postgres-extensions</artifactId>
    <version>3.19.0-SNAPSHOT</version>
</dependency>

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

<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 andprogrammatic code generation for more details.

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!

Tell us a little bit about yourself and join our newsletter (examples)

PostgreSQL

Feedback

Community

Support

Legal

Documentation