Re: EXCLUDE USING and tstzrange

On 6/4/21 10:58 AM, Laura Smith wrote:
> One other question, what's the syntax for manipulating only the upper
> bound of a range.
> 
> Say I have a Postgres function that does a "SELECT INTO" for an
> existing tsrange.  Is there an easy way to change the variable's
> upper bound whilst leaving the "old" lower bound intact ?

There may be easier/better ways, but for example this works:

8<------------------------------
insert into test
  values(42, '[2021-01-01, 2021-06-03)');
INSERT 0 1

select test_range from test where test_id = '42';
                      test_range
-----------------------------------------------------
  ["2021-01-01 00:00:00-05","2021-06-03 00:00:00-04")
(1 row)

update test
  set test_range = tstzrange(lower(test_range),
                             '2021-06-04', '[)')
where test_id = '42';
UPDATE 1

select test_range from test where test_id = '42';
                      test_range
-----------------------------------------------------
  ["2021-01-01 00:00:00-05","2021-06-04 00:00:00-04")
(1 row)
8<------------------------------

HTH,

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development