SQL index to validate date ranges

Question

I'm modeling a case where batches of products arrive and are consumed over time.

The data looks something like the following:

batch_units

| id | batch_id | date       | quantity |
|----|----------|------------|----------|
| 1  | 1        | 2020-01-01 | 100      |
| 2  | 1        | 2020-01-01 | -5       |
| 3  | 1        | 2020-01-03 | -3       |
| 4  | 2        | 2020-02-01 | 50       |
| 5  | 2        | 2020-02-03 | -1       |

Each batch starts off with some quantity, which is consumed over time.

I want to ensure that any negative quantities will always be after the initial quantity (the positive value) for each batch.

For this I've used the following GiST index:

alter table batch_units
add constraint batch_units_date_overlap_check
exclude using gist (
  batch_id with =,
  (
    case when quantity >= 0
      then daterange('-infinity'::date, date, '()')
      else daterange(date, date, '[]')
    end
  ) with &&
);

The works for almost every case, but doesn't allow removing from the same batch twice in the same day. It would be ideal if it allowed for this.

I'm not sure if I'm approaching the problem correctly here. Does anyone have any suggestions?

For the case of batch_id = 1 and the date 2020-01-01, how do we actually know whether the negative quantity really came first or not? Note that there no order in your SQL table; if you show us this order, we need some column to provide the ordering. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 20, 2020 at 2:58
I see what you mean, that was a bit of a typo on my end. It's reasonable to assume that the first negative quantity would be at least the day after - that limitation would be fine. — J3Y
– J3Y, Commented Jul 20, 2020 at 3:03

Tim Biegeleisen · Accepted Answer · 2020-07-20 03:19:54Z

1

If you wanted to find batches which were valid, i.e. ones which do not start off with a negative number, you may use the following exists logic:

SELECT DISTINCT batch_id
FROM batch_units bu1
WHERE
    quantity > 0 AND
    NOT EXISTS (SELECT 1 FROM batch_units bu2
                WHERE bu2.batch_id = bu1.batch_id AND bu2.date < bu1.date AND
                      bu2.quantity < 0);

Demo

The above query should benefit from the following standard B-tree index:

CREATE INDEX idx ON batch_units (batch_id, date, quantity);

This should allow Postgres to just do a scan of the batch_units table, and then quicky lookup each record against the index.

edited Jul 20, 2020 at 3:19

answered Jul 20, 2020 at 3:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J3Y Over a year ago

Would this be possible to implement as a constraint or index?

Tim Biegeleisen Over a year ago

@J3Y I think you might have to use a trigger to get the above logic where you want it. But, maybe there are other ways of doing this besides what I posted.

J3Y Over a year ago

Thanks, I think for now we'll keep the constraint since it's simpler - although it comes with the limitation that 2 negative values can't have the same date. If we need more complex validation later then we may migrate to trigger validation.

Collectives™ on Stack Overflow

SQL index to validate date ranges

batch_units

1 Answer 1

Demo

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

batch_units

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related