Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Multiranges in Postgres

cybertec-postgresql.com

Post reply on HN
Print view View original

21–30 of 34 posts

Re: Multiranges in Postgres

#21

Post by orthoxerox »

alexvoda wrote: post #17
polskibus wrote: post #14

Earlier quoted context omitted.

Would you mind sharing concrete examples of SQL Server deviating from the standard ?

No RDBMS is fully compliant with the standard, especially newer versions of the standard. There was a website comparing the major RDBMSs to the standard with examples. I can not find it now but I found this: https://en.wikipedia.org/wiki/SQL_compliance Edit: this was it https://troels.arvin.dk/db/rdbms/ It's not updated though.

And it has some mistakes: for example, in Postgres TIMESTAMP WITH TIME ZONE doesn't store the time zone. It's more like Oracle's TIMESTAMP WITH LOCAL TIME ZONE: it converts the timestamp to UTC when storing and converts it to the session's time zone when retrieving.

Re: Multiranges in Postgres

#22

Post by darksaints »

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!

Re: Multiranges in Postgres

#23

Post by phamilton »

thom wrote: post #7

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.

However, multirange GiST is implemented by merging all ranges into a single range (ignoring any gaps), which makes it less useful in many cases.

src: https://github.com/postgres/postgres/blob/f14aad5169baa5e2ac...

Re: Multiranges in Postgres

#24

Post by alexvoda »

orthoxerox wrote: post #21
alexvoda wrote: post #17

Earlier quoted context omitted.

No RDBMS is fully compliant with the standard, especially newer versions of the standard. There was a website comparing the major RDBMSs to the standard with examples. I can not find it now but I found this: https://en.wikipedia.org/wiki/SQL_compliance Edit: this was it https://troels.arvin.dk/db/rdbms/ It's not updated though.

And it has some mistakes: for example, in Postgres TIMESTAMP WITH TIME ZONE doesn't store the time zone. It's more like Oracle's TIMESTAMP WITH LOCAL TIME ZONE: it converts the timestamp to UTC when storing and converts it to the session's time zone when retrieving.

I would argue that at least at the time that webpage was written and maintained, none, of them (including the standard) got timestamps right. In the above case timezone is actually an offset.

Arguably, timestamp with timezone should store a timestamp plus a tz_database time zone. Storing offsets is easy and useful but not necessarily correct. Timestamp with Offset should be a different type.

Validating all of those rules would be a hairy mess. For example, what do you do with a timestamp in the future when changes are introduced (negative leap seconds, changes to DST, geopolitical changes affecting timezones).

Re: Multiranges in Postgres

#25

Post by ainar-g »

alexvoda wrote: post #17
polskibus wrote: post #14

Earlier quoted context omitted.

Would you mind sharing concrete examples of SQL Server deviating from the standard ?

No RDBMS is fully compliant with the standard, especially newer versions of the standard. There was a website comparing the major RDBMSs to the standard with examples. I can not find it now but I found this: https://en.wikipedia.org/wiki/SQL_compliance Edit: this was it https://troels.arvin.dk/db/rdbms/ It's not updated though.

https://modern-sql.com/ is another site with compatibility graphs.

Re: Multiranges in Postgres

#26

Post by Tostino »

phamilton wrote: post #23
thom wrote: post #7

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.

However, multirange GiST is implemented by merging all ranges into a single range (ignoring any gaps), which makes it less useful in many cases. src: https://github.com/postgres/postgres/blob/f14aad5169baa5e2ac...

Right, but it just means the index is not used as the final source of truth and the DB has to run an exact check on the smaller subset of rows returned. You won't get incorrect queries, but depending on the workload it may not be a useful index for improving query speed.

Re: Multiranges in Postgres

#28

Post by pjungwir »

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.

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:

https://arxiv.org/abs/2112.06947

Re: Multiranges in Postgres

#29

Post by durkie »

darksaints wrote: post #22

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. Additionall…

yeah they've been super useful for me too. i haven't switched to the multirange datatype yet (still use int4range[]), but I'm excited to since you can now do exclusion constraints on multiranges, which will greatly simplify some things for me.

i keep track of roads that a person has biked/walked for Wandrer (wandrer.earth), and storing everything as a geometry would be a storage and analytical nightmare. keeping things as a range lets you easily determine what part of an activity covered new ground, and you can generate the geometry / length on demand while only storing a few integers (plus the raw road geometry, but that's shared among everyone).

Post reply on HN