Postgresql – how to update the value of a period contained inside a bigger range

datatypespostgresqlrange-typestimestamp

I have a tsrange like

'[2010-01-01 14:00, 2010-01-01 15:00]',20

And want to update the 20 value to 15 in a range inside that, like

'[2010-01-01 14:10, 2010-01-01 14:30]',15

My expected outcome would be 3 rows like

'[2010-01-01 14:00, 2010-01-01 14:10)',20
'[2010-01-01 14:10, 2010-01-01 14:30)',15
'[2010-01-01 14:30, 2010-01-01 15:00]',20

Is that possible?

Best Answer

You cannot simply use the difference operator -, because it does not allow multiple resulting rows:

ERROR: result of range difference would not be contiguous

So you need to create three rows and use the intersection operator *:

  • 1st range unbounded up to lower bound
  • 2nd range as given
  • 3rd range upper bound to unbounded

WITH cte(r, val) AS (SELECT '[2010-01-01 14:10, 2010-01-01 14:30)'::tsrange, 15)
SELECT t.range * tsrange(NULL, lower(c.r), '(]') - c.r AS range, t.val FROM tbl t, cte c
UNION ALL
SELECT t.range * c.r                                           , c.val FROM tbl t, cte c
UNION ALL
SELECT t.range * tsrange(upper(c.r), NULL, '[)') - c.r         , t.val FROM tbl t, cte c;

Returns:

range                                           val
---------------------------------------------------
["2010-01-01 14:00:00","2010-01-01 14:10:00")   20
["2010-01-01 14:10:00","2010-01-01 14:30:00")   15
["2010-01-01 14:30:00","2010-01-01 15:00:00"]   20

This applies the operation to all rows in table tbl.
And it works for all ranges (overlapping completely, in parts or not at all).

Note that my 2nd row matches the upper bound of the input value (unlike your example, which is probably unintended).

All bounds are preserved. Input values trump existing values. This is achieved with (example 1st part):

tsrange(NULL, lower(c.r), '(]') - c.r AS range
  • The lower bound is unbounded and automatically excluded (()
  • Initially use including upper bound (]); intersect that with the input range, to get the complementary bound to the lower bound of the input range.

To eliminate rows with an empty time range:

WITH cte(r, val) AS (SELECT '[2010-01-01 14:10, 2010-01-01 15:30)'::tsrange, 15) 
SELECT * FROM (
<same as above>
   ) sub
WHERE range <> 'empty';

SQL Fiddle.

Related Question