Refresh Lower / Upper Range Limit

Question

I have a column like tstzrange (timestamp with time zone range) and I need to update only the upper or lower border of this value (and keep the inclusive / exclusive borders)

I managed to change

 (-infinity,infinity) 

from

 UPDATE table SET my_column = tstzrange( lower(my_column), now(), '()' ) 

and I

 (-infinity, <current timestamp>) 

but I don’t know how to preserve default range bounds .. this would even change [ ] to ( )

Answer 1

I found a function that I missed, maybe do it like this

 UPDATE table SET my_column = tstzrange( lower(my_column), now(), concat( CASE WHEN lower_inc(my_column) THEN '[' ELSE '(' END, CASE WHEN upper_inc(my_column) THEN ']' ELSE ')' END ) ) 

It would be better to create a function for this, perhaps. Or is there another (simpler / better) solution?

Answer 2

As far as I know, using CASE WHEN ... is the best way to get borders. Here are some simple custom functions that return range bounds for all native range types.

Warning: These functions behave surprisingly because range types behave in sometimes surprising ways .

The built-in range types int4range, int8range, and daterange use all the canonical form, which includes the lower bound and excludes the upper bound; i.e [).

and

Although '(]' is indicated here, the displayed value will be converted to canonical form , since int8range is a discrete range type.,.

(in italics)

PostgreSQL allows you to canonize a closed range from 1 to 10 in a half-open range from 1 to 11.

 select int4range('[1,10]'); [1,11) 

It does the same for ranges that are half open on the left.

 select int4range('(1,10]'); [2,11) 

range_bounds () returns the bounds of the result, not for input.

 select range_bounds(int4range('(1,10]')); [) 

Functions

 create or replace function range_bounds(in range int4range) returns char(2) as $$ select case when lower_inc(range) then '[' else '(' end || case when upper_inc(range) then ']' else ')' end; $$ language sql returns null on null input; create or replace function range_bounds(in range int8range) returns char(2) as $$ select case when lower_inc(range) then '[' else '(' end || case when upper_inc(range) then ']' else ')' end; $$ language sql returns null on null input; create or replace function range_bounds(in range numrange) returns char(2) as $$ select case when lower_inc(range) then '[' else '(' end || case when upper_inc(range) then ']' else ')' end; $$ language sql returns null on null input; create or replace function range_bounds(in range tsrange) returns char(2) as $$ select case when lower_inc(range) then '[' else '(' end || case when upper_inc(range) then ']' else ')' end; $$ language sql returns null on null input; create or replace function range_bounds(in range tstzrange) returns char(2) as $$ select case when lower_inc(range) then '[' else '(' end || case when upper_inc(range) then ']' else ')' end; $$ language sql returns null on null input; create or replace function range_bounds(in range daterange) returns char(2) as $$ select case when lower_inc(range) then '[' else '(' end || case when upper_inc(range) then ']' else ')' end; $$ language sql returns null on null input;