unnesting multirange data types

From: "Jonathan S(dot) Katz" <jkatz(at)postgresql(dot)org>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: unnesting multirange data types
Date: 2021-06-09 18:33:56
Message-ID: 60258efe-bd7e-4886-82e1-196e0cac5433@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I have been exploring multirange data types using PostgreSQL 14 Beta 1.
Thus far I'm really happy with the user experience, and it has allowed
me to simplify some previously onerous queries!

I do have a question about trying to "unnest" a multirange type into its
individual ranges. For example, I have a query where I want to find the
availability over a given week. This query may look something like:

SELECT datemultirange(daterange(CURRENT_DATE, CURRENT_DATE + 7))
- datemultirange(daterange(CURRENT_DATE + 2, CURRENT_DATE + 4))
as availability;

availability
---------------------------------------------------
{[2021-06-09,2021-06-11),[2021-06-13,2021-06-16)}
(1 row)

I would like to decompose the returned multirange into its individual
ranges, similarly to how I would "unnest" an array:

SELECT * FROM unnest(ARRAY[1,2,3]);
unnest
--------
1
2
3
(3 rows)

So something like:

SELECT unnest('{[2021-06-09,2021-06-11),
[2021-06-13,2021-06-16)}')::datemultirange;

unnest
-------------------------
[2021-06-09,2021-06-11)
[2021-06-13,2021-06-16)
(2 rows)

I looked at the various functions + operators available for the
multirange types in the documentation but could not find anything that
could perform this action.

Does this functionality exist?

Thanks,

Jonathan

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2021-06-09 18:45:06 Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic
Previous Message Tom Lane 2021-06-09 17:58:08 Re: Character expansion with ICU collations