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
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 |