4 ms·

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

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 to know, how future-proof and DBMS-switch-proof a schema using these could be. [1]: https://www.postgresql.org/docs/current/rangetypes.html https://www.postgresql.org/docs/current/rangetypes.html [2]: https://www.postgresql.org/docs/current/features-sql-standard.html https://www.postgresql.org/docs/current/features-sql-standar...

jansommer 4y ago
Range types are not supported in SQL Server. You'd likely need columns for start and end to be compliant.
- Nullabillity 4y ago
  SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.
  - polskibus 4y ago
    Would you mind sharing concrete examples of SQL Server deviating from the standard ?
    - alexvoda 4y ago
      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 https://en.wikipedia.org/wiki/SQL_compliance Edit: this was it https://troels.arvin.dk/db/rdbms/ https://troels.arvin.dk/db/rdbms/ It's not updated though.
      - orthoxerox 4y ago
        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.
        alexvoda 4y ago
        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).
      - ainar-g 4y ago
        https://modern-sql.com/ https://modern-sql.com/ is another site with compatibility graphs.
    - darksaints 4y ago
      SELECT TOP
    - panzi 4y ago
      One thing that comes to mind is that you have to quote table and column names that don't follow the identifier pattern [like so] instead of "like so". Though I haven't kept up to date on this, maybe it can now also support SQL standard compliant table/column name quoting. Even MySQL can now do that (with a config option, at least). That's only a tiny thing, though. I'm sure there is more. I don't use Microsoft SQL Server. Quite happy with PostgreSQL (and SQLite for tiny in file things).
  - jansommer 4y ago
    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.
Someone 4y ago
Given that https://www.postgresql.org/docs/15/features-sql-standard.html 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/202104/20210401_02.md https://github.com/dazuiba008/digoal_blog_fork/blob/master/2..., it’s clear that isn’t about range types)

jansommer 4y ago

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

Nullabillity 4y ago
SQL Server/MSSQL is just yet another (relatively poor) implementation of the SQL standard with a very presumptuous name.
- polskibus 4y ago
  Would you mind sharing concrete examples of SQL Server deviating from the standard ?
  - alexvoda 4y ago
    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 https://en.wikipedia.org/wiki/SQL_compliance Edit: this was it https://troels.arvin.dk/db/rdbms/ https://troels.arvin.dk/db/rdbms/ It's not updated though.
    - orthoxerox 4y ago
      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.
      - alexvoda 4y ago
        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).
    - ainar-g 4y ago
      https://modern-sql.com/ https://modern-sql.com/ is another site with compatibility graphs.
  - darksaints 4y ago
    SELECT TOP
  - panzi 4y ago
    One thing that comes to mind is that you have to quote table and column names that don't follow the identifier pattern [like so] instead of "like so". Though I haven't kept up to date on this, maybe it can now also support SQL standard compliant table/column name quoting. Even MySQL can now do that (with a config option, at least). That's only a tiny thing, though. I'm sure there is more. I don't use Microsoft SQL Server. Quite happy with PostgreSQL (and SQLite for tiny in file things).
- jansommer 4y ago
  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.

Someone 4y ago

Given that https://www.postgresql.org/docs/15/features-sql-standard.html 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/202104/20210401_02.md https://github.com/dazuiba008/digoal_blog_fork/blob/master/2..., it’s clear that isn’t about range types)