cashapp / sqldelight

SQLDelight - Generates typesafe Kotlin APIs from SQL
https://cashapp.github.io/sqldelight/
Apache License 2.0
6.01k stars 501 forks source link

Add PostgreSql TSRANGE/TSTZRANGE support #5297

Open griffio opened 2 weeks ago

griffio commented 2 weeks ago

👷 🚧 Add some support for TS Range type https://www.postgresql.org/docs/16/rangetypes.html#RANGETYPES-EXAMPLES

Types are mapped to string representations as there is no client side data type such as a range that is compatible (Kotlin ranges only implement open/closed on upper bounds). This is consistent with JSON, TSVECTOR types and uses OTHER type to bind the literal value.

Range functions https://www.postgresql.org/docs/16/functions-range.html are used to work with ranges e.g extract lower and upper values to the client types LocalDateTime and OffSetDateTime

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');

CREATE TABLE TsRanges(
  slot_ts TSRANGE NOT NULL,
  slot_tsz TSTZRANGE
);

SELECT LOWER(slot_ts) AS begin_ts, UPPER(slot_ts) AS end_ts,
LOWER(slot_tsz) AS begin_tsz, UPPER(slot_tsz) AS end_tsz,
EXTRACT (epoch FROM UPPER(slot_ts) - LOWER(slot_ts)) / 60
FROM TsRanges;

SELECT isempty(slot_ts),
 lower_inc(slot_ts), upper_inc(slot_ts),
 lower_inf(slot_ts), upper_inf(slot_ts),
 range_merge(slot_tsz, slot_tsz)
FROM TsRanges;

Also add basic support for EXCLUDE table constraints for range supported indexes

CREATE TABLE Reservations (
    start_time TSTZRANGE,
    finish_time TSTZRANGE,
    CONSTRAINT no_screening_time_overlap EXCLUDE USING GIST (finish_time WITH =, start_time WITH &&)
);

MultiRange support (Postgresql 14 or higher)

Allows useful arithmetic on ranges
e.g identify the available time slots that are not occupied by any appointments within a specified time range.

SELECT tsmultirange(tsrange('2010-01-01 14:30:00', '2010-01-01 15:30:00', '[]'))
 - range_agg(appointment_dates)
FROM Appointments
WHERE appointment_dates && tsrange('2010-01-01 14:30:00', '2010-01-01 15:30:00', '[]');

Note: 🧜 Merge this after https://github.com/cashapp/sqldelight/pull/5274

TODO: Create issue for PR More tests e.g cast SELECT '("2010-01-01 14:30:00+00","2010-01-01 15:30:00+00"]'::TSTZRANGE