It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
Out of the most popular relational databases Postgres is the only independent open-source project. MySQL is owned by Oracle, and SQL Server is proprietary; MariaDB is still a little new on the scene. This makes it something of an underdog and enterprise adoption has been smaller-scale and slower; if an approved technology list has three relational database management systems (RDBMS) on it, Postgres won't be one of them (yet!).
Postgres is as good as any of its peers and imo better than most at the business of being an RDBMS. For purposes of trying to settle on one without an established preference, you'd want to know about their level of adherence to the SQL standard and the various non-standard features each provides.
To give an example where adherence to the standard lapses, take SQL Server's implementation of TOP (as in SELECT TOP 1 thing FROM table). The ANSI SQL standard does not recognize this syntax, accepting only SELECT thing FROM table LIMIT 1 instead. Or there's MySQL's limitations on cast types. I don't know exactly where Postgres falls relative to the others on conformance but the docs identify two unimplemented areas of the standard, and my impression is that it's among the more thorough implementations.
Postgres has a lot of extensions to the standard and/or going above and beyond its competitors. Some of them are immensely powerful, like the foreign data wrapper system which allows you to query anything you can think of as if it were a table by implementing an API. You can even use Python with the multicorn project. Others are extremely convenient, like real UUID types (implemented by most) or array fields (also found in Oracle). Some let you manage complexity better by doing bizarre things to your data model, like table inheritance (a table capitals inherits the fields of a table cities and can be inserted into independently; if you query cities, you also see rows in capitals). Its JSON support is also very good, although this is an area that's been seeing a lot of activity across the board lately.
I feel like, at least for MySQL, the JSON support appeared after PostgreSQL implemented it :-)
Another couple of things PostgreSQL does really well: utf-8 (don't know why but for a while the default utf-8 implementation in MySQL was limited to three bytes) and managing and querying geographical data.
It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
Yeah, I've been looking into MySQL's JSON stuff in the past couple weeks and it definitely seems to be playing catch-up. Postgres hasn't solved the problem space entirely, but MySQL has a smaller function suite, no binary JSON, no direct indexing, and no lateral join. You can't even get a real boolean out of a JSON document in-query because MySQL doesn't have a boolean type and can't cast to tinyint.
I worked with Postgres for PostGis extension: it add spacial object to make map like OpenStreetMap - A great and well documented project.
(This kind of extensions may exists in other SQL-DB)
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Out of the most popular relational databases Postgres is the only independent open-source project. MySQL is owned by Oracle, and SQL Server is proprietary; MariaDB is still a little new on the scene. This makes it something of an underdog and enterprise adoption has been smaller-scale and slower; if an approved technology list has three relational database management systems (RDBMS) on it, Postgres won't be one of them (yet!).
Postgres is as good as any of its peers and imo better than most at the business of being an RDBMS. For purposes of trying to settle on one without an established preference, you'd want to know about their level of adherence to the SQL standard and the various non-standard features each provides.
To give an example where adherence to the standard lapses, take SQL Server's implementation of
TOP(as inSELECT TOP 1 thing FROM table). The ANSI SQL standard does not recognize this syntax, accepting onlySELECT thing FROM table LIMIT 1instead. Or there's MySQL's limitations on cast types. I don't know exactly where Postgres falls relative to the others on conformance but the docs identify two unimplemented areas of the standard, and my impression is that it's among the more thorough implementations.Postgres has a lot of extensions to the standard and/or going above and beyond its competitors. Some of them are immensely powerful, like the foreign data wrapper system which allows you to query anything you can think of as if it were a table by implementing an API. You can even use Python with the multicorn project. Others are extremely convenient, like real UUID types (implemented by most) or array fields (also found in Oracle). Some let you manage complexity better by doing bizarre things to your data model, like table inheritance (a table
capitalsinherits the fields of a tablecitiesand can be inserted into independently; if you querycities, you also see rows incapitals). Its JSON support is also very good, although this is an area that's been seeing a lot of activity across the board lately.Thanks a lot! Very Informative!
I feel like, at least for MySQL, the JSON support appeared after PostgreSQL implemented it :-)
Another couple of things PostgreSQL does really well: utf-8 (don't know why but for a while the default utf-8 implementation in MySQL was limited to three bytes) and managing and querying geographical data.
Yeah, I've been looking into MySQL's JSON stuff in the past couple weeks and it definitely seems to be playing catch-up. Postgres hasn't solved the problem space entirely, but MySQL has a smaller function suite, no binary JSON, no direct indexing, and no lateral join. You can't even get a real boolean out of a JSON document in-query because MySQL doesn't have a boolean type and can't cast to tinyint.
I worked with Postgres for PostGis extension: it add spacial object to make map like OpenStreetMap - A great and well documented project.
(This kind of extensions may exists in other SQL-DB)