And although you didn't ask, I'll list what's wrong with Oracle. It's very simple.
Oracle treats empty strings as being NULL.
Anyone who's never used Oracle before in their life is probably wondering if I'm making it up. I'm not. In Oracle, inserting '' in a VARCHAR column is exactly the same as inserting NULL. And if there's a NOT NULL column, you're not allowed to store the empty string in there.
Which means that in Oracle, you do not have any way of distinguishing "I don't know the person's middle name" vs. "I know what the person's middle name is: he/she doesn't have one".
There are apparently historical reasons for this, but I don't care. The empty string is NOT the same as NULL, and any software that treats them as the same IS BUGGY!
Sorry. Had to get that off my chest. I know I'm answering a question you didn't ask, but that has been bothering me for nearly 25 years (I first learned about this misfeature of Oracle's in 2002 or 2003), and I just had to vent to somebody who would understand.
I'm pretty sure the historical reasons I mentioned involved creating the software before the SQL standard came out, meaning they were treating NULL as meaning "nothing", instead of meaning "unknown" as the SQL standard requires. But that's as far as I care to go digging into a database system I will never use of my own free will.
Interesting I have definitely used the distinction between null and empty string quite a bit in MSSQL and matches most programming languages.
Another issue that used to exist was Oracle table name were limited to like 12 characters or something so I have seen horrible abbreviated table names to fit in the Oracle limitation even in other DB's due to some systems being able to run on both MSSQL and Oracle even though SQL Server has a 255 char limit for table names.
Don't know of anything wrong with MariaDB, but there used to be plenty wrong with MySQL. To give the most egregious example (THANKFULLY fixed in MariaDB, but was present in MySQL for the longest time), inserting the value 128 into a TINYINT column (signed 8-bit int) would clamp the value rather than returning an error. Which might be what you want... except if that was a primary key column. Marvel at the following, which used to be how MySQL behaved:
CREATE DATABASE foo;
USE foo;
CREATE TABLE one ( id TINYINT NOT NULL PRIMARY KEY ) TYPE=InnoDB ;
CREATE TABLE two (
id TINYINT NOT NULL PRIMARY KEY,
INDEX (id),
CONSTRAINT id_fkey FOREIGN KEY (id) REFERENCES one(id)
) TYPE=InnoDB ;
Now that we've created both tables, let's insert a record into table one:
INSERT INTO one VALUES (127);
And now let's insert a record with a different primary key into table two:
INSERT INTO two VALUES (128);
MariaDB will give you an error at this point (ERROR 1264 (22003): Out of range value for column 'id' at row 1), but MySQL (at least back when I tried this about ten years ago, which was the last time I was forced to work with MySQL — and I am so glad I never have to go back!) would return no error message and just say "Query OK, 1 row affected (0.009 sec)".
Now let's select the value we inserted into table "two":
SELECT * FROM two;
And what do we see? The value 127, even though we inserted 128. Which has created a foreign-key relationship to table "one" that we never intended to put in there.
There are other reasons why MySQL was inadequate, but I no longer remember them. Probably MariaDB has fixed them by now. But I no longer have to use MySQL/MariaDB for anything, and I never want to go back. I have a VERY strong averse reaction, caused by past pain, when I think of using MariaDB. (I actually spun up a virtual machine to test what I wrote here, because there's no way I was going to install MariaDB on my primary work machine).
4. Many ways to lock out instant add table, meaning you can’t add a column without a full table write, which can lock the table for minutes at a time in even moderately sized tables.
5. Dealing with legacy mysql databases often means dealing with utf8mb3, which used to be the default utf8 data type despite not storing all utf8
6. Dealing with all but the most recent mysql databases means dealing with non strict mode which means your NOT NULL column won’t require a value.
Just a set of things too minor to move off of it but annoying enough to not want to start with it.
My list:
No `explain (analyze,buffers)`. Instant DDL has some warts (e.g. fk, metadata locks). Query planning bugs (actually... query planning in general is disappointing). Exiting the repl doesn't stop queries. Implicit type casting. Replication lag from large DDL (e.g. creating an index). Lack of two phase DDL (creating constraints NOT VALID and then VALIDATE later). Lack of extensions (e.g. pg_vector). No safe access to inspect buffer cache. AWS Aurora seems to only add shiny new things to Postgres. And more.
Again, none of this is quite enough to migrate off of it for an established system, but certainly enough to avoid it on a new project.
And although you didn't ask, I'll list what's wrong with Oracle. It's very simple.
Oracle treats empty strings as being NULL.
Anyone who's never used Oracle before in their life is probably wondering if I'm making it up. I'm not. In Oracle, inserting '' in a VARCHAR column is exactly the same as inserting NULL. And if there's a NOT NULL column, you're not allowed to store the empty string in there.
Which means that in Oracle, you do not have any way of distinguishing "I don't know the person's middle name" vs. "I know what the person's middle name is: he/she doesn't have one".
There are apparently historical reasons for this, but I don't care. The empty string is NOT the same as NULL, and any software that treats them as the same IS BUGGY!
Sorry. Had to get that off my chest. I know I'm answering a question you didn't ask, but that has been bothering me for nearly 25 years (I first learned about this misfeature of Oracle's in 2002 or 2003), and I just had to vent to somebody who would understand.
I'll list what's wrong with Oracle
Very interesting (and hopefully cathartic). I never got past "it involves doing business with Oracle".
> Oracle treats empty strings as being NULL.
That is ridiculous. Do not they know the difference between "" and NULL?
I'm pretty sure the historical reasons I mentioned involved creating the software before the SQL standard came out, meaning they were treating NULL as meaning "nothing", instead of meaning "unknown" as the SQL standard requires. But that's as far as I care to go digging into a database system I will never use of my own free will.
Interesting I have definitely used the distinction between null and empty string quite a bit in MSSQL and matches most programming languages.
Another issue that used to exist was Oracle table name were limited to like 12 characters or something so I have seen horrible abbreviated table names to fit in the Oracle limitation even in other DB's due to some systems being able to run on both MSSQL and Oracle even though SQL Server has a 255 char limit for table names.
Don't know of anything wrong with MariaDB, but there used to be plenty wrong with MySQL. To give the most egregious example (THANKFULLY fixed in MariaDB, but was present in MySQL for the longest time), inserting the value 128 into a TINYINT column (signed 8-bit int) would clamp the value rather than returning an error. Which might be what you want... except if that was a primary key column. Marvel at the following, which used to be how MySQL behaved:
Note: the below taken nearly verbatim from https://sql-info.de/mysql/referential-integrity.html#3_5
Now that we've created both tables, let's insert a record into table one:
And now let's insert a record with a different primary key into table two:
MariaDB will give you an error at this point (ERROR 1264 (22003): Out of range value for column 'id' at row 1), but MySQL (at least back when I tried this about ten years ago, which was the last time I was forced to work with MySQL — and I am so glad I never have to go back!) would return no error message and just say "Query OK, 1 row affected (0.009 sec)".
Now let's select the value we inserted into table "two":
And what do we see? The value 127, even though we inserted 128. Which has created a foreign-key relationship to table "one" that we never intended to put in there.
There are other reasons why MySQL was inadequate, but I no longer remember them. Probably MariaDB has fixed them by now. But I no longer have to use MySQL/MariaDB for anything, and I never want to go back. I have a VERY strong averse reaction, caused by past pain, when I think of using MariaDB. (I actually spun up a virtual machine to test what I wrote here, because there's no way I was going to install MariaDB on my primary work machine).
1. No transactional DDL
2. No MERGE statement
3. No partial indices
4. Many ways to lock out instant add table, meaning you can’t add a column without a full table write, which can lock the table for minutes at a time in even moderately sized tables.
5. Dealing with legacy mysql databases often means dealing with utf8mb3, which used to be the default utf8 data type despite not storing all utf8
6. Dealing with all but the most recent mysql databases means dealing with non strict mode which means your NOT NULL column won’t require a value.
If you are talking about SQL Server, the MERGE statement is basically a bug minefield, I wouldn't use it unless I wanted to deadlock myself.
Just a set of things too minor to move off of it but annoying enough to not want to start with it.
My list:
No `explain (analyze,buffers)`. Instant DDL has some warts (e.g. fk, metadata locks). Query planning bugs (actually... query planning in general is disappointing). Exiting the repl doesn't stop queries. Implicit type casting. Replication lag from large DDL (e.g. creating an index). Lack of two phase DDL (creating constraints NOT VALID and then VALIDATE later). Lack of extensions (e.g. pg_vector). No safe access to inspect buffer cache. AWS Aurora seems to only add shiny new things to Postgres. And more.
Again, none of this is quite enough to migrate off of it for an established system, but certainly enough to avoid it on a new project.