1

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?

  • A test case with an actual CREATE TABLE script would be welcome. And clarify whether you want to apply the operation to just the one specific row or all rows overlapping with the given tsrange. Define exactly what qualifies as "overlapping". – Erwin Brandstetter Aug 13 '14 at 14:39
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.