JOOQ: How to use postgres generate_series and insert into table?

Question

I am trying to write this PostgreSQL query using JOOQ:

INSERT INTO my_table (date, from_city, to_city, rate_per_mile)
select g.dt::date, 'city1', 'city2', 13.12
from generate_series(current_date, current_date + 364, interval '1 day') as g(dt);

I am a bit new to JOOQ, saw here a part of what I am looking for, and I came with this:

var advanceDates = DSL.table("generate_series({0}, {0} + 364, interval '1 day')",
    currencyConversion.getDate()).as("dates");

dslContext.insertInto(CURRENCY_CONVERSION).values(
  dslContext
    .select(
        advanceDates.field(0),
        val("city1"),
        val("city2"),
        val(13.12))
    .from(advanceDates));

But I think advanceDates.field(0) is not the correct usage, and it's returning null.

How do I form this query in JOOQ?

score 2 · Accepted Answer · answered May 10 '21 at 18:40

You cannot dereference a field from a plain SQL table by index, because the jOOQ runtime model does not know any of the fields of that table. I suspect the problem here is that you don't know the name either, as it's a table valued function and you've not named the field explicitly. How about naming it explicity:

var advanceDates = DSL.table("generate_series({0}, {0} + 364, interval '1 day')",
    currencyConversion.getDate()).as("dates", "d");

And then creating a reference to it like this:

.select(
  field(name("dates", "d"), currencyConversion.getDate().getDataType())
  ...
)

As always, assuming this static import:

import static org.jooq.impl.DSL.*;

JOOQ: How to use postgres generate_series and insert into table?

1 Answers1