How do I insert Postgres "infinity" into a Timestamp field with JOOQ?

Question

I have a column defined like this:

expiry timestamp(0) without time zone not null

With Postgres, I can issue SQL like:

insert into my_table(expiry) values ('infinity')

I've been digging through the JOOQ doco, but couldn't find any example of dealing with this. Can I do that with JOOQ - and what would it look like?

Additionally, is it possible using an UpdatableRecord? Is there some kind of infinity "flag" instance of Timestamp I can use?

Answer 1

Ok, found a way to do it directly.

MyRecord r = db.insertInto(
  MY_RECORD, 
  MY_RECORD.ID,
  MY_RECORD.CREATED,
  MY_RECORD.EXPIRY
).values(
  val(id),
  currentTimestamp(),
  val("infinity").cast(Timestamp.class)
).returning().fetchOne();

But that feels more like a workaround than the right way to do it. Casting a string to a timestamp seems a little bit round-about to me, so I wrote a CustomField to make using it and querying easier:

public class TimestampLiteral extends CustomField<Timestamp> {
  public static final TimestampLiteral INFINITY = 
    new TimestampLiteral("'infinity'");
  public static final TimestampLiteral NEGATIVE_INFINITY = 
    new TimestampLiteral("'-infinity'");
  public static final TimestampLiteral TODAY = 
    new TimestampLiteral("'today'");

  private String literalValue;

  public TimestampLiteral(String literalValue){
    super("timestamp_literal", SQLDataType.TIMESTAMP);
    this.literalValue = literalValue;
  }

  @Override
  public void accept(Context<?> context){
    context.visit(delegate(context.configuration()));
  }

  private QueryPart delegate(Configuration configuration){
    switch( configuration.dialect().family() ){
      case POSTGRES:
        return DSL.field(literalValue);

      default:
        throw new UnsupportedOperationException(
          "Dialect not supported because I don't know how/if this works in other databases.");
    }
  }

}

Then the query is:

MyRecord r = db.insertInto(
  MY_RECORD, 
  MY_RECORD.ID,
  MY_RECORD.CREATED,
  MY_RECORD.EXPIRY
).values(
  val(id),
  TimestampLiteral.TODAY,
  TimestampLiteral.INFINITY
).returning().fetchOne();

Don't know if this is necessarily the "right" way to do this, but it seems to work for the moment.

Still interested to hear if there's a way to do this with an UpdatableRecord.