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

11–20 of 34 posts

Re: Multiranges in Postgres

#11

Post by jansommer »

ainar-g wrote: post #9

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

Range types are not supported in SQL Server. You'd likely need columns for start and end to be compliant.

Re: Multiranges in Postgres

#12

Post by afhammad »

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.

Multiranges could 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.

Re: Multiranges in Postgres

#13

Post by Nullabillity »

jansommer wrote: post #11
ainar-g wrote: post #9

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

Range types are not supported in SQL Server. You'd likely need columns for start and end to be compliant.

SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.

Re: Multiranges in Postgres

#14

Post by polskibus »

Nullabillity wrote: post #13
jansommer wrote: post #11

Earlier quoted context omitted.

Range types are not supported in SQL Server. You'd likely need columns for start and end to be compliant.

SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.

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

Re: Multiranges in Postgres

#15

Post by Someone »

ainar-g wrote: post #9

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

Given that https://www.postgresql.org/docs/15/features-sql-standard.htm... doesn’t mention them, I expect them to be non-standard.

(The word ‘range’ appears once there, in “F404. Range variable for common column names”, but reading the English parts of https://github.com/dazuiba008/digoal_blog_fork/blob/master/2..., it’s clear that isn’t about range types)

Re: Multiranges in Postgres

#16

Post by stuaxo »

This is really useful, I've been doing something like this manually.

What's the performance like to get multiranges from a million row column of unique integers, that has a few gaps in it.

(Also slightly unsorted, but obv can do that in query).

Re: Multiranges in Postgres

#17

Post by alexvoda »

polskibus wrote: post #14
Nullabillity wrote: post #13

Earlier quoted context omitted.

SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.

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.

Re: Multiranges in Postgres

#18

Post by darksaints »

polskibus wrote: post #14
Nullabillity wrote: post #13

Earlier quoted context omitted.

SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.

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

SELECT TOP

Re: Multiranges in Postgres

#19

Post by rubyist5eva »

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.

we've been speeding up our time based queries using tsrange and gist indexes and they have been really great

Re: Multiranges in Postgres

#20

Post by jansommer »

Nullabillity wrote: post #13
jansommer wrote: post #11

Earlier quoted context omitted.

Range types are not supported in SQL Server. You'd likely need columns for start and end to be compliant.

SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.

I agree, you'll never hear me recommend MSSQL to anyone, but it's unfortunately quite popular among my customers and hence something I have to take into account when buying software.
Post reply on HN