3 ms·

It seems a lot of those things you list as "wrong" come from lack of understanding of the reasons behind these choices, and born out of pure idealism in vacuum.

It seems a lot of those things you list as "wrong" come from lack of understanding of the reasons behind these choices, and born out of pure idealism in vacuum. For example, allowing duplicate rows is irrelevant, because if you have a primary key, there are no duplicate rows. SQL doesn't require primary keys because relational algebra has no such concept as a "primary key". There are just keys. However without defining a primary key, enforcing unique rows would mean the database silently indexing the ENTIRE ROW'S CONTENTS, including potentially blobs and large text fields. This would obviously be nonsense. Likewise, having SELECT DISTINCT be a default would mean a very expensive processing step in your query processing being a default. An expensive step that in fact doesn't matter, because the vast majority of queries don't produce duplicate results in practice. DISTINCT is optional because the need for it is exceptional, and its cost is high. Even you don't buy the "have no NULL" argument. So I don't have to defend this. Real-world data is not perfectly "rectangular". Optional attributes are a thing. So having a primitive for it makes sense. Once again, SQL allows you to define a field as non-nullable, so complaining about it being there if you EXPLICITLY WANT IT is silly. Regarding having many ways to express the same query: that's true for all languages. This is one of the biggest issues in optimizing compilers, canonicalizing expressions so patterns can be recognized. There's no way to do it at the source, so complaining SQL also does it, is like shouting at clouds. Regarding the type system, type systems can always be better, but let's not forget type systems (typically) exist to eliminate mistakes, not to enable new features. The kind of mistake where you compare "weight and quantity" is not likely. More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. Even if SQL had a detailed type system, therefore, most people wouldn't bother duplicating their detailed type definitions from Java to SQL or back. The only remaining issue is SQL is chatty. Which is quite ironic given the procedural code for what SQL does would be several times the size of the SQL query. SQL is a high-level language, and it being explicit is fine. And few extra letters here and there for a keyword don't make or break a language. I prefer chatty over cryptic.

mulmen 5y ago
Totally agree with your post but wanted to add on to this part: > More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. PostgreSQL actually supports user-defined types[1]! You could do something like define [2] a “kilogram” type and a “pound” type and then summing or joining on the column is safe. That could still get sticky with grams and kilograms. Also, pounds are defined in terms of kilograms. So you could also define a “weight” type. This can have semantics like DATE[3] or BOOLEAN[4] so inserting “1kg”, “1kilogram”, “1000g” or “2.204623lb” all store the same value. You can then use (define) format functions like TO_CHAR(weight, string) to display weights in grams, lb or whatever you need. Of course, the other argument here is that all you really need are the primitive types and normalization. A “weight” table can just have a “unit”::string column. You can do conversions with another table such as “from_unit”::string, “to_unit”::string and “multiple”::float. Your “product_weight” table would then just have foreign key relationships to the weight and conversions tables. [1]: https://www.postgresql.org/docs/current/xtypes.html https://www.postgresql.org/docs/current/xtypes.html [2]: https://github.com/df7cb/postgresql-unit https://github.com/df7cb/postgresql-unit [3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DATATYPE-DATETIME-INPUT https://www.postgresql.org/docs/13/datatype-datetime.html#DA... [4]: https://www.postgresql.org/docs/13/datatype-boolean.html https://www.postgresql.org/docs/13/datatype-boolean.html

mulmen 5y ago

Totally agree with your post but wanted to add on to this part: > More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. PostgreSQL actually supports user-defined types[1]! You could do something like define [2] a “kilogram” type and a “pound” type and then summing or joining on the column is safe. That could still get sticky with grams and kilograms. Also, pounds are defined in terms of kilograms. So you could also define a “weight” type. This can have semantics like DATE[3] or BOOLEAN[4] so inserting “1kg”, “1kilogram”, “1000g” or “2.204623lb” all store the same value. You can then use (define) format functions like TO_CHAR(weight, string) to display weights in grams, lb or whatever you need. Of course, the other argument here is that all you really need are the primitive types and normalization. A “weight” table can just have a “unit”::string column. You can do conversions with another table such as “from_unit”::string, “to_unit”::string and “multiple”::float. Your “product_weight” table would then just have foreign key relationships to the weight and conversions tables. [1]: https://www.postgresql.org/docs/current/xtypes.html https://www.postgresql.org/docs/current/xtypes.html [2]: https://github.com/df7cb/postgresql-unit https://github.com/df7cb/postgresql-unit [3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DATATYPE-DATETIME-INPUT https://www.postgresql.org/docs/13/datatype-datetime.html#DA... [4]: https://www.postgresql.org/docs/13/datatype-boolean.html https://www.postgresql.org/docs/13/datatype-boolean.html