MUmulmen·Aug 4, 2021
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 i
Read the rest
n 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
[2]: https://github.com/df7cb/postgresql-unit
[3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DA...
[4]: https://www.postgresql.org/docs/13/datatype-boolean.html