5 ms·
What is your favorite SQL dialect and why?
My favorite SQL dialect (which is probably seen as a downside more than a positive) is MySQL. The two main reasons are:
(1) Being able to use both single- and double-quotes for strings (probably a tiny thing, but for me I enjoy that, perhaps because I have a python background);
(2) having types that always coerce, such as `SELECT numCol=strCol` or `SELECT date="2014-01-01"`.
On the other hand, a dialect like BigQuery seems too strict for me, erroring on something such as `SELECT 1="1"` or `SELECT date"2014-01-01"`.
What is your favorite SQL dialect and why?
- PaulHoule 3y agoPgSQL because of full phat functionality.
- deemarklit 3y agoWhat would be an example of where you prefer postgres over something else? What's your favorite part of the syntax?
- PaulHoule 3y agoI like arrays https://www.postgresql.org/docs/14/arrays.html https://www.postgresql.org/docs/14/arrays.html The full-text functionality kicks ass https://www.postgresql.org/docs/14/textsearch.html https://www.postgresql.org/docs/14/textsearch.html It can query JSON and XML documents directly https://www.postgresql.org/docs/14/datatype-json.html https://www.postgresql.org/docs/14/datatype-json.html https://www.postgresql.org/docs/14/functions-xml.html https://www.postgresql.org/docs/14/functions-xml.html It supports stored procedures https://www.postgresql.org/docs/14/plpgsql.html https://www.postgresql.org/docs/14/plpgsql.html The extension mechanisms are very powerful, if you are interested doing nearest-neighbor vector search like Pinecone or FAISS (super hot today) you can install https://github.com/pgvector/pgvector https://github.com/pgvector/pgvector Adding it all up, pgsql has a lot of the functionality you'd expect in a database like Oracle but it is also a product engineering managers love because it is highly reliable and easy to maintain.
- deemarklit 3y agoSo cool, I didn't realize how deep the JSON and XML functionality out of the box were. So much better than the support that Mysql or most others have. Thanks for sharing! Also, when do you find yourself using Arrays in a database? (instead of, say, a one-to-many FK table).
- ttfkam 3y agoCasting with "::" is a Postgres convenience I use every day. But really it's all about the features. Every time someone brings up MySQL, I look at the following link and see a sea of red. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html
- mmphosis 3y agoAgnostic SQL that works with Postgres, SQLite and MariaDB
- deemarklit 3y agoI see. When you say 'agnostic SQL', which data types or features do you usually have to give up? Also, do mixed-type comparisons ever get in the way, as these are usually handled differently in Postgres vs SQLite vs MariaDB -- for example, `1='1'` returns `true` in MariaDB (or even Postgres) but `false` in SQLite.
- joshxyz 3y agoPostgresql for me simply because the docs. I am losing my mind googling mysql maradb sql.
- deemarklit 3y agoI don't mind the mysql docs at all. What's an example where you've found the docs irritating with the other two? (mariadb I've had troubles finding things in the docs)
- danielovichdk 3y agoHaving worked with mysql, postgres, Oracle and ms SQL. T-SQL any day. Great documentaion, tooling, analyzerers and third party options.