4 ms·

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well. Sure, but the non-standard enhancements

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well. Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.

jade12 8y ago
Doesn't every major RDBMS support JSON out of the box these days?
- zaarn 8y ago
  (Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG. In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.
  - atwebb 8y ago
    MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.
wtetzner 8y ago
SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016 https://en.wikipedia.org/wiki/SQL:2016
- inferiorhuman 8y ago
  Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.
  - crazysmoove 8y ago
    I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1]. [0] https://modern-sql.com/ https://modern-sql.com/ [1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example (Edited for formatting.)
- ioltas 8y ago
  Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72b6460336e86ad5cafd3426af6013c7d8457367 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... The release is planned for roughly next September/October.
irrational 8y ago
Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?
- ken 8y ago
  SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.
  - matharmin 8y ago
    While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.
  - nbevans 8y ago
    And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.
- marcolussetti 8y ago
  If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.
  - GordonS 8y ago
    Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.
- lefred 8y ago
  Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mysql-80-document-store https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-nosql-sql-mysql https://www.slideshare.net/lefred.descamps/oracle-code-roma-...

jade12 8y ago

Doesn't every major RDBMS support JSON out of the box these days?

zaarn 8y ago
(Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG. In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.
- atwebb 8y ago
  MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.

wtetzner 8y ago

SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016 https://en.wikipedia.org/wiki/SQL:2016

inferiorhuman 8y ago
Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.
- crazysmoove 8y ago
  I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1]. [0] https://modern-sql.com/ https://modern-sql.com/ [1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example (Edited for formatting.)
ioltas 8y ago
Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72b6460336e86ad5cafd3426af6013c7d8457367 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... The release is planned for roughly next September/October.

irrational 8y ago

Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?

ken 8y ago
SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.
- matharmin 8y ago
  While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.
- nbevans 8y ago
  And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.
marcolussetti 8y ago
If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.
- GordonS 8y ago
  Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.
lefred 8y ago
Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mysql-80-document-store https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-nosql-sql-mysql https://www.slideshare.net/lefred.descamps/oracle-code-roma-...