Postgres Gist

test_file.sql

CREATE EXTENSION btree_gist;

DROP TABLE IF EXISTS prices;
CREATE TABLE prices (
    id serial PRIMARY KEY,

    gas_station_id int,
    gas_price_in_cents int
        CONSTRAINT positive_price CHECK (gas_price_in_cents > 0),

    ts_range tstzrange,

    CONSTRAINT overlapping_times EXCLUDE USING GIST (
      gas_station_id WITH =,
      ts_range WITH &&
    ) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) VALUES
                   (1, 2000, '[2000-01-01 00:00, 2000-06-01 00:00)');
INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) VALUES
                   (1, 2000, '[2000-06-01 00:00, 2000-12-01 00:00)');

SELECT * FROM prices;


create or replace function new_gas_price(
    new_gas_station_id integer,
    new_gas_price_in_cents integer,
    start_ts timestamptz, end_ts timestamptz
) returns void language plpgsql as
$$
DECLARE
    tsrange tstzrange := tstzrange(start_ts, end_ts, '[)');
BEGIN

    CREATE TEMP TABLE tbl_ranges AS
    SELECT gas_station_id, gas_price_in_cents, tstzrange(lower(prices.ts_range), lower(tsrange), '[)') as ts_range
    FROM prices WHERE (prices.ts_range @> tsrange)

    UNION

    SELECT gas_station_id, gas_price_in_cents, tstzrange(upper(tsrange), upper(prices.ts_range), '[)') as ts_range
    FROM prices WHERE (prices.ts_range @> tsrange);

    DELETE FROM prices WHERE (prices.ts_range @> tsrange);


    UPDATE prices SET ts_range = ts_range - tsrange WHERE ts_range && tsrange;

    INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) VALUES (new_gas_station_id, new_gas_price_in_cents, tsrange);
    INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) SELECT * FROM tbl_ranges;


    DELETE FROM prices WHERE ts_range = 'empty';
    DROP TABLE tbl_ranges;

END;
$$;


SELECT * FROM prices;

START TRANSACTION;
SELECT new_gas_price(1, 2500, '2000-03-01 00:00:00', '2000-04-01 00:00:00');
COMMIT;

SELECT * FROM prices;

START TRANSACTION;
SELECT new_gas_price(1, 100, '2000-01-01 00:00:00', '2000-09-01 00:00:00');
COMMIT;

SELECT * FROM prices;