Re: Finding free time period on non-continous tstzrange field values

On Thu, Dec 1, 2022 at 7:59 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Amitabh Kant <amitabhkant(at)gmail(dot)com> writes:
> > I tried the following query :
>
> > SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022-11-30
> > 00:00:00', '[]')) -
> > range_agg(time_range) AS availability
> > FROM test_time_range
> > WHERE time_range && tstzrange('2022-11-25 00:00:00', '2022-11-30
> 00:00:00',
> > '[]');
>
> > but then I receive the following error. My guess is I need to cast the
> > tstzrange output, but can't seem to find the correct cast.
>
> > ERROR: function tstzmultirange(tstzrange) does not exist
> > LINE 1: SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022... ^
> > HINT: No function matches the given name and argument types. You might
> need
> > to add explicit type casts. SQL state: 42883 Character: 8
>
> That function certainly should exist:
>
> psql (14.6)
> Type "help" for help.
>
> postgres=# \df tstzmultirange
> List of functions
> Schema | Name | Result data type | Argument data types |
> Type
>
> ------------+----------------+------------------+----------------------+------
> pg_catalog | tstzmultirange | tstzmultirange | |
> func
> pg_catalog | tstzmultirange | tstzmultirange | VARIADIC tstzrange[] |
> func
> pg_catalog | tstzmultirange | tstzmultirange | tstzrange |
> func
> (3 rows)
>
> My guess is that your server is not in fact PG14, but some
> older version.
>
> regards, tom lane
>

My apologies as indeed was the case. Server is running 13.6 .... Will
update the server and try it again.

Amitabh