sqlpostgresqlinsertrangerange-types

How to use the NOW() function as upper bound of a range?

I have a table with a column of type tstzrange in a Postgres 10.6 database. I need to insert / update rows with a defined lower bound but the value of the current time for the upper bound of the range, so NOW() as upper value.

Have tried things like:

UPDATE table_name
SET date_range = ['2018-03-23 00:00:00-05', now())
WHERE id = 3;

Is there a way to use a built-in function or a subquery maybe?

Solution

  • Use a range constructor. The manual:

    Each range type has a constructor function with the same name as the range type. Using the constructor function is frequently more convenient than writing a range literal constant, since it avoids the need for extra quoting of the bound values. The constructor function accepts two or three arguments. The two-argument form constructs a range in standard form (lower bound inclusive, upper bound exclusive), while the three-argument form constructs a range with bounds of the form specified by the third argument. The third argument must be one of the strings “()”, “(]”, “[)”, or “[]”.

    So:

    UPDATE table_name
SET    date_range = tstzrange('2018-03-23 00:00:00-05', now())
WHERE  id = 3;

    I assume you are aware that now() resolves to the start time of the transaction.