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?

Shorn · Accepted Answer · 2019-10-10T21:17:19.967

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.

score 2 · Answer 2 · answered Jun 30 '18 at 02:55

I create a java.sql.Timestamp passing org.postgresql.PGStatement.DATE_POSITIVE_INFINITY to its constructor.

create.insertInto(
  MY_RECORD, 
  MY_RECORD.ID,
  MY_RECORD.CREATED,
  MY_RECORD.EXPIRY
).values(
  1,
  new Timestamp(System.currentTimeMillis()),
  new Timestamp(PGStatement.DATE_POSITIVE_INFINITY)
).execute();

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

2 Answers2