how to update the value of a period contained inside a bigger range 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?
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 (
&lt;same as above&gt;
   ) sub
WHERE range &lt;&gt; 'empty';


SQL Fiddle.