Re: EXCLUDE USING and tstzrange

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 16:20, Joe Conway <mail(at)joeconway(dot)com> wrote:

> 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

Thanks Joe !