For contrast, MS SQL Server has `NVARCHAR(MAX)`. It's generally bugged me with MS-SQL that they added `NVARCHAR(MAX)` rather than just changing the behavior of `TEXT`

It's because text and ntext are extremely old LOB types, and they support SQL statements like WRITETEXT and UPDATETEXT that other statements don't. They also can't be indexed, don't support functions like LEFT(), REPLACE(), etc. Text and ntext behave wildly differently than char and varchar while varchar(max) and nvarchar(max) are almost the same. Changing that functionality would cause problems. Further, neither text nor ntext are ISO standard data types.

It's the same reason that SQL Server has datetime2 and Oracle has varchar2 (the latter of which is only now being merged into varchar). That's why float, real, and money still exist even though numeric/decimal was created. They behave differently and in ways that are not exactly backwards compatible. In order to prevent your customers from having to rewrite their application just because you updated your data types, they chose to add new data types.