Update lower/upper bound of range type

Question

I have column of tstzrange type (timestamp with time zone range) and I need to update only upper or lower bound of this value (and keep inclusive/exclusive boundaries)

I managed to change

(-infinity,infinity)

with

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

and I have

(-infinity, <current timestamp>)

but I don't know how to keep boundaries from default range.. this would change even [ ] to ( )

Answer 1

I found function I missed, it's possible to do that 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 function for this probably. Or is there any other (simpler/better) solution?

Answer 2

Improvement to Mikes great answer.

When we use the pseudo-type anyrange, we only need a single function:

CREATE OR REPLACE FUNCTION range_bounds(IN range anyrange)
    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;