> it creates an index; it does not "create" a "key"
I'm still not sure what you mean. By key, do you mean a unique key? With some quick searching, I'm not finding any difference in the Postgres docs.
> MySQL will permit this statement to execute
That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL.
> Simple date math in SELECTS would result in idiocy
I've never tried that before, and it looks like that hasn't changed. No idea why that doesn't throw an error.
Since 8.0 (April 2018), schema changes are transactional, but you can't include DML changes in the same transaction as a DDL. I haven't found that to be a dealbreaker, but would certainly be nice to have.
No, I mean key[1]. A key is a set of attributes that uniquely identifies a row. The primary key in a table is typically one such key, but tables may also have other such keys. In particular, if the primary key is a surrogate key, there is often another natural key. "Key" is just the general form of all of those things. A key MUST be non-null & unique in most RDBMSs, or it trivially isn't a key.
But an index is just a datastructure to speed up access. You almost always cover the key with an index, as keys are generally the means with which your going to look up other attributes. But one often has other indexes that cover non-keys, e.g., if one wants to search based on some non-key attribute.
So naming the create index command "CREATE KEY" is kind of dumb. I don't think they'll remove it, as it would be a breaking change. (Probably even changing the SHOW CREATE TABLE output to use CREATE INDEX would be breaking to somebody, and I don't see MySQL as being willing to do it…)
> That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL.
So it has! I've used MySQL within the last 6 years, and definitely have hit this, so… IDK. It was 5.<something> but that's not really saying a lot given how long the 5.x series persisted. We were on a managed service, and I wonder if our service host was holding us back, here.
Also, at least in the Docker container, the default encoding does seem to at least be utf8mb4, which is good, though I kinda wish they'd just get rid of "utf8" and make utf8mb4 utf8, but, this'll probably do the job. So, good to see MySQL has improved, I guess, but for me personally it took much too long.
I'm still not sure what you mean. By key, do you mean a unique key? With some quick searching, I'm not finding any difference in the Postgres docs.
> MySQL will permit this statement to execute
That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL.
> Simple date math in SELECTS would result in idiocy
I've never tried that before, and it looks like that hasn't changed. No idea why that doesn't throw an error.
select date('2021-11-01') - date('2021-10-01'), datediff(date('2021-11-01'), date('2021-10-01'))
results in: 100, 31
> Schema changes escaped from transactions
Since 8.0 (April 2018), schema changes are transactional, but you can't include DML changes in the same transaction as a DDL. I haven't found that to be a dealbreaker, but would certainly be nice to have.