Just want to throw out the usefulness of multiranges in my current domain, which is spectrum regulation/licensing. A few of our processes enabled by multirange support have caught serious exclusivity conflicts (where there are more than one licensee with exclusive rights to a specific span of spectrum within a specific geographic area) with licenses that were not caught by the FCC before they were issued. Additionally useful for swaps (where licensees trade licenses to improve their holdings relative to their existing portfolio)...multiranges allow for easy and error free analysis of potential swap targets that maximize benefit to both parties. And we also use it for spot checks to make sure that we are using spectrum legally according to the special conditions constraints (e.g. not using 600MHz A block within a distance that could cause interference with hospital usage of WMTS equipment). I love range types!
It’s also worth knowing that you can accelerate many sorts of range queries with a GIST index. This often ends up faster than using two separate columns.
I've used tsrange successfully in 2 projects to optimise storage of temporal data.
The same can be achieved with start/end timestamp columns at the cost of more complex and error prone queries.<p>Multiranges <i>could</i> be used to further optimise for storage of duplicate data (if a data pattern repeats at different intervals) but it's not a big enough margin for me yet.
I really like PostgreSQL ranges, but I've always been curious, if these are an extension or they are a part of the SQL standard? The “Range Types” page[1] doesn't seem to mention the standard at all, and appendix D.1, “Supported Features”, doesn't mention range types.<p>The reason I'm curious is because may future SQL database implementations will most likely use the standard at least in some capacity, so it'd be nice to know, how future-proof and DBMS-switch-proof a schema using these could be.<p>[1]: <a href="https://www.postgresql.org/docs/current/rangetypes.html" rel="nofollow">https://www.postgresql.org/docs/current/rangetypes.html</a><p>[2]: <a href="https://www.postgresql.org/docs/current/features-sql-standard.html" rel="nofollow">https://www.postgresql.org/docs/current/features-sql-standar...</a>
I'm still hoping to use multiranges to add temporal tables to Postgres, but life has been busy the last year. I apologize to you all for the delay.<p>The coolest application of multiranges I've heard about is for astronomical observations. The authors of this paper were kind enough to share an early draft with me, and they say it greatly speeds up comparisons of sky objects. It's very accessible and a fun read:<p><a href="https://arxiv.org/abs/2112.06947" rel="nofollow">https://arxiv.org/abs/2112.06947</a>
Is there a name for the ` <@ ` operator? Been calling it the tornado operator but I haven't found anything indicating an "official" name.
This is really useful, I've been doing something like this manually.<p>What's the performance like to get multiranges from a million row column of unique integers, that has a few gaps in it.<p>(Also <i>slightly</i> unsorted, but obv can do that in query).