I got the Postgresql tsrange operator working with JOOQ, and figured it
might be helpful for others and generate a useful discussion. The
following is probably highly ill-advised and totally unsupported by JOOQ.
1) Using tsrange values and operators in a query:
To use a tsrange as a condition in a where clause:
String condition = "appointment.period && tsrange('2015-01-01 00:00:00',
'2015-02-01 00:00:00')";
List<Appointment> appointments =
DSL.select().from(APPOINTMENT).where(condition).fetchInto(Appointment.class);
// JOOQ POJO
2) Inserting tsrange values:
The only way I've found to do this is with a workaround - having the start
and end values as columns and using a trigger to update the tsrange column.
Example:
create table appointment (
appointment_id serial primary key,
start timestamp not null,
end timestamp not null,
period tsrange not null,
);
create or replace function appointment_range() returns trigger as
$appointment_range$
begin
new.period := tsrange(new.start::timestamp, new.end::timestamp);
return new;
end;
$appointment_range$ language plpgsql;
create trigger appointment_range before insert or update on appointment
for each row execute procedure appointment_range();
Of course, the limitation here is that you cannot change whether the range
limits are inclusive or not inclusive at runtime.
Initially I tried passing a String to AppointmentRecord.setPeriod similar
to: tsrange('2015-01-01 00:00', '2015-02-01 00:00') but that fails with the
exception: "ERROR: column "period" is of type tsrange but expression is of
type character varying. Hint: You will need to rewrite or cast the
expression.". I also tried the same thing with insertInto(...).set(...),
with the same result.
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
