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.
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".