Postgres Application Period Constraints

application_periods_postgres

We'll simulate a table with gas station ids and prices over time.
We want to enforce that time ranges can never overlap and be able to see when overlaps
do occur and also how to effectively have an interval replace underlying intervals.

We follow some advice here: https://stackoverflow.com/questions/10616099/postgres-date-overlapping-constraint

```
CREATE EXTENSION btree_gist;

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),

    from_ts TIMESTAMPTZ,
    to_ts TIMESTAMPTZ,

    CHECK ( from_ts < to_ts ),

    CONSTRAINT overlapping_times EXCLUDE USING GIST (
      gas_station_id WITH =,
      TSTZRANGE(from_ts, to_ts) WITH &&
    )
);
```

With raw SQL:

```
INSERT INTO prices (gas_station_id, gas_price_in_cents, from_ts, to_ts) VALUES
                   (1, 2000, '2000-01-01 00:00:00', '2000-06-01 00:00:00');

INSERT INTO prices (gas_station_id, gas_price_in_cents, from_ts, to_ts) VALUES
                   (1, 5000, '2000-06-01 00:00:00', '2000-12-01 00:00:00');

SELECT * FROM prices;
```

```
16,1,2000,2000-01-01,2000-06-01
17,1,5000,2000-06-01,2000-12-01
```

Now we can see a conflicting interval error:

```
INSERT INTO prices (gas_station_id, gas_price_in_cents, from_ts, to_ts) VALUES
                   (1, 4000, '2000-03-01 00:00:00', '2000-09-01 00:00:00');
```

```
[23P01] ERROR: conflicting key value violates exclusion constraint "overlapping_times" 
Detail: Key (gas_station_id, tstzrange(from_ts, to_ts))=(1, ["2000-03-01 00:00:00+00","2000-09-01 00:00:00+00")) 
conflicts with existing key (gas_station_id, tstzrange(from_ts, to_ts))=
                            (1, ["2000-01-01 00:00:00+00","2000-06-01 00:00:00+00")).
```

To override the underlying intervals we'd do:

```
DELETE FROM prices WHERE from_ts >= '2000-03-01 00:00:00' AND to_ts <= '2000-09-01 00:00:00';

UPDATE prices SET from_ts = '2000-09-01 00:00:00'
WHERE             from_ts >= '2000-03-01 00:00:00' AND to_ts > '2000-09-01 00:00:00'
  AND gas_station_id = 1;

UPDATE prices SET to_ts = '2000-03-01 00:00:00'
WHERE             from_ts < '2000-03-01 00:00:00' AND to_ts <= '2000-09-01 00:00:00'
  AND gas_station_id = 1;

INSERT INTO prices (gas_station_id, gas_price_in_cents, from_ts, to_ts) VALUES
                   (1, 4000, '2000-03-01 00:00:00', '2000-09-01 00:00:00');

SELECT * FROM prices;
```


```
17,1,5000,2000-09-01,2000-12-01
16,1,2000,2000-01-01,2000-03-01
19,1,4000,2000-03-01,2000-09-01
```

However this is a bit unruly for all applications to encode that logic. We can do better here:

```
CREATE OR REPLACE FUNCTION set_gas_prices(
    new_gas_station_id int,
    new_gas_price_in_cents int,
    new_from_ts TIMESTAMPTZ,
    new_to_ts TIMESTAMPTZ
)
RETURNS void AS $$
BEGIN
    DELETE FROM prices
    WHERE from_ts >= new_from_ts AND to_ts <= new_to_ts
      AND gas_station_id = new_gas_station_id;

    UPDATE prices SET from_ts = new_to_ts
    WHERE             from_ts >= new_from_ts AND to_ts > new_to_ts
      AND gas_station_id = new_gas_station_id;

    UPDATE prices SET to_ts = new_from_ts
    WHERE             from_ts < new_from_ts AND to_ts <= new_to_ts
      AND gas_station_id = new_gas_station_id;

    INSERT INTO prices (gas_station_id, gas_price_in_cents, from_ts, to_ts) VALUES
                       (new_gas_station_id, new_gas_price_in_cents, new_from_ts, new_to_ts);
END;
$$  LANGUAGE plpgsql;
```

Now we can test an overlap:

```
TRUNCATE TABLE prices;

SELECT set_gas_prices(1, 2000, '2000-01-01 00:00:00', '2000-06-01 00:00:00');
SELECT set_gas_prices(1, 5000, '2000-06-01 00:00:00', '2000-12-01 00:00:00');

SELECT * FROM prices WHERE (from_ts, to_ts) OVERLAPS ('2000-03-01 00:00:00', '2000-09-01 00:00:00');
```

```
14,1,2000,2000-01-01,2000-06-01
15,1,5000,2000-06-01,2000-12-01
```

And now an interval that overlaps the underlying ones:

```
SELECT set_gas_prices(1,4000,'2000-03-01 00:00:00','2000-09-01 00:00:00');

SELECT * FROM prices;
```

```
12,1,5000,2000-09-01,2000-12-01
11,1,2000,2000-01-01,2000-03-01
13,1,4000,2000-03-01,2000-09-01
```

Audit Logs can be solved here: https://eager.io/blog/audit-postgres/
Temporal Tables could be solved here: https://github.com/nearform/temporal_tables